Forum Discussion
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 change the cells to any other type of cell - general, text, number, time, whatever - nothing changes.
Please help.
- 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
84 Replies
I am facing an issue starting this month and I also have a new laptop! When doing delimited the date format is not consistent across data set, for some it updated fine and some it doesn't. This issue never happened before. Can someone please help.
- I am still facing the same issue when the data is extracted. I think its how the data is extracted it is some form of hard code which isn't recognised in excel formats.
Most probably on your new laptop regional setting are differ from what it was before. I have no idea in which locale you are, try to check Time & Language section in Windows settings.
Apologies a few years late to the party on this but appears unresolved.
I was experiencing a similar issue and it was driving me mad but the check was ridiculously simple.
Have you got "Show Formulas" on?
If so choose Formulas from Ribbon at top & Click "Show Formulas" to Disable/Enable as appropriate.
Hope this works?
Cheers.Jschofield_
Steve GouldSteve Gould Was this solved? I had the same problem and I got to fix it
- I have tried date to text and everything else mentioned here. But nothing changes the date. Everything with a formula changes as normal. But everything else won't change no matter what formatting is used. The file was exported as CVS and then saved as an excel file. Please help, this is doing my head!
It looks like you are trying to read US dates into a 'rest of world' setting. The dates like 11/12/2002 are treated at dates (the wrong date because it was meant as November but is read as December) whereas 1/26/2002 would be left as text because Excel fails to recognise the 26 month of the year. The best solution is to start again and use Power Query or similar to reimport the data.
The alternative is to play games and 'unscramble the eggs' with DATE, DAY and MONTH for the numbers and SEARH, MID, CONCATENATE and DATEVALUE for the text. Not nice!
- Have you tried the Text-to-Columns trick akaal_sahaye mentioned above? It worked for me. Kind of crazy to have to do this as a work-around, but now that I know it, it is fairly simple to implement.
- Yes I have tried this method first. The cells which have a formula work but its the cell that have date in them and not a number when I show the formula that do not work. So I am assuming that when the data is imported the dates are some how hard coded. You cannot change the format to anything at all.
I use large datasets and I find it way easier to import the data in Access, change the format there, then copy it back into Excel. It takes way less time than trying to figure out what's wrong with Excel formats
That's at least discussable what easier to use - Text to Columns, Power Query, Access, VBA programming or something else. Depends on what do you know better. If Excel, when Text to Columns or like. If another tool, when it.
- Hi! Can you please help me. Seems like imported data in excel is in different format, and dates I see excel reads as a text. I am working on more that 7000 rows of dates therefore cannot manually change date from 2018.09.28 to 09/28/2018 in order to work with it. Can you please help me? I tried text to column it didn't work for me
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
