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
Hi Steve,
I guess you have text in your column, not dates (which are actually numbers). To convert you may use from ribbon Data->Text to Columns selecting Date and applying MDY on the third stage of the wizard.
Thanks a lot.
To change date format on excel sheet, we have to change region format first from computer setting first, then we will be able to change the date format in cell of excel sheet, thats how i solve it.
thank you
- I am using excel365 online from a sharepoint file. I have my computer set as default to UK date format and everytime I open the file I reset the dateformat to UK using file options, control 1, and then setting the file format to UK. But to no avail, the file instantly defaults to US and the cells do not change globally. Worse still some of cells are in US format and some in UK.
I just want to use UK format and have followed all the advice given in this thread and others.Excel for web has no idea about regional settings on your computer, you need to change them on web (SharePoint/OneDrive site) as well. That's in File->Options within Excel for web
I have just tried your solution again since there were a few earlier dates that I had not changed. this is the result. As you can see the change is not global. Some cells change others don't.
Thank you so much for this resolution. I have been banging my head against a wall for days trying to determine what I Was doing wrong! I appreciate the step by step instruction. Bravo, and thank you again!
Man you guys just saved my life .. let me follow you on twitter SergeiBaklan
Sorry, I don't use Twitter
- It simply does not work.
- I have the same problem. If a cell has information in it already (a date that Excel identifies as text) then the Text to Columns trick works fine and the dates are converted to numbers, but it doesn't to anything to cells with no data in it. This is very frustrating, even with brand new excel files there doesn't seem to be a way to format blank cells to anything other than text.
Dear Sergei,
this was an older question but I have the same problem and was wondering if you may be able to assist.
I have a brand new Sheet and want to have 8 columns and 3 of them are a date.
I have inserted 10.10.2018. Then I go to Format Cells / Date / 14 March 2012 and hit ok.
Now the date should be shown as 10 October 2018 but it does not change at all.
Also tried other custom variants for dates but it just stays as it is as 10.10.2018.
Is there any general setting that is wrong?
Thank you
regards
Chris
- I see. The problem is you are reading it as date, but system is reading it as text. First replace "." [dot] with "/", then go to Data and use Text to columns feature. Your problem will be solved.
Hi Chris,
What is your default (system) date format? To check you may hit Ctrl+1 on any cell and select Date. First two formats in the right pane will be marked by asterisk, these are so called short and long dates. In my case it looks like
You shall enter dates as in first record, i.e. 10/10/2018 (instead of 10.10.2018) if you see in above 14/03/2012. Otherwise you have some text in form of dates, not dates as Excel recognizes them.
In brief, most probably you enter dates as text
- This solved my issue. thankss!
I did that. Didn't do anything.
Steve,
After applying Sergei's solution, select the dates, press Ctrl+1 and then select Custom category.
After that, copy the format YYYY/MM/DD to the Type box, and then hit OK.
- hello, I have a similar problem, I have a whole column of dates (presumably stored as text). I have tried many options(including this) to format as date but nothing is changing
