• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Fix Excel Dates Formatted as Text

You are here: Home / Excel / Fix Excel Dates Formatted as Text
Fix Excel dates formatted as text
October 23, 2017 by Mynda Treacy

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:

identify excel dates formatted as text

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:

excel status bar

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:

excel status bar

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:

quick test 2 excel dates formatted as text

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.

quick test 3 excel dates formatted as text

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:

auto fix excel dates formatted 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:

auto convert excel dates formatted as text

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.

Subscribe YouTube

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Option 3: Paste Special

paste special to fix excel dates formatted as text

  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



 

paste special to fix excel dates formatted as text

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.

text to columns to fix excel dates formatted as text

  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:

text to columns step 3 fix excel dates formatted as text

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:

datevalue fix excel dates formatted as text

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.

Fix Excel dates formatted as text

More Excel Posts

excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.

Share and Collaborate in Excel

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.
Workbook Protection

Excel Workbook Protection

Excel Workbook protection can prevent your users from breaking your reports while still allowing interaction with Slicers and refreshing.
Category: Excel
Previous Post:Excel Date and Time FormattingExcel Date and Time Formatting
Next Post:Convert Dates to Text

Reader Interactions

Comments

  1. Chris van Zyl

    September 30, 2021 at 10:59 pm

    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

    Reply
    • Mynda Treacy

      October 1, 2021 at 9:02 am

      Good to know, Chris. Thanks for sharing.

      Reply
  2. Marnie

    May 4, 2021 at 7:38 pm

    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.

    Reply
    • Mynda Treacy

      May 4, 2021 at 9:23 pm

      Hi Marnie, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  3. Christina

    February 20, 2021 at 4:53 am

    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

    Reply
    • Mynda Treacy

      February 20, 2021 at 9:34 am

      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

      Reply
  4. Thea

    January 8, 2021 at 1:02 am

    Thank you so much! Worked perfectly and I learned something new.

    Reply
  5. Rafi Rahi

    May 27, 2020 at 10:07 pm

    You saved my day when I was trying to read in an excel file into SAS dataset.Thank you so much!

    Reply
    • Mynda Treacy

      May 28, 2020 at 9:01 am

      So glad we could help 🙂

      Reply
  6. Andrew Stewart

    November 19, 2019 at 7:28 am

    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?

    Reply
    • Mynda Treacy

      November 19, 2019 at 11:48 am

      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

      Reply
      • kayla

        January 9, 2020 at 7:02 am

        None of these options worked!!! What am I doing wrong???

        Reply
        • Mynda Treacy

          January 9, 2020 at 9:28 am

          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

          Reply
  7. contabilidad

    January 12, 2019 at 8:56 am

    Option 2 is amazing, thank you very much.

    Reply
  8. Hoosein

    March 29, 2018 at 9:59 pm

    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

    Reply
    • Mynda Treacy

      March 30, 2018 at 8:47 am

      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

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

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.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x