DATEVALUE function not working
The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text. However, it must be a valid text date which is determined by your date and time settings (national settings) on your operating system.
Table of Contents
1. Verify your date and time settings
Microsoft recommends that you verify your date and time settings so they are compatible with the text date format. On a "Windows 10" operating system open the Control Panel and then "National Settings" and see if you can find a supported date setting. Both the short and long dates must match.
2. Convert a text date to an Excel date using a formula
I could not find a setting that worked with the text date above so I created the following formula in cell C3.
The picture above shows the output of the formula in column C.
3. Explaining the formula in cell C3
Step 1 - Extract year from string
To extract the year from the text date in cell B3 I simply use the RIGHT function to extract the 4 last characters.
The RIGHT function extracts a specific number of characters always starting from the right.
RIGHT(text,[num_chars])
RIGHT(B3, 4)
becomes
RIGHT("Jan 21 1996", 4)
and returns 1996.
Step 2 - Extract month abbreviation from string
The month number is a little harder since it is built of three letters. To get the three letters from B3 I use the LEFT function.
The LEFT function extracts a specific number of characters always starting from the left.
LEFT(text, [num_chars])
LEFT(B3, 3)
becomes
LEFT("Jan 21 1996", 3)
and returns "Jan".
Step 3 - Convert month to a number
The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.
MATCH(lookup_value, lookup_array, [match_type])
MATCH(LEFT(B3, 3), {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0)
becomes
MATCH("Jan", {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0)
and returns 1. "Jan" is the first value in the array.
Step 4 - Extract day from string
The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.
MID(text, start_num, num_chars)
MID(B3, 4, 3)
becomes
MID("Jan 21 1996", 4, 3)
and returns " 21".
Step 5 - Create an Excel date
The DATE function allows you to create an Excel date based on a year, month, and day number.
The arguments are DATE(year, month, day).
DATE(RIGHT(B3, 4), MATCH(LEFT(B3, 3), {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0), MID(B3, 4, 3))
becomes
DATE(1996, 1, 21)
and returns 1/21/1996 in cell C3.
Get Excel *.xlsx file
Functions in this article
More than 1300 Excel formulas
Excel categories
2 Responses to “DATEVALUE function not working”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi, Oscar
Result 4/19/2896 is strange.
Alternative formula...
=--SUBSTITUTE(TRIM(B3)," ",", ",2)
aMareis,
Result 4/19/2896 is strange.
I didn't see that. Yes, there is a space character that makes it weird.
=--SUBSTITUTE(TRIM(B3)," ",", ",2)
Great formula, thanks for your valuable comment.