datetime.date object stored in cell not immediately converted to datetime

Issue #1163 invalid
Mark Dickinson created an issue

If I store a datetime.date object in a worksheet cell and then immediately retrieve it, I get back the same datetime.date object. If instead I store the date object in a cell, write the workbook to disk, and then reload, when I retrieve the cell contents I get a datetime.datetime object (with zero time part).

Either of these behaviours seems reasonable to me, but I'd expect to get the same type of object back regardless of whether I've saved the workbook out to disk or not.

This appears to be a regression from openpyxl 2.4, where a date object stored in a cell is immediately converted to datetime.

(Versions: Python 3.7.2, macOS 10.14.3, openpyxl 2.5.14.)

Here's an interpreter session demonstrating the issue.

Python 3.7.2 (default, Dec 30 2018, 08:55:50) 
[Clang 10.0.0 (clang-1000.11.45.5)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import openpyxl
>>> openpyxl.__version__
'2.5.14'
>>> wb = openpyxl.Workbook()
>>> ws = wb.active
>>> import datetime
>>> ws["A1"] = datetime.date.today()
>>> type(ws["A1"].value)  # expected to see datetime.datetime here
<class 'datetime.date'>
>>> wb.save("temp.xlsx")
>>> wb = openpyxl.load_workbook("temp.xlsx")
>>> ws = wb.active
>>> type(ws["A1"].value)
<class 'datetime.datetime'>  # as expected

I'm not sure it's my place to put a priority on this, but the default priority of "major" seems like an overstatement, so I've made it "minor".

Comments (4)

  1. CharlieC

    You're assumption is flawed. Excel treats dates and datetimes invariably as numbers and relies solely on formatting to make them into dates and datetimes. This is what breaks consistency for types. openpyxl's does its best with what it finds but defaults to the ISO format for datetimes.

    There is a datetime type in the OOXML specification and you can force openpyxl to use (and respect it) by setting iso_dates=Trueon the workbook but Excel still requires a time format otherwise it will just convert them to serials.

    Basically, the file format is not suited to reliably storing dates and times.

  2. Mark Dickinson reporter

    Just to make sure I understand this fully: it's intentional that saving and reloading a workbook changes the data types?

    I guess the safe way to work is never to store date objects, but instead only ever store datetime objects in a worksheet cell?

  3. CharlieC

    It's not the intention of the library to change types, it's a sloppy specification. We use the iso format for dates and datetimes as the default format for both because it is arguably the safest, even if, as you're seeing, it removes the distinction (the file format doesn't know the difference). You can easily overwrite this by setting your own number format for dates. openpyxl will respect this when it reads files.

    The safest thing is not to rely on the file format for dates or datetimes.

  4. Mark Dickinson reporter

    The safest thing is not to rely on the file format for dates or datetimes.

    Got it. Thanks. (And thank you for the quick response.)

  5. Log in to comment