Date Formatting Won't Change

Copper Contributor

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. 

68 Replies

@Tibby 

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.

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 : )
This is also solution of f2+enter key when format do not work on date mm/dd/yyyy or any date format.
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.
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.
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.

@ShanurMiah 

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!

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

@Rano605 

If that's one time operation you may select column with texts, on ribbon Data->Text to Columns and on third step of the wizard select source date formar

image.png

Now you shall have dates in your locale format

image.png

(I use ISO format) or apply any one which is more suitable for you.

 

If you import data on regular basis it's better to make such transformation within importing process, e.g. Power Query works fine with that.

@Rano605 

An additional column in the import table could also be useful.  Replacing the period "." by hyphen "-" or forward slash "/" using

= --SUBSTITUTE([@DateText],".","/")

would give a date value column.

image.png

@Big_Wheelz 

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.. :)

 

 

Thanks a lot

@Cthomason 

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.

@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 Gould 

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.

 

ExcelRT_0-1632302843410.png