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
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.
That did the trick and much easier! Thank you!
