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.

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

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.

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

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.

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.

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

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!

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.

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?

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

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!

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.

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.

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.

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?

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

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

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

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

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.

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.

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.