Time '0:00' is not saved correctly after loading and saving a file

Issue #1043 duplicate
Raf Geens
created an issue

Midnight seems to produce a strange result when loaded and saved through openpyxl. Other times did not seem affected.

Steps to reproduce:

  1. In Excel, format a cell as Time with an English (US) locale. Put a 0 value in it.
  2. It will display as '0:00' and the cell contents will show as '00:00:00'.
  3. Format another cell as Custom with the 'hh:mm' format. Put a 0 value in it.
  4. It will display as '00:00' and the cell contents will show as '00:00:00'.
  5. Save the file as time_example.xlsx (see attachment)
  6. Load and save the file in openpyxl:
import openpyxl
wb = openpyxl.load_workbook('time_example.xlsx')
wb.save('test.xlsx')
  • 7a. Open test.xlsx in Excel. Both cells will display as hash signs and the contents will be -1.
  • 7b. Open test.xlsx in LibreOffice. Both cells will display as normal (besides the custom field showing as 0:00 instead of 00:00), but the contents will be '-24:00:00'.
  • 7c. Open test.xlsx in Google Spreadsheets. Both cells will display as normal (besides the custom field showing as 0:00 instead of 00:00), but the contents will be '29/12/1899 00:00:00'

Versions:

  • openpyxl: 2.5.3
  • Python: 3.5.5
  • Excel: 2016, Version 1803
  • LibreOffice: 5.4.6.2

Comments (4)

  1. CharlieC

    The real problem is that Excel uses an error-prone system for dates and times that relies on formatting and persisting a decades-old bug from Lotus 1-2-3. As such there are some values which simply cannot be persisted reliably. For example "0" formatted as a date is "0th January 1900" to Excel. This sort of makes it impossible to follow both the specification and do what Excel does, which is what most people expect. To avoid this we added the option to store datetimes in ISO form and hence avoid ambiguity: set iso_dates=True on the workbook. Support for this is not universal.

  2. Raf Geens reporter

    Thanks for the response. I've tried iso_dates, which results in LibreOffice not having the issue anymore when opening the file. In Excel I get '#VALUE!' as both the content and displayed value. I also read https://github.com/eawag-rdm/openpyxl_date-handling-test , and according to that even if it ends up working correctly in Excel, it can still get messed up by Excel reading and writing the file. Since I don't do any calculations on those fields, it would seem using strings instead is the safest.

    When looking up iso_dates, I noticed it wasn't documented. Would you like me to create an issue for that?

  3. CharlieC

    Excel 2016 should be able to handle ISO dates, at least it can on my Mac. Older versions won't and OpenOffice doesn't either. 0 is sort of an edge case: Excel really just doesn't do dates very well – anything before 1900-01-01 is a joke – so, yeah strings is much safer.

    Rather than a new issue I'd prefer a PR based on the 2.5 branch with user-friendly documentation, ie. written by someone who might use the feature. The code that handles times can also be peer-reviewed in case there is a possible fix to 0.

  4. Log in to comment