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 cha...
- 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
Thanks Sergei,
I found the general settings of windows and amended it. Now it is the way I wanted it.
Thanks so much for your help. Much appreciated and glad it worked out after all.
And also the little formula for the due date works.
Such a small thing to change but if you don't know where it makes you pulling your hair.
I was not aware it is in general settings but thought it must be somewhere in excel :-)
kind regards
Chris
Also, the only way I could get dates in Excel formatted as Text to Date was to use the DATEVALUE formula. (Text to columns did not work for me)
- Thank you! This was the answer.
For those still having this problem, I was able to fix it by doing the following;
1. Change your Windows Date Settings to MM/DD/YYYY as seen in the screenshot below. This is the default setting for Windows, and what excel was designed to work with.
2. Open a new workbook and enter a date in this format: MM/DD/YYYY. Then try changing its formatting (it should work with the Windows date setting above).
3. Once you confirm you can change the date format, then you can change the Windows Date setting back to what it was, so when you open a CSV file it will display the same date format as your Windows OS.
- I had the same issue and you just saved my tuchas. THANK YOU!!!
- Thank you! This helped me solve my issue after attempting all of the above this actually fixed it.
As a comment, that only works if the text represents dates as in your locale. In this case you may click Finish on first step. if not, you shall select Date and date format of the source on the third step.
your message is a year old, you may not need this, but highlight column of numbers to reformat from text to date or number.
2. Go to Data, select Text to Columns
3 Select Fixed width - then next
4 next screen do not do anything except select "next"
5. Select General under the column data format and then select finish
Once this is done you can select the range of cells and format them into date, numbers, etc.. It breaks the spell.. 🙂
- 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!
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.
Text to Columns could work if on third step of the wizard properly select date format.
