Author: Oscar Cronquist Article last updated on October 31, 2018

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.

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 date must match.

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 below shows the output of the formula in column C.

Explaining the formula in cell C3

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

To extract the year from the text date in cell B3 I simply use the RIGHT function to extract the 4 last characters.

RIGHT(B3, 4) becomes RIGHT("Jan 21 1996", 4) and returns 1996.

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.

LEFT(B3, 3) becomes LEFT("Jan 21 1996", 3) and returns "Jan"

The MATCH function lets you convert the month name into the corresponding month number.

MATCH("Jan", {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0)

returns 1.

Lastly, the MID function returns the day numbers.

MID(B3, 4, 3) becomes MID("Jan 21 1996", 4, 3) and returns " 21".

The DATE function now looks like this DATE(1996, 1, " 21") and returns 1/21/1996 in cell C3.

Download Excel *.xlsx file

DATEVALUE function not working.xlsx