How to Convert Text Dates in Excel

Set yourself up

For a general overview of working with dates, 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 dates that we need 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 setup to recognize dates automatically in the short and long formats you use on Windows. 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”. 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 is not listed in the available types, we can also create a custom type by going to the Custom category and specifying the format, using the notation mentioned earlier: dd-mm-yy.

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

Unfortunately, if your dates are in a more complex or less common format, Excel may not be able to recognize them right away. 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)