Skip to content

GitLab

  • Projects
  • Groups
  • Snippets
  • Help
    • Loading...
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Sign in / Register
O
openpyxl
  • Project overview
    • Project overview
    • Details
    • Activity
    • Releases
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributors
    • Graph
    • Compare
  • Issues 200
    • Issues 200
    • List
    • Boards
    • Labels
    • Service Desk
    • Milestones
  • Merge Requests 2
    • Merge Requests 2
  • CI/CD
    • CI/CD
    • Pipelines
    • Jobs
    • Schedules
  • Operations
    • Operations
    • Incidents
    • Environments
  • Packages & Registries
    • Packages & Registries
    • Container Registry
  • Analytics
    • Analytics
    • CI/CD
    • Repository
    • Value Stream
  • Snippets
    • Snippets
  • Members
    • Members
  • Activity
  • Graph
  • Create a new issue
  • Jobs
  • Commits
  • Issue Boards
Collapse sidebar
  • openpyxl
  • openpyxl
  • Issues
  • #1588

Closed
Open
Created Jan 13, 2021 by nmartensen@nmartensenDeveloper

not possible to correctly convert excel dates to timedelta

At work we use excel files and pandas for tracking the employee time spent on our projects, and overtime etc. Our scripts stopped working after a pandas upgrade this week. The reason is that pandas.read_excel() in the latest version now defaults to using openpyxl for reading xlsx files, and dates read differ between the previous default engine 'xlrd' and the new default 'openpyxl'. Unfortunately the results obtained with openpyxl cannot be correctly translated to the timedelta values they are meant to be.

Here is a short notebook to illustrate the issue:

import datetime as dt
import openpyxl
import xlrd
import pandas as pd
def testroundtrip(tobj):
    wb = openpyxl.Workbook()
    ws = wb.active
    ws["A1"] = tobj
    wb.save('tada.xlsx')
    opxlin = ws['A1'].value.total_seconds()

    wb2 = openpyxl.load_workbook('tada.xlsx')
    ws2 = wb2.active
    opxlout = ws2['A1'].value

    wb3 = xlrd.open_workbook("tada.xlsx")
    ws3 = wb3.sheet_by_index(0)
    xlrdout = xlrd.xldate.xldate_as_datetime(ws3.cell_value(rowx=0, colx=0),0)

    pdout = pd.read_excel("tada.xlsx", header=None, engine='xlrd').iloc[0,0]

    return f"Seconds: {str(opxlin):12} opxl:{str(opxlout):23} xlrd:{str(xlrdout):23} pd:{pdout}"
for tobj in (
    dt.timedelta(hours=-24, seconds=-1),
    dt.timedelta(hours=-24, seconds=0),
    dt.timedelta(hours=-24, seconds=1),
    dt.timedelta(hours=0, seconds=-1),
    dt.timedelta(hours=0, seconds=0),
    dt.timedelta(hours=0, seconds=1),
    dt.timedelta(hours=24, seconds=-1),
    dt.timedelta(hours=24, seconds=0),
    dt.timedelta(hours=24, seconds=1),
):
    print(testroundtrip(tobj))
/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:16: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.
  app.launch_new_instance()


Seconds: -86401.0     opxl:1899-12-28 23:59:59     xlrd:1899-12-29 23:59:59     pd:1899-12-29 23:59:59
Seconds: -86400.0     opxl:1899-12-29 00:00:00     xlrd:1899-12-30 00:00:00     pd:1899-12-30 00:00:00
Seconds: -86399.0     opxl:00:00:01                xlrd:1899-12-30 00:00:01     pd:1899-12-30 00:00:01
Seconds: -1.0         opxl:23:59:59                xlrd:1899-12-30 23:59:59     pd:1899-12-30 23:59:59
Seconds: 0.0          opxl:1899-12-30 00:00:00     xlrd:1899-12-31 00:00:00     pd:00:00:00
Seconds: 1.0          opxl:00:00:01                xlrd:1899-12-31 00:00:01     pd:00:00:01
Seconds: 86399.0      opxl:23:59:59                xlrd:1899-12-31 23:59:59     pd:23:59:59
Seconds: 86400.0      opxl:1899-12-31 00:00:00     xlrd:1900-01-01 00:00:00     pd:1900-01-01 00:00:00
Seconds: 86401.0      opxl:1900-01-01 00:00:01     xlrd:1900-01-01 00:00:01     pd:1900-01-01 00:00:01

The date values obtained by xlrd.xldate.xldate_as_datetime() can be translated back into the desired timedeltas by subtracting datetime(1899,12,31). This does not work with the values obtained from openpyxl:

  • All values from excel date <= 1 that are returned as datetime are off by 24 hours compared to xlrd. (Since the shift is towards earlier date, we could in theory detect this and work around it. It would still be nice if that weren't necessary.)
  • Negative timedeltas of less than 24 hours absolute duration are indistinguishable from positive timedeltas, and the absolute duration is also wrong (24 hours minus correct absolute duration). This cannot be detected and worked around.

When using the 'xlrd' engine, pandas.read_excel() uses xlrd.xldate.xldate_as_datetime() to read date values and then drops the date part on the day where the excel date number value is >= 0 and < 1. Would it make sense for openpyxl to implement the same behavior?

Edited Jan 13, 2021 by nmartensen
Assignee
Assign to
None
Milestone
None
Assign milestone
Time tracking
None
Due date
None