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
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.
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.
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
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
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.
- 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!
- 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!
- 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.
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!
Steve Gould Was this solved? I had the same problem and I got to fix it
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 GouldI 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.
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.
- 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.
I am having the same issue converting Nov 01 2021 10:30 to 11/01/2021 10:30 it does nothing no matter what I try.
Has anyone found a solution to this? I am having your exact same issue. Tried windows settings, number formatting, custom formatting. I am at my wit's end. jwellesley2022
- Definitely, it is a bug in Excel. Open source spreadsheets (OpenOffice, LibreOffice) behave the way Excel should/did.
- Recently there was an update that caused issues with Access. Hope a fix is found soon
- This formatting problem occurred in my pivot table. If this is the case for anyone, you must first select the date cell column, select PivotTable Analyze, Ungroup.
A similar thing happening here too
I'm on a pivot table with a separate sheet for the data. The date formats are all correct, field is unlocked, laptop's geeral settings are all right, the date column on the raw data sheet is all right and set to dd/mm/yyy, yet when it reflects onto the pivot table sheet it show up as "May" for example. Why is that? It's driving me crazy.
Would appreciate the help.
