Dates in Excel are all mixed up and won't convert

Closed
Report
Posts
1
Registration date
Monday June 26, 2017
Status
Member
Last seen
June 26, 2017
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
Hi, I downloaded some data from my bank for some transactions.

Using MS Excel 2008 V 12.2 on a Imac 2015

I usually convert the dates to YYYY-MM-DD (System International Standard) so there is absolutely no confusion ever (as what 11-02-04 would mean), as with other formats, at any time in the future.

However, when I convert the data in the cell to YYYY-MM-DD using format, half the dates never convert. They remain 26/07/2015. The strange thing is there is no real consistency, some entries convert, some don't. It seems strange if an engineer would program their dates to different formats. I've tried everything, including manually deleting the entire contents of the cell, and replacing it with 2015-07-26, then it converts to July 7, 2015. I've researched this with no luck on the web, even looking at some answers in this forum. Would appreciate any help. I can try to post the sample excel here or a screen shot if possible.

26/04/2015 27/04/2015
29/04/2015 30/04/2015

2015-04-06 2015-05-06
14/06/2015 15/06/2015



June 29, 2015 30/06/2015
2015-11-07 13/07/2015
2015-11-07 13/07/2015
2015-11-07 13/07/2015
2015-07-13 14/07/2015

26/07/2015 27/07/2015
26/07/2015 27/07/2015
26/07/2015 27/07/2015
28/07/2015 29/07/2015
28/07/2015 29/07/2015
2015-08-08 2015-10-08
2015-08-08 2015-10-08
2015-08-08 2015-10-08
20/08/2015 21/08/2015
20/08/2015 21/08/2015
23/08/2015 24/08/2015
23/08/2015 24/08/2015

Note that in August, only one date reformatted properly. Also in April. The June 29th one spelled out, I tried to convert and it shows up like this, and won't change back to 29/06/2015 even if I delete the whole contents of the cell, and type it in manually.

Am not a techie, so would a appreciate simple step by step instructions on how to fix if you can help. Also a layman's terms to explain why this is happening would be great. it seems really mindboggling to me how/why/if an software engineer? would change date formats in mid month or whatever, and also why the date won't change properly even with complete deletion and manual input.

My other question, out of interest, is that I assume hundreds of brilliant engineers design Excel. And in decades of existence, how come the International SI standard of YYYY-MM-DD, the first format I would include on day one, is NOT even an option in the drop down field?

Thanks in advance for your kind assistance,

Stuey

1 reply

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Stuey,

When I select all your dates and paste it in Excel, then the dates are automatically converted to a matching format.

This could be a Mac thing, which I have no experience with.

Something you can try is using a formula:
=TEXT(A1,"YYYY-MM-DD")

As for your other question, my quess would be localization. The default date format in my country (Netherlands) is dd-mm-jjjj.

Best regards,
Trowa