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.

datestonumbers01

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.

datestonumbers02

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

datestonumbers03

In Step 1, select Delimited, and click Next

datestonumbers04

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

datestonumbers06

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.

datestonumbers07

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

datestonumbers08

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

datestonumbers09

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.

datestonumbers10

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

62 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…

    Thanks!

  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

    =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

  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:

    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!

  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:

    Marvelous!

  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.

  25. KT says:

    Thank you! Found this page via google search, and it was exactly the solution I needed for my problem.

  26. shiv says:

    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?

  27. Victoria says:

    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!

  28. Bulldozer says:

    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.

  29. mb says:

    Thanks, worked perfectly and saved us creating new columns repeatedly.

  30. Tony says:

    Excellent solution! Thanks.

  31. David Devereaux says:

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

  32. Ben says:

    omg. thank you. that was driving me nuts. it’s always the simple ones.

  33. Nilesh says:

    Thanks for sharing this info

  34. Daniel says:

    This tip was golden for me…. thank you so much!

  35. Dinesh says:

    Excellent, Very knowledgeable,Thank you so much

  36. Merle Anne says:

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

  37. Anna L says:

    This article was great! Pictures were good too! Thank You!Really

  38. Del Simcox says:

    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

    • Akram says:

      @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

  39. Kyrene says:

    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

  40. Abhishek Palande says:

    Thank you soooo much…I was trying since 25 minutes bt without success…this works:-)

  41. George says:

    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.

  42. Quentin says:

    Thank you very much … have been using a very long method on how to convert text dates

  43. Caitlin says:

    Thank you! Kept me from putting my head through my desk and/or eating a ton of junk food.

  44. Fran says:

    I LOVE you. What a huge help. Pulled out way too much hair before I found this post!

  45. Dave Woolley says:

    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.

  46. Gamera says:

    Thank you!

  47. Brenda Gallop says:

    Thank you! It was driving me crazy….

  48. John Hejka says:

    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.

Leave a Reply to Pascal Cancel reply

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