Mar 21 2018 08:05 AM - last edited on Nov 09 2023 11:10 AM by
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.
Sep 26 2021 12:39 PM
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.
Sep 26 2021 01:19 PM
Sep 26 2021 01:32 PM - edited Sep 26 2021 01:38 PM
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.
Oct 03 2021 08:19 AM
Oct 03 2021 09:27 AM
That's strange that Text to Columns doesn't work. Perhaps you may share part of the file as sample?
Oct 03 2021 11:58 AM
Oct 20 2021 12:59 AM
Man you guys just saved my life .. let me follow you on twitter @Sergei Baklan
Oct 21 2021 04:26 AM
Oct 23 2021 04:21 AM
In which form to type depends on regional format settings
Dec 23 2021 08:54 AM
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!
Jan 02 2022 06:23 AM
Jan 02 2022 07:16 AM
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
Jan 02 2022 07:22 AM
Jan 02 2022 07:29 AM
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.
Jan 02 2022 07:39 AM
I tried on my site, it works
and
and
Do you really have dates or these are texts which looks like dates?
Jan 02 2022 07:40 AM
On your sample it's hard to understand where are dates and where months.
Jan 02 2022 07:44 AM
Jan 05 2022 09:59 AM
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.
Jan 05 2022 10:04 AM