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:

Don't worry, I've got several never fail, dead easy ways to tell if dates really are date serial numbers, or if they're actually text masquerading as dates.

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

1. In an empty cell enter 1 and copy the cell to the clipboard
2. Select the cells containing the text dates you want to convert to date serial numbers.
3. Home tab > Paste >
4. Paste Special

 

In the Paste Special dialog box select:

- Values

- Multiply (or Divide)

Tip: Paste Special > Values shortcut keys:

ALT > E > S > V > Enter or CTRL+ALT+V > V > Enter

Alternate approach: You can also use Paste Special with 'Add'.

Simply copy an empty cell > Select your date cells > Paste Special > Values & Add!

No need to go back and delete the '1'.

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.

1. Select the cells containing your dates
2. Data tab
3. Text to Columns
4. Delimited
5. 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.

Need more ideas on how to fix Excel Dates formatted as text? Check out this post: 6 Ways to Fix Dates Formatted as Text in Excel

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.

Please Share

Working with dates and time are one of the most common struggles for Excel users, so do your friends and colleagues a favour and share this post with them on LinkedIn, Google+, Facebook and Twitter.