Mar 21 2018 08:05 AM - last edited on Nov 09 2023 11:10 AM by
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.
Dec 17 2020 03:18 AM
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.
Dec 20 2020 06:25 AM
Dec 20 2020 06:41 AM
Mar 01 2021 07:25 AM
Mar 01 2021 07:31 AM
Mar 01 2021 10:39 AM
Mar 01 2021 12:14 PM
Mar 01 2021 01:40 PM
Mar 05 2021 08:45 AM
Mar 05 2021 09:22 AM
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!
Mar 23 2021 10:01 AM
Mar 23 2021 02:53 PM
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
Now you shall have dates in your locale format
(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.
Mar 24 2021 12:39 AM
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.
Mar 24 2021 10:49 AM
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.. :)
Mar 29 2021 04:23 AM
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.
Jun 30 2021 06:43 PM
@Steve Gould Was this solved? I had the same problem and I got to fix it
Aug 26 2021 06:49 AM
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
Sep 22 2021 02:28 AM
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.