Excel Dates Won’t Change Format

Have you ever imported data into Excel, from your credit card statement, or somewhere else, and had a problem formatting the dates? And, if you try to sort that column of dates, things end up in the wrong order.

That happened to me this week, and here’s how I fixed the problem, using a built-in Excel tool.

Dates As Text

In the screen shot below, you can see the column of imported dates, which show the date and time. I didn’t want the times showing, but when I tried to format the column as Short Date, nothing happened – the dates stayed the same.


Why won’t the dates change format? Even though they look like dates, Excel sees them as text, and Excel can’t apply number formatting to text.

There are a few signs that the cell contents are being treated as text:

  • The dates are left-aligned
  • There is an apostrophe at the start of the date (visible in the formula bar)
  • If two or more dates are selected, the Quick Calc in the Status Bar only shows Count, not Numerical Count or Sum.


Fix the Dates

If you want to sort the dates, or change their format, you’ll have to convert them to numbers – that’s how Excel stores valid dates. Sometimes, you can fix the dates by copying a blank cell, then selecting the date cells, and using Paste Special > Add to change them to real dates.

Unfortunately, that technique didn’t work on this data, probably because of the extra spaces. You could go to each cell, and remove the apostrophe, but that could take quite a while, if you have more than a few dates to fix.

A much quicker way is to use the Text to Columns feature, and let Excel do the work for you:

  • Select the cells that contain the dates
  • On the Excel Ribbon, click the Data tab
  • Click Text to Columns


In Step 1, select Delimited, and click Next


  • In Step 2, select Space as the delimiter, and the preview pane should show the dates divided into columns.
  • Click Next


In Step 3, you can set the data type for each column:

  • In the preview pane, click on the date column, and select Date
  • In the Date drop down, choose the date format that your dates are currently displayed in. In this example, the dates show month/day/year, so I’ve selected MDY.


  • Select each of the remaining columns, and set it as “Do not import column (skip)”


  • Click Finish, to convert the text dates to real dates.

Format the Dates

Now that the dates have been converted to real dates (stored as numbers), you can format them with the Number Format commands.

There are a few signs that the cell contents are now being recognized as real dates (numbers):

  • The dates are right-aligned
  • There is no apostrophe at the start of the date (visible in the formula bar)
  • If two or more dates are selected, the Quick Calc in the Status Bar shows Count, Numerical Count and Sum.


To format the dates, select them, and use the quick Number formats on the Excel Ribbon, or click the dialog launcher, to see more formats.


Everything should work correctly, after you have converted the text dates to real dates.

Can't sort or format Excel dates? How to fix them. blog.contextures.com


You may also like...

66 Responses

  1. Ben says:

    I don’t remember testing this with dates, but could you achieve the same result by using paste special to add 0 or multiply by 1? Or is there an extra space between date and time getting in the way?

  2. Bryan Munch says:

    Another very easy trick is to simply use the DATEVALUE() formula in a new, adjacent column.

    • Whitney says:

      @Bryan, Agree! I had tried so many things to work with data exported from QuickBooks Online. Finally QBO suggested that I use DATEVALUE, copy and paste that column as values, then reformat the values to date format.

  3. Sue says:

    Recently switched the OS to Win 7 from 03, and 2010 Office/Excel, etc. Now the dates will not format to the existing data in the old USA date format, 04/25/14. Before I could type 4-25-14 and the program automatically changed it to 04/25/14. Now one of two things happens: it stays as 4-25-14 (left justified like text) or formats to 25/04/2014. I have used the number/date format on the column and the cell to no avail. Since we are in Costa Rica, it seems on some cells the program is reading the ISP address and formatting to the International style, but this does not happen in most cases. When it does, the only solution it seems is to convert it to text by placing an apostrophe in front of the number. This is yet another totally aggravating and frustrating MS problem.

    • Ali says:

      Hi Sue,

      Maybe your problem is with the regional configuration on your PC. If you change the regional configuration the dates must be showed as the region or country you select.

      If that doesn’t work, or you don’t want to change the configuration in your machine, select the whole column where your data is then right click, select format cells and in “Category” select “Custom” and then in the “Type” field write: m/d/yyyy or the order you need. —> click on “Ok”. From there you can place the dates in any way you want, just check how the formats must be declared from that window.

      Hope this help you. :)

  4. Sean says:

    Awesome…this saved me a lot of hassle…I was saving excel files as text, then reimporting them…


  5. habibi says:

    it’s work like a charm. Thanks a lot

  6. Debbie says:

    Thank you SO much. Great instructions. Worked perfectly – saved me HOURS and HOURS of work.

  7. rwilly says:

    It doesn’t work with datas token from other cells by formulas, e. g. cell B2 contains


    But there should be a possibility, e. g. in order to work with imported datas which must not be changed.

    Thx for help

    rwilly from northern germany

  8. Mary says:

    I had an issue that the date wasn’t stored as text, was right-aligned, and nothing was working when I tried to change the date formatting. I discovered–quite by accident–that if I clicked in the formula bar of a given cell, the formatting change appeared. Unfortunately, it took clicking in each affected cell. Fortunately, I had under 100 rows in the worksheet.

    Is it just me, Sue, or do we, the paying customers, end up doing MS’s beta-testing for them? >:(

  9. Sandeep says:

    Thanks for the tip, it helped :)

  10. Borp says:

    i had the format m/d/yyyy h:mm:ss AM/PM. Followed your guidance and as a result I got the same 5/d/yyyy h:00:00 AM in each cell. What should I do to have proper dates? Thanks!

  11. Embla says:

    Thnx Debra, you’re a lifesaver! I only knew the long way of confirming the date format by pressing F2 followed by ‘enter’ in every cell. Upto a 100 cells that was doable (upto 20 possibly quicker) but I often have upto a 1000 cells (which definately causes RSI). Thank you!

  12. John Rogers says:


  13. Bob Ritchie says:

    I recently upgraded from an old OS to a new Mac mini and all new software. Having used EXCEL for decades I am totally confused but the new software. In the process of working an old but revised spreadsheet, a big one, and found the date column had been changed to a number. the proper date is in the cells as shown in the formula bar, but I have failed to find a way to revert to the date format. This is limited to this one spread sheet.

    Sounds simple but everything i have tried, for the last 3 hours doesn’t work. It used to be simple in the old version.

    Any suggestions?

  14. Nicky says:

    Thanks Debra, this was a real life saver!

  15. Abe says:

    Utterly amazing. You would NOT believe how many different (wrong) methods posted on the internet I’ve tried so far. This one worked perfectly (in Excel 2007). Where can I send you a monetary token of my appreciation lol?

  16. Dimple says:

    So helpful ! Thanks !

  17. Teena says:

    my issue is when I type a date, it comes out in date format but an entirely different date than I typed. Example: when I type 1292014 it comes out 5/30/5437. Anyone know how to fix this?

  18. Pascal says:

    This is brilliant! Thanks! I have been struggling for a while with a very similar case! Cheers. P.

  19. mahima says:

    I have to sort several rows on first column i.e date by formatting it in a particular date format. HOwever, all except one row is getting foratted and sorted. This exceptional row is left at the bottom and cannot be formatted or sorted.Hence remains in the bottom instead being somewhere in the middle. What could be the reason?

    Thanks in advance,

  20. sejal patel says:

    Thanks so much! this was so helpful. I have been struggling with this for long time, and my project file is enormous and I have to sort and send information in excel monthly. This was a huge time saver for me.
    thanks a lot!!!!!

  21. Cindy says:

    I have been using Excel for years and always enter dates this way: 1/18, which then comes out as Jan 18 and defaults to current year. And if I am working on prior year data, I just be sure to enter 1/18/14. Then I click format to select how I want it to look-often just dependent on room on the page. ie whether there is room for the full year yyyy or just yy or whether I want the month to print out mmm or just mm……

    Now if I enter 1/18, it comes out as some form of the correct month, but the year is 2018 and I have not found a way to correct that other than putting an apostrophe in front of it, but that is a huge pain and also no longer allows date formatting. There must be a way! This is Excel! Should not have to reformat every single entry!

  22. Howard says:

    I want every date in my spreadsheet to be a 2014 date, but Excel makes every date a 2015 date unless I hand-type every cell. Is there a way to set the year for the entire date column? Computers are supposed to do trivial, repetitive tasks like that in less than a second.

  23. Lori Girard says:

    OH my thanks so much! I tried EVERYTHING. Excel worked totally fine for me last year and I NEVER EVER had this problem with dates. This year… the dates just messed up in a crazy crazy way – didn’t make any sense at all. I tried several different solutions ‘out there’ on several blogs. THIS is the only one that worked. I am greatly relieved. THANK YOU! I don’t understand how Microsoft can’t fix this … clearly it is a new problem as I have talked to several people who have encountered it recently. I have used excel for years and NEVER had this problem until this year.

  24. Maz says:

    Debra, this is fantastic. I never leave comments but I felt like I really had to for this one. It has been plaguing me for ages. Your method worked perfectly even without the time stamp. It’s fast and clean.
    And I love your step by step screenshots that are very clear.
    Kudos to you.