Excel Dates Won’t Change Format

Have you ever imported data into Excel, from your credit card statement, or somewhere else, and found that Excel dates won’t change format? 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.

Fix Dates That Won’t Change Format

This video shows how to fix the dates that won’t change format, and there are written steps below.

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.

problem dates

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.

Quick Calc in the Status Bar

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. There’s a video at the end of this article, that shows how to do that.

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

Click Text to Columns

In Step 1, select Delimited, and click Next

select Delimited

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

select Space as the delimiter

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.

choose the date format

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

Do not import

  • 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.

Check Status Bar

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.

Format the numbers

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

Download the Sample File

To follow along with this tutorial, get the Date Format Fix Sample file from my Contextures website, on the Excel Dates Fix Format page.

More Excel Date Info

Prevent Grouped Dates in Excel

Sum for a Date Range in Excel

Count Items in a Date Range in Excel

How to Fix Numbers That Won’t Add Up

If you’re having a problem with Excel numbers that won’t add up, this video shows a few fixes that you can try. There are details on the Fix Numbers that Don’t Add Up page on my Contextures site.

______________________________

How to Fix Excel Dates That Won’t Change Format

Fix Excel Dates Won't Change Format

___________________________

90 thoughts on “Excel Dates Won’t Change Format”

  1. 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?

    1. @Ben, you’re right, that Paste Special trick usually works, and I forgot to mention that I’d tried it.
      Thanks for the reminder, and I’ve added that to the article, with a link to my page on fixing “text” numbers.

    2. I don’t know if this solves all problems, but this is the simplest best answer I’ve come across. I feel like I’ve wasted too much time reading other more complicated ways of performing this simple fix.

    1. @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.

  2. 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.

    1. 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. 🙂

    2. I found an answer to your problem. To explain, I made the mistake of formatting my field as date (using the 00/00/00 format), but making it left-aligned. When I entered 4-04-2014, it would show up as 4-04-2014 instead of 04-04-14 as it should have. The solution:

      1) Change the alignment to General. Sometimes this was all I needed to do. But other times, I would still get 4-04-2014; then I would change one numberof the date — either I could delete the ‘0’ in the “04”, or just delete a digit, then re-enter it (like deleting the “4” in 2014 and then retyping it. When I did this and exited the field, it automatically right-justified the date and showed it in the desired format: 04/04/14.

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

  4. It doesn’t work with datas token from other cells by formulas, e. g. cell B2 contains
    =A2
    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

  5. 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? >:(

  6. Hi,
    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!

  7. 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!

  8. 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?

  9. 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?

  10. 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?

    1. Your computer actually stores dates internally in the Julian Date format. May 30, 5437 is stored as 1292014. If you type that number with either / or – between the day, month and year, it will read it as a date and format it as we humans recognize dates. {1/29/2014 or 12/9/2014} If you type in 1/29/2014 and change the formatting back to a number, it will give you the Julian equivalent of 41668.

  11. 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,

  12. 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!!!!!

  13. 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!

  14. 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.

  15. 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.

  16. 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.

  17. Hi i have a problem i’ve imported data from a webpage the date in the cell appears as “30/04/15” (30 April 2015) which is exactly what i need however in the formula tab it comes up as “1930/04/15” how can i change it to be exactly as it appears?

  18. you are so AWESOME!!! thank you so much for the clear instructions and that actually fix my problem – I am so super happy right now!

  19. If you don’t mind adding a new column, I found using the TEXT function will get you the converted format you want.
    Example: =TEXT(A2,”MM/DD/YY”). It works for any custom format allowed by Excel.

  20. A quicker way to delete the space in front of the date for those who have very large items to format would be to use the “Find and Select” feature in the tool bar. Select the area of dates you want to change, Click the Find and Select….select replace, you will have two tabs, select replace: in the find what use space bar to move over 1 space, then in the next block below “Replace with” leave curser to the far left…in other words don’t move it and click on replace all. This will remove the space in front of the dates allowing them to auto correct to your selected format!!! Vola!!!

  21. I was a fundi in Excel – MS somehow have managed to mess the date formatting and we spend hours trying to figure it out instead of doing our work – what is this rubbish and what were you guys thinking – something as simple as a date format you mess with – I can only imagine how messed up the formula functions are in Office 2016 – very frustrated and disappointed!!! why fix something that ain’t broke!!!!

  22. A little VBA code can handle this nicely.
    Give the range of dates you want to convert a name, either manually or by vba code. I’m named the range “rngDates”.
    Then loop through the range of cells, testing to see if the cell contains a date [If IsDate(d)], and if it does, convert the textual date to a date value (using the type conversion function CDate, and the apply the desired format with NumberFormat:
    For Each c in rngDates
    d = c.value
    If IsDate(d) Then
    c.value = CDate(d)
    c.NumberFormat = “dd/mmm/yyyy”
    End If
    Next

    1. @Del Simcox – Not very familiar with VBA, Kindly provide complete vba code.
      Have to manually copy date and time daily from received and sent emails from outlook and paste in excel 2010 in columns K4:K300(sent) and L4:L300(received). Date in outlook format is ( Fri 7/15/2016 1:38 PM) and its pasted in excel as text.Need to convert text in format (ddd mmm dd, yyyy – hh:mm AM/PM)
      Tried below code and get runtime error .Would be great if a macro button is provided to convert the text date in reqd. format,even better macro runs automatically for tht cell when copied.
      Thanks in advance … @Debra it’s a fantastic site.Thank You
      Sub test()
      Dim rngDates As Range
      For Each c In rngDates
      d = c.Value
      If IsDate(d) Then
      c.Value = CDate(d)
      c.NumberFormat = “ddd mmm dd, yyyy – hh:mm AM/PM”
      End If
      Next
      End Sub

  23. Hello,
    I have this stupid problem where I cannot change the date formatting. Can someone help me.
    I’m going crazy… I check in google to see if I can solved my problem. I could see a lot of people has a trouble change date format but it doesn’t solved mine.
    Here is what I already did.
    I copy a lot of data from several Excel.CSV. I can send an Excel with my problem.
    No Formula.
    column A, I could change the date formatting but now I can’t!
    column B, some date I could change and some not.
    I first separeted the date and time, it works but change the date formatting, isn’t working
    then save my work in HTML but it isn’t working,
    then save in CSV, it isn’t working.
    Copy and paste and Notepad, isn’t working.
    Clear all formating, isn’t working
    No macro are visible.
    Well can someone has a new idea to help me out, I’m out of idea
    Please ask me the excel sheet and I will send it to you.
    Thank you
    Kyrene

  24. The method does not work for me – tried several times.
    I have to change it manually – can send you the file.
    Would be grateful for help.

  25. Debra,
    Have never used the text to columns fcn. It works as you showed perfectly for converting my text date fields to numeric date fields that I import from my bank and credit card companies every month. Thanks.

  26. The “Paste Special” trick worked. I would have never thought of the step without this hint. Someone really knows what MS Excel developers did in background to “make Excel” work … but NOT in all situations.

  27. Hi Debra,
    Very Well explained !!!!
    This method is very interesting, and so much helpful.
    Firstly i am not sure about it but when i applied it it worked for me like boom !!!!
    Thanks for sharing this .

  28. Hey, thanks! That solved my problem so easily! For some reason the =DATEVALUE function wasn’t working on my data (exported from QuickBooks Online). I sure I’ll be able to use this method again in the future, as I struggled a lot in the past, trying to strip out the time values from data.

  29. Although this article was written in 2015, but here we are in 2021 and people still getting the benefits, Thank you so much for saving lots of time and efforts for many people (including mine).
    “Doing good will always keep a person alive”

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.