Reply to post: It's even worse than that

Geneticists throw hands in the air, change gene naming rules to finally stop Microsoft Excel eating their data

Mike 137 Silver badge

It's even worse than that

Excel doesn't actually change e.g. 'DEC1' to '01-Dec' when your enter it. That's just what it shows the user. It actually modifies the data into the internal clock tick representation of the date (in this case the number 44166). Because of this, if you reformat the cells to "general" and back to "date", it now reads "01/12/2020", which is even further from your original entered data, but is still 44166 internally..

This has always been the default behaviour for anything that looks remotely like a date. It can only be overridden by opening Excel and then importing the data. At that point you get to specify how it's to be interpreted. We had this problem once when analysing a c. 10,000 entry password dump - almost half the passwords were corrupted by Excel.

POST COMMENT House rules

Not a member of The Register? Create a new account here.

  • Enter your comment

  • Add an icon

Anonymous cowards cannot choose their icon


Biting the hand that feeds IT © 1998–2020