How to Convert Text Dates in Excel

Set yourself up

For a general overview of how to convert text date formats in Excel, see my previous article How to Convert Text Dates to Numeric.

The first step in analyzing data in Excel is typically to load a text file into the program. Frequently, this text file contains text dates that we need to convert and use for our analysis. It is important that Excel knows to treat this date text as dates because it has several functions that can make working with dates much simpler.

Excel is set up to recognize dates automatically in the short and long formats you use on Windows, making it easier to convert text date formats in Excel. In Windows 10, these are found and changed by going to Settings > Time & Language > Date & time > Change date and time formats.

A short date of d/MMM/yyyy means that the expected format is a day without a leading zero, followed by an abbreviated month name, and then a four digit year, with the three parts separated by “/”, as in “1/Jan/2001”. You can easily convert text date formats in Excel to this structure by using the DATEVALUE function.

A long date of dddd, d MMMM yyyyy specifies the format as the day of the week, followed by a day without a leading zero, the full month name, and then the four-digit year, as in “Wednesday, 6/June/2018”. A short time of h:mm tt would be “5:30 pm”, and a long time of h:mm:ss tt “5:30:12 PM”.

Excel will recognize some additional common formats such as those involving numeric months with or without a leading 0 and yyyy-MM-dd (e.g., “2011-12-31”).

Analyze your date and time data

Built-in Functions for Manipulating Dates

In Excel 2016, you can find tools for working with dates by clicking “Date & Time” in the Formulas Ribbon.

The most important function for text date conversion in Excel is the DATEVALUE function. It is used to convert textual dates to a number that Excel recognizes as a date. The dates are stored as sequential numbers starting with 1 representing “Jan. 1, 1990”. For example, entering =DATEVALUE(“2011/02/23”) produces “40597”.  The function TIMEVALUE similarly converts a textual time (“12:20 AM”) to a number that Excel recognizes as a time.

With the conversion accomplished, we can then either format the number into any date format we choose or use Excel’s special functions for working with dates. We can change the formatting by right clicking on the cell, selecting Format Cells and then selecting the desired format from the available date types.

If the desired format isn't available, you can create a custom date format by selecting the Custom category and specifying the format, like dd-mm-yy. This is helpful for dates that need to be converted into a specific structure.

Excel provides functions to extract parts of a date such as DAY, YEAR, and WEEKDAY. Other useful functions include NOW to get the current date and time, and DAYS to compute the number of days between two dates.

Analyze your date and time data

Conversion with Non-Standard Formats

If your dates are in a more complex or non-standard format, Excel may not recognize them automatically, and you'll need to apply extra steps to convert text date formats correctly. We'll have to add an additional step to convert the dates to a recognized format. We can resort to extracting parts of the text string using the functions LEFT, MID, and RIGHT and then combine the pieces using & or CONCATENATE into a format that Excel supports. For example, if my text string is “2018-Feb-02”, which Excel does not recognize, I can use the following formula to swap the positions of the day and year parts of the text to become “02-Feb-2018”, which Excel does recognize.

=RIGHT(A2,2)&"-"&MID(A2,6,3)&"-"&LEFT(A2,4)