How to output durations in HHHH:MM format with xlsxwriter in Python

I am trying to output durations to an Excel spreadsheet from Python with xslxwriter.

I have the following code:

from datetime import datetime, timedelta
import xlsxwriter

xlsx_filename = f'foo.xlsx'
workbook = xlsxwriter.Workbook(xlsx_filename)
worksheet = workbook.add_worksheet()
time_format = workbook.add_format({'num_format': 'h:mm'})
time = 86460 # A day and a minute
duration = timedelta(
    hours=time // 3600,
    minutes=(time % 3600) // 60,
    seconds=time % 60
)
worksheet.write_datetime(0, 0, duration, time_format)
workbook.close()

The spreadsheet that is output contains in its single cell 0:01, i.e. zero hours and one minute. I am trying to get to get it to say 24:01.

I cannot find a way to do this with xslswriter, though there’s no problem representing that value in Excel. I have tried using the write_number method instead with a similar format, but that just outputs zeroes.

So far, the best I can manage is to write the duration as a string, and then convert it to a number in Excel.

Answer

It isn’t an XlsxWriter issue. You just need to specify the correct number format for Excel which in this case is something like [h]:mm. The brackets will give a 24h+ format.

Like this:

from datetime import datetime, timedelta
import xlsxwriter

xlsx_filename = f'foo.xlsx'
workbook = xlsxwriter.Workbook(xlsx_filename)
worksheet = workbook.add_worksheet()

time_format = workbook.add_format({'num_format': '[h]:mm'})

time = 86460 # A day and a minute
duration = timedelta(
    hours=time // 3600,
    minutes=(time % 3600) // 60,
    seconds=time % 60
)

worksheet.write_datetime(0, 0, duration, time_format)

workbook.close()

Output: