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]

@ExcelRT 

Most probably on your new laptop regional setting are differ from what it was before.  I have no idea in which locale you are, try to check Time & Language section in Windows settings.

[#3]

I am still facing the same issue when the data is extracted. I think its how the data is extracted it is some form of hard code which isn't recognised in excel formats.

[#4]

@KaylaSch 

The result of a failed date input tends to be some entries that look like dates but as text and in the wrong format and other entries that have been converted to numeric dates for calculation but are probably the wrong dates.

If it is text the formula should use MID to pick out the values and reassemble them to look like the default date on your machine before coercing to a date-value.

For the entries that are already numbers you would need to pick out the

yr := DAY(date), 

m := YEAR(date)

d := MONTH(date)

using these date functions, and the rebuild the date you need using 

= DATE(yr, m, d)

Which conversion strategy to use is dictated by the result of

= IF(ISNUMBER(date), ..., ...)

Importing dates using Power Query can provide a good alternative strategy.

[#5]

hello, I have a similar problem, I have a whole column of dates (presumably stored as text). I have tried many options(including this) to format as date but nothing is changing

[#6]

@ifeoluwa_Clement 

That's strange that Text to Columns doesn't work. Perhaps you may share part of the file as sample?

[#7]

Thanks for your reply, i was able to make it work. The text data included time (13/09/2021 10:24:33), i was able to convert it to date format after i separated the time from the data.

[#8]

Man you guys just saved my life .. let me follow you on twitter @Sergei Baklan 

[#9]

If anyone still has the same problem: Maybe when you were typing in the data, you were doing it wrong. Try inputing 2021/05/23 instead of 2021.05.23

[#10]

@MATOME_THATA 

Sorry, I don't use Twitter

[#11]

@Varroliver 

In which form to type depends on regional format settings

[#12]

@Sergei Baklan 

Thank you so much for this resolution. I have been banging my head against a wall for days trying to determine what I Was doing wrong! I appreciate the step by step instruction. Bravo, and thank you again! 

[#13]

I am using excel365 online from a sharepoint file. I have my computer set as default to UK date format and everytime I open the file I reset the dateformat to UK using file options, control 1, and then setting the file format to UK. But to no avail, the file instantly defaults to US and the cells do not change globally. Worse still some of cells are in US format and some in UK.
I just want to use UK format and have followed all the advice given in this thread and others.

[#14]

@dcruzj 

Excel for web has no idea about regional settings on your computer, you need to change them on web (SharePoint/OneDrive site) as well. That's in File->Options within Excel for web

[#15]

Thanks Sergei
I do this every time as I explained. However, excel defaults to US every time I open it. I then reset it as you describe, but the change does not affect all dates globally.
I then try and use control 1 and change the formatting there since the options doesn't work. Unfortunately that hassn't worked either.
I have now changed all the dates by hand and hope that this will hold. If it doesn't, I think that there's a problem with the software.

[#16]

@Sergei Baklan 

I have just tried your solution again since there were a few earlier dates that I had not changed. this is the result. As you can see the change is not global. Some cells change others don't.

[#17]

@dcruzj 

I tried on my site, it works

and

and

Do you really have dates or these are texts which looks like dates?

[#18]

@dcruzj 

On your sample it's hard to understand where are dates and where months.

[#19]

Hi Sergei
That's my problem, the top line is mm/dd/yyyy and the second line is dd/mm/yyyy.
I have now gone through all the dates and set them to UK by hand. I hope that this holds when I next open the file.

[#20]

I am having the same issue converting Nov 01 2021 10:30 to 11/01/2021 10:30 it does nothing no matter what I try.

 @Steve Gould 

[#21]

Recently there was an update that caused issues with Access. Hope a fix is found soon