Author: Oscar Cronquist Article last updated on March 01, 2022

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.

1. Verify your date and time settings

win 10 national 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.

Back to top

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.

=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))

The picture above shows the output of the formula in column C.

Back to top

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.

Back to top

Get Excel *.xlsx file

DATEVALUE function not working.xlsx

Back to top