0

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

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.

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,

Stu

4
  • care to share how exactly you "convert" the dates? With a formula? With a ribbon command? It's quite apparent that the dates are in DMY order, but the conversion seems to apply MDY order, which is why only a few dates in August are converted. – teylyn Jun 27 '17 at 0:11
  • (1) What teylyn said: please explain more clearly how you are converting the dates.  (2) What I believe teylyn was saying: It seems fairly obvious that the dates that converted successfully have a day ≤12, and the ones that didn’t have a day >12. – G-Man Says 'Reinstate Monica' Jun 27 '17 at 1:08
  • Hi Telyn, Thanks for your reply. I simply use Format > Cells > Number > Custom > YYYY-MM-DD.. if you notice it worked in one date in August, not the rest – stu Jun 28 '17 at 1:16
  • G-Man, ok, but if the 4 digit number is obviously a year, and the the numbers over 12 are obviously dates, wouldn't it know to convert all the dates in the column properly? I've done this for years with no issues with other data. – stu Jun 28 '17 at 1:17
2

If you attempt to change the format of a cell containing a "date", and the display does not change, then the cell contains Text rather than a true date. You can verify this by toggling Ctrl+`. This will cause true dates to revert to numbers. Text cells will remain unchanged.

Say column A contains a mixture of true dates and Text. In B1 enter:

=IF(ISNUMBER(A1),A1,DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2)))

and copy downwards. Column B values will all be dates and will all accept formatting:

enter image description here

Please note the custom format.

If your system supports VBA, you can write a macro to correct values "in-place".

3
  • Hi Gary's Student, reminder, am NOT a techie, have no idea what VBA is or how to write a macro...;-) – stu Jun 28 '17 at 1:18
  • @stu Then stick with the helper column approach. – Gary's Student Jun 28 '17 at 1:25
  • hi, I spent some time replying to your answer, but it all disappeared!! ;-( I tried adding two columns and pasting that string, and I get Value! in the cell.... Is there ANY way I can attach a sample file or email anyone who cares to try to fix this, so you know exactly what's going on, and save everyone a ton of back and forth? Many thanks! – stu Jun 28 '17 at 2:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.