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
Text to Columns could work if on third step of the wizard properly select date format.
Data > Text to Columns with date format worked for me, thanks.
Before that, even pressing F2 to edit the cell and press enter was insufficient to have it auto-recognize as a date, which seemed odd to me.
- Thank you! This was the answer.
- This is also solution of f2+enter key when format do not work on date mm/dd/yyyy or any date format.
- Hye..
This is not a bug oh ms excel.
You have to justify that where is your date and where is your month in the date..
For example data is 11/12/2020 (in this case excel can understand mm/dd/yyyy and also dd/mm/yyyy both of them you can aply on it) but of data is 24/12/2020 the excel (in this case you cant apply mm/dd/yyyy)
So that..
Firstly you have to justify your data to excel where is date and where is month in the column for that steps are :
1. Select the column or data do you want to change in any format..
2. Then click on data tab.
3. Click on Text to columns.
4. Step 1 of 3 is Delimited (defult selected option) then next.
5. In step 2 of 3 just tick on tab and treat consecutive delimiters as one. Click next.
6. Here on the date option you can tell that where is your date and month in the column.. MDY stands for MONTH DATE YEAR, DMY stands for DATE MONTH YEAR. Select the option as your data in column.
7. Click on finish..
Now you can format your date month year in excel as custom options as well as date options.
Please click on like button if useful : ) MarcinSkoczylas I suspect you have "Show formulas" switch on.
This looks like a bug in the Excel. I hit the same problem just a few days ago in spreadsheet that was created month ago and dates were displayed correctly. Now my dates are displayed as numbers and whatever I do to change the format of affected cells to Date the format does not change and they are always displayed as a number or formula text. Selecting columns or whole row with dates, then going to Format/Cell and changing format does nothing. Interesting is that changing that to any other format does not work (i.e. nothing happens when I change the format). Please check screenshot attached. Microsoft, please fix this bug as soon as possible!
