Forum Discussion
Date Formatting Won't Change
- Finally figured this out.
Change the date format of your computer from the taskbar.
Regardless of timezone and all, go to your task bar> click the time/date > region >then look for additional date, time & regional settings > change date, time and number formats> additional settings> then go to the date tab and you can manually set the format by using the MMM dd yyyy or whichever format you want it in. Restart your computer and it should work on excel.
Wew
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.
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!
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.
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.
