Mac base date is not used correctly

Issue #1049 resolved
Ben Webb created an issue

Openpyxl reads the property from the workbook, and correctly propagates this information to the base_date attribute on a cell. However, the actual value attribute is wrong.

e.g. using https://assets.publishing.service.gov.uk/government/uploads/system/uploads/attachment_data/file/663589/GGIS_Grant_Awards_2016_to_2017_2017-10-27_1621.xlsx

>>> import openpyxl
>>> wb = openpyxl.load_workbook('/home/bjwebb/Downloads/GGIS_Grant_Awards_2016_to_2017_2017-10-27_1621.xlsx')
/home/bjwebb/opendataservices/cove/.ve/lib/python3.5/site-packages/openpyxl/reader/worksheet.py:318: UserWarning: Unknown extension is not supported and will be removed
  warn(msg)
>>> wb.excel_base_date
2416480.5
>>> from openpyxl.utils.datetime import CALENDAR_MAC_1904
>>> CALENDAR_MAC_1904
2416480.5
>>> from openpyxl.utils.datetime import CALENDAR_WINDOWS_1900
>>> CALENDAR_WINDOWS_1900
2415018.5
>>> wb['GGIS_Grant_Awards_2016_to_2017 ']['F2'].value
datetime.datetime(2012, 10, 2, 0, 0)
>>> # We expected datetime.datetime(2016, 10, 3, 0, 0)
>>> wb['GGIS_Grant_Awards_2016_to_2017 ']['F2'].base_date
2416480.5
>>> wb['GGIS_Grant_Awards_2016_to_2017 ']['F2'].base_date is CALENDAR_MAC_1904
True

I notice that the from_excel method appears to do what I want, but this is called without an offset in openpyxl.reader.worksheet, and is commented out in openpyxl.cell.cell.

Comments (4)

  1. CharlieC

    Trust the UK government to be using Mac 2008 to create files. Interesting also that the timestamp is a string and not a datetime object.

    I think this became an issue when we switched from calculating datetimes on the fly to doing it when parsing and we were obviously missing a test.

  2. CharlieC

    I have to merge your changes into 2.6 and discovered that we were writing the files incorrectly, though I think it might be an idea to coerce the 1900 epoch to simplifiy this and anyone using the 1904 workaround for older dates can complain to Microsoft.

  3. Log in to comment