Date Formatting Won't Change

[Steve Gould]

I have an excel column that includes dates currently formatted as MM/DD/YYYY. I want it formatted as YYYY/MM/DD. When I go to format cells and change the date format, nothing changes. If I try to change the cells to any other type of cell - general, text, number, time, whatever - nothing changes. 

Please help. 

[#2]

Hi Steve,

I guess you have text in your column, not dates (which are actually numbers). To convert you may use from ribbon Data->Text to Columns selecting Date and applying MDY on the third stage of the wizard.

[#3]

I did that. Didn't do anything. 

[#4]

Steve,

After applying Sergei's solution, select the dates, press Ctrl+1 and then select Custom category.

After that, copy the format YYYY/MM/DD to the Type box, and then hit OK.

[#5]

Dear Sergei,

this was an older question but I have the same problem and was wondering if you may be able to assist.

I have a brand new Sheet and want to have 8 columns and 3 of them are a date. 

I have inserted 10.10.2018. Then I go to Format Cells / Date / 14 March 2012 and hit ok.

Now the date should be shown as 10 October 2018 but it does not change at all. 

Also tried other custom variants for dates but it just stays as it is as 10.10.2018.

Is there any general setting that is wrong?

Thank you

regards

Chris

[#6]

Hi Chris,

What is your default (system) date format? To check you may hit Ctrl+1 on any cell and select Date. First two formats in the right pane will be marked by asterisk, these are so called short and long dates. In my case it looks like

You shall enter dates as in first record, i.e. 10/10/2018 (instead of 10.10.2018) if you see in above 14/03/2012. Otherwise you have some text in form of dates, not dates as Excel recognizes them.

In brief, most probably you enter dates as text

[#7]

Thanks so much for the fast reply.

I looked up what the first ones look like under date and it is same as yours. 

What I did now:

I marked all cells and chose format text. Then I chose those cells that are supposed to have a date format and chose the first option as with the asterisk in your screenshot.

Then I entered the date as you said in this way: 10/10/2018

But it shows now exactly how I entered it as 10/10/2018. But if I chose in the format the first option: should it then not show it the way it is displayed in that box?

What I actually wanted is to have e.g. in A2 a date and in A3 this date plus 5 days as it should serve as a follow up due date and put some conditional formatting to it with colours if it is overdue to follow up.

Hope this makes sense.

But even though I inserted it now as a date (I hope) and put into A3 then =A2+5  it just shows "Value" but according to some other forum info it should add the to the days and would give me then in theory 15/10/2018.

Not sure what I do wrong 😞 

Thanks

Chris

[#8]

Chris,

1) Don't format all cells as text before you start to work with them, keep default format (General).

2) Enter the date not as on my screenshot, but in one which you see in your formatting options. We may have different default date formats.

After that =A2+5 shall work. In behind dates in Excel are just sequential integer numbers starting from 1 which is 01 January, 1900. And 13 Oct 2018 is 43386. On the top is only formatting.

[#9]

Thanks kindly!

Is there a possibility to amend somewhere the default format to e.g. dd/mm/yyyy ?

Or how is the default date format set? 

Is this depending on which version one works?

Thank you!

[#10]

Chris, that's Windows setting, not Excel one. Press Win+R, here "control international" and Enter.

In opened windows you may check / change default formats

Note, if you change format here that affects all your applications, not only Excel.

[#11]

Thanks Sergei,

I found the general settings of windows and amended it. Now it is the way I wanted it.

Thanks so much for your help. Much appreciated and glad it worked out after all.

And also the little formula for the due date works.

Such a small thing to change but if you don't know where it makes you pulling your hair.

I was not aware it is in general settings but thought it must be somewhere in excel 🙂

kind regards

Chris

[#12]

Also, the only way I could get dates in Excel formatted as Text to Date was to use the DATEVALUE formula.  (Text to columns did not work for me)

[#13]

@Big_Wheelz 

Text to Columns could work if on third step of the wizard properly select date format.

[#14]

@SergeiBaklan After trying the solutions proposed and seeing no changes as I wanted it to be :

01/02/2020 to be 1st of February 2020 in the form dd/mm, I tried entering 1 FEB and it worked as 01/02/2020 finally, i customised to dd/mmm and it all changed at last.

I hope what I did will help others.

[#15]

I have a similar situation currently. 

I am working with about 200 000 rows across 10 columns.

My first column contained the date and time. I separated these. My dates are somewhat repetitive since data was collected every 5 minutes for 2 years. 

All my dates from the first one to the 90 754th one will not format correctly. Excel is reading it as year, month day, when I need it should be month, day, year. For example, my dates are reading 05/04/15 (y/m/d) but i need 05 to be the month, 04 to be the day and 15 to be the year. 

All my dates below 90 754 are correct. 

I have tried formatting them so many different times. I have spent the last three hours trying to figure this out.

I spent days stitching my data together. I don't want to have to redo it because I can't get this right.

Any suggestions would be greatly appreciated!

[#16]

@KaylaSch out of despair I typed 03 or 3 Feb in a cell as I don't use year with it and I don't need to see it.

it worked to the format I wanted as 03/02 not meaning 2nd of March.

To be sure, I dragged down a few rows and it was consistent.

My default date on the laptop is dd/mm/yyyy so I could not understand why Excel persisted to do mm/dd/yyyy

Hope it works for you.

[#17]

@SergeiBaklan

Data > Text to Columns with date format worked for me, thanks.

Before that, even pressing F2 to edit the cell and press enter was insufficient to have it auto-recognize as a date, which seemed odd to me.

[#18]

This looks like a bug in the Excel. I hit the same problem just a few days ago in spreadsheet that was created month ago and dates were displayed correctly. Now my dates are displayed as numbers and whatever I do to change the format of affected cells to Date the format does not change and they are always displayed as a number or formula text. Selecting columns or whole row with dates, then going to Format/Cell and changing format does nothing. Interesting is that changing that to any other format does not work (i.e. nothing happens when I change the format). Please check screenshot attached. Microsoft, please fix this bug as soon as possible!

[#19]

@MarcinSkoczylas I suspect you have "Show formulas" switch on.

[#20]

@Steve Gould 

I use large datasets and I find it way easier to import the data in Access, change the format there, then copy it back into Excel. It takes way less time than trying to figure out what's wrong with Excel formats