Forum Discussion
Solved
Date Formatting Won't Change
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.
- 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
Steve Gould If the date is set to an illegal value e.g., 31-Sep-2024, any formatting will not work on the cell. Even if the cell format is set to date, istext() will return true for this cell. When changing the value to a legal date value, the formatting will work as expected.
Add one more column to the right of the date column ( which is not changing the format ). Then use formula "=1*A2 ( or what ever the column of the date ) and then copy the formula to the entire column. Chenge the format to this new column to your desired date format and it will change. You can remove the formula after that if you want to delete the original column.
I was trying to convert the "MM/DD/YYYY" format to "YYYY-MM-DD" format. Saswat's `=1*A2` method didn't work directly with my original date format (e.g., "04/20/2024") but did work once I converted the date to "2024-04-20."
Here are the steps I used:
1. Add a first new column.
2. In this column, enter `=CONCATENATE(RIGHT(A2,4),"-",LEFT(A2,2),"-",MID(A2,4,2))`, which generates the format "2024-04-20."
3. Copy the value from this column to a second new column. Note that you still can’t change the format in this column.
4. Add a third new column and enter `=1*A2` here.
5. Copy the value from the third column to a fourth new column. Now, you can change the format in this column—this will serve as a true date column.Example:
| Original | 1st new col | 2nd new col| 3rd new col | 4th new col |
|--------------|--------------|--------------|---------------|----------------|
| 04/20/2024 | 2024-04-20 | 2024-04-20 | 2024-04-20 | April 20, 2024 |
| 04/21/2024 | 2024-04-21 | 2024-04-21 | 2024-04-21 | 2024-04-21 |You can replace your original column with the 4th new column and delete all temporary columns after.
This method works on my old Excel 2007.
- 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 Steve Gould Hi steve, i have the same problem too not until i tried to change my computer’s date format and language to “Europe English” try adding it to your language settings if you’re trying to format your date to “MMDDYY” or else if USA english which is the default “DDMMYY” or try typing the date first for example 23 November 2023. Then restart your computer then try again to format your date. Hope this helps solve your problem.
- This is excel bug & no one can fix it. Thats all. Basically it happens when data comes from CSV or text file
A similar thing happening here too
I'm on a pivot table with a separate sheet for the data. The date formats are all correct, field is unlocked, laptop's geeral settings are all right, the date column on the raw data sheet is all right and set to dd/mm/yyy, yet when it reflects onto the pivot table sheet it show up as "May" for example. Why is that? It's driving me crazy.
Would appreciate the help.
- This formatting problem occurred in my pivot table. If this is the case for anyone, you must first select the date cell column, select PivotTable Analyze, Ungroup.
- Recently there was an update that caused issues with Access. Hope a fix is found soon
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.
Has anyone found a solution to this? I am having your exact same issue. Tried windows settings, number formatting, custom formatting. I am at my wit's end. jwellesley2022
- Definitely, it is a bug in Excel. Open source spreadsheets (OpenOffice, LibreOffice) behave the way Excel should/did.
I am facing an issue starting this month and I also have a new laptop! When doing delimited the date format is not consistent across data set, for some it updated fine and some it doesn't. This issue never happened before. Can someone please help.
- 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.
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.
Apologies a few years late to the party on this but appears unresolved.
I was experiencing a similar issue and it was driving me mad but the check was ridiculously simple.
Have you got "Show Formulas" on?
If so choose Formulas from Ribbon at top & Click "Show Formulas" to Disable/Enable as appropriate.
Hope this works?
Cheers.Jschofield_
Steve Gould
