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.
Formula in cell C3:
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
DATEVALUE function not working.xlsx
More than 1300 Excel formulasExcel 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.