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:
My favourite way to test if dates are entered correctly is to use the keyboard shortcut CTRL + Back Quote. Dates entered correctly will be displayed as their date serial number and dates entered as text will still display as text:
Note: If you also have formatting and formulas on the worksheet you'll notice the CTRL+ Back Quote keyboard shortcut also temporarily removes the formatting and exposes the formulas. Use the keyboard shortcut CTRL + Back Quote to revert to the previous view.
Text Date Quick Test 2:
Easily test if dates are text by selecting 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.
Caution: in Excel for Microsoft 365 a SUM will appear in the status bar if proper dates and text dates are included in the range, therefore this isn't a reliable option for that version of Excel.
Text Date Quick Test 3:
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 4:
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: Power Query
The video below runs through 5 common date formatting problems and illustrates how easy it is to fix them with Power Query. The bonus with Power Query is that you can refresh the query to get new data and have it automatically fix those dates too.
Download Workbook
Enter your email address below to download the sample workbook.
Option 3: 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.
- Home tab > Paste >
- 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 4: 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 5: 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.
Just a little thing, though obnoxious.
When using the Paste|Special|Multipy approach, if you do it as simply as that, you will paste the “pick a cell anywhere and put a 1 in it” cell’s formatting too. Variety of ways to overcome this as an issue, but the only good one is to copy one of the cells to fix (assuming it has proper formatting…) and paste it on the “cell anywhere.” THEN do the process. And you can paste a cell back over the one picked to restore its formatting if that’s a concern (would be for me, hate leaving little bombs all over the place).
Great idea if formatting is already in place.
Hello
Thanks for helping me .I interested exle .because many problems in my work solve whit excle
I like every day one technique of excle learning.
I hope success for you
Hello Mynda – I consistently use another test as my 1st go-to method. I just turn on the autofilter and select the dropdown arrow. If it is formatted as text, it will show each individual unique date as shown in the cells. If it is formatted as a date, then I will see Years with a ‘+’ sign. If I select the + sign, then it will show the months, etc. For me this is very quick. I usually have the autofilters already on and just select the drop down arrow.
Yes, another great way. Thanks for sharing, Keith!
Hello Mynda, and thanks very much for your interesting and useful newsletters, videos and web pages – and keep well!
RE: “Note: If you also have formatting and formulas on the worksheet you’ll notice the CTRL+ Back Quote keyboard shortcut also temporarily removes the formatting and exposes the formulas. Use the keyboard shortcut CTRL + Back Quote to revert to the previous view.”
1) Depending on your keyboard, the back quote may be a shifted character, in which case the combination is Shift-Ctrl-(Key). This is the case on my German keyboard.
2) The combination works as described on my machine (keyboard as above, Excel 365), but pressing the combination a second time doesn’t revert to the previous view; it duplicates the contents of the cell. Example: Display shows 6.12.2006, keypress changes it to 24419 as expected but a second kepress results in 2441924419. The Esc key returned the display to the original 6.12.2006
Good to know, Chris. Thanks for sharing.
Hi Mynda – i have time duration as text written in a column as 9min 18s, 6s, 3min etc etc. it is data from our new phone system exported as csv- we want to sum this & get a total “time on phones” per employee. Any idea how i can convert this to a time value? All options i have tried have returned an error.
Hi Marnie, please post your question on our Excel forum where you can also upload a sample file and we can help you further.
I have an excel spreadsheet that has multiple columns, with ONE column that has dates but the dates are in TWO different formats. I am able to do the steps to correct them, individually, but then they don’t sort as one column. They format in two different table formats
Hi Christina,
It sounds like your date format differs from the format in the file. Some of the dates have come in appearing correct, but in fact they will all be wrong. You need to use ‘option 2’ to fix your dates as shown in the video in the post above.
Mynda
Thank you so much! Worked perfectly and I learned something new.
You saved my day when I was trying to read in an excel file into SAS dataset.Thank you so much!
So glad we could help 🙂
If excel could be trusted to leave formats as the spreadsheet creator intended this would be awesome.Excel’s creators have decided that users are too stupid so they take some text, in a cell formatted as text that resembles a date and they reformat the cell as a date type which changes the entire value of the data. It actually DESTROYS data integrity but they won’t listen. I have had to work SO hard t work around this. Is there a setting somewhere that can turn off this short-sightedness?
Hi Andrew,
Unfortunately or fortunately, depending on which side of the fence you sit, Excel was designed to help the average user and anticipate their intention. There’s no setting to turn off changing text dates into date serial number on input. However, if you type an apostrophe before your date it will remain as text and you won’t see the apostrophe on the face of the cell, only when viewed in the formula bar.
Mynda
None of these options worked!!! What am I doing wrong???
Hi Kayla,
Some text is stubborn 🙂 Please post your question and a sample Excel file on our Excel forum where we can help you further.
Mynda
Option 2 is amazing, thank you very much.
Hi- The file that I spool from our accounting package always downloads the date as a text field. This results in 01-03-2018 being corrected(as per your guideline to 3rd of January instead of the 1st of March. So all date orders in the date field will result in the months changing for example :
01-03-2018 3rd of January
02-03-2018 3rd of February
03-03-2018 3rd of March
How do i fix this
Thanks
Hi Hoosein,
Which method did you use? If you use Option 3: Text to Columns, in step 2 of the Text to Columns Wizard you can choose the date format you want (DMY or MDY).
Mynda