Fix Excel Dates Formatted as Text

Dates incorrectly entered or imported as text are a showstopper. We need our dates entered as date serial numbers to be able to leverage the Date Functions, use them in math and lookups, or even use them in PivotTables. So, often the first thing we need to do is fix Excel dates formatted as text.

However, it can be tricky to tell if a date is text, because unlike regular values, a date will often appear the same in the formula bar as it does on the face of the cell, as you can see below:

Text Date Quick Test 1:

A quick test to check if dates are text is to select more than one cell containing your dates, then glance down to the status bar to see if you get a Sum result. The status bar is in the bottom right of the Excel window:

That's right, the dates that are correctly entered as date serial numbers will display the SUM result in the status bar when you select more than one cell containing a date. Whereas text will only display the count.

Text Date Quick Test 2:

Another quick test is to change the cell format to General.

If your date format displays the date serial number (as shown in the second list of dates to the right) you're good to go, but if it still displays a date (as shown in the first list of dates on the right), then you've got yourself some text dates that need fixing.

It's not as simple as just changing the cell format to 'Date'. Those text dates are stubborn. We'll get on to fixing dates formatted as text soon.

Text Date Quick Test 3:

Widen the column; any dates that are text will align to the left, assuming no other alignment formatting has been applied to the cells.

Fix Excel Dates Formatted as Text

Option 1: Use Built in Error Checking to Fix Dates

More recent versions of Excel are good at detecting dates incorrectly entered as text and tagging them with a warning notification. Hovering your mouse over the warning tells you the date is entered as text:

Clicking on the warning reveals a list of options for fixing the date, or ignoring the error, as you can see in the image below:

Tip: if you have a lot of dates to fix, or a lot of formulas referencing these dates then converting them to date serial numbers with this method could be slow. Option 2 may be quicker.

In cases where the warning doesn't display, as well as in earlier versions of Excel, we need to resort to other methods of fixing dates formatted as text. Let's take a look.

Option 2: Paste Special

In an empty cell enter 1 and copy the cell to the clipboard

Select the cells containing the text dates you want to convert to date serial numbers.

These methods will return a list of date serial numbers which you can then apply a date number format to.

Tip: on long lists with lots of formulas referencing your dates this method is often quicker than the error checking method in option 1.

Option 3: Text to Columns

If your text dates aren't formatted based on the standard structure for your region e.g. dd/mm/yyyy or mm/dd/yyyy, then a great tool to use is Text to Columns, because this allows you to specify the order of the date characters ensuring that they are converted to date serial numbers correctly.

Select the cells containing your dates

Data tab

Text to Columns

Delimited

Next

In step 2 of the wizard simply click Next.

In step 3 of the wizard you can select the order of the date data from the drop down:

Note: this is the format of the text you're converting, not your final desired format.

Your final desired format can be applied with custom number formatting.

Option 4: DATEVALUE Formula

The DATEVALUE function takes a date text string and converts it to a date serial number, as you can see in this example:

Tip: You'll probably want to convert those DATEVALUE formulas in column C to values with Paste Special > Values and get rid of the text dates in column B. No need to have them cluttering up your spreadsheet.

Excel Date and Time Formatting

Now that you know how to fix Excel dates formatted as text you'll want to format them, and maybe even use a custom date or time format. Here's a post with everything you need to know about formatting Excel dates and time.

Free eBook - Working with Date & Time in Excel

Everything you need to know about Date and Time in Excel - Download the free eBook and Excel file with detailed instructions.

Resources

Affiliate Program

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

✕

Hang On, Don't Go Just Yet.

As a thank you for visiting how would you like a10% Discount Code to use with any of my courses?