openpyxl version 3.0.7, python version 3.8.8
I’m trying to format cells as hours:minutes. From reading the openpyxl doc’s re formatting numbers: https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/numbers.html
FORMAT_DATE_TIME3 should do what I need. It uses BUILTIN_FORMATS(20) which is 'h:mm'. However, my implementation of the code does not have the expected results on the spreadsheet. The values come out as expected, but not with the formatting. I want the cells to be formatted as h:mm, so when I add 1:02 and 1:58 I get 3:00 ( for 3 hours). I've tried 2 different ways to format a cell (meth 1 & meth 2), with a sum formula for meth 1 column but I'm not quite there.
Any help would be appreciated, Thanks.
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import numbers
wb = Workbook()
ws = wb.active
ws['A3'] = "Meth 1"
ws['A4'] = "1:02"
ws['A5'] = "1:58"
ws['A4'].number_format = numbers.FORMAT_DATE_TIME3
ws['A5'].number_format = numbers.FORMAT_DATE_TIME3
print('A4 number format = ', ws['A4'].number_format)
print('A5 number format = ', ws['A5'].number_format)
cell = ws.cell(row=7, column=1, value='= SUM(A{}:A{})'.format(4, 5))
cell.number_format = numbers.FORMAT_DATE_TIME3
ws['C3'] = "Meth 2"
cell = ws.cell(row=4, column=3, value="1:02".format(numbers.FORMAT_DATE_TIME3))
cell.number_format = numbers.FORMAT_DATE_TIME3
cell = ws.cell(row=5, column=3, value="1:58")
cell.number_format = numbers.FORMAT_DATE_TIME3
wb.save(filename = 'hours_mins.xlsx')
I have printed the number format to verify it is the format required
A4 number format = h:mm
A5 number format = h:mm
You have entered the times as strings. As such, the formatting for numbers has no effect. You need to enter either datetime.time objects or datetime.timedelta objects in order for the formatting to have an effect, though openpyxl by default will try and set it correctly.
eg.
ws["A4"] = datetime.time(hours=1, minutes=2)