Is there a way to read duration type field from excel with Python openpyxl?

Question

I am trying to read duration field from Excel file, but instead of timedelta type I get datetime type. So when duration value is more than 24 hours, python openpyxl reads that field not correctly. Is there a right way to read duration field with Python openpyxl?

import openpyxl

workbook = openpyxl.load_workbook('./files/file.xlsx')
worksheet = workbook.active

cell = worksheet.cell(row=2, column=9)
print(cell.value)

Cell (row=2, column=9) value in excel file is 38:12:40, but printed output:

1900-01-01 14:12:40

Cell format code in excel file - [HH]:MM:SS

Jose Macedo Jose Macedo · Accepted Answer · 2019-09-10T16:35:01

I think this solution will work for you, maybe you need to adjust the format but the type is timedelta

cell = worksheet.cell(row=1, column=1)
print(cell.value) #1900-01-01 14:12:40
s = cell.value-datetime.datetime(1899,12,30)
print(s) #2 days, 14:12:40
print(type(s)) #<class 'datetime.timedelta'>

The problem is that Excel have a custom type and when you put that format, it will convert it for that one starting in 1900

Is there a way to read duration type field from excel with Python openpyxl?

1 Answers