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
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.
IMHO, bit easier use Text to Columns as mentioned above (select column A, Data->Text to Columns, on third step of the wizard select Date and MDY)
If formula conversion is more preferable, we may cut number of steps using
=DATE( RIGHT(A2,4), LEFT(A2,2), MID(A2,4,2) )which returns the date directly.
