Format Durations Over 24 Hours in Excel with Python

Durations are everywhere in operational reports — ticket resolution times, machine run hours, time worked per person per week — and they are the values most often displayed wrongly. A ticket open for 26 hours 30 minutes shows as 2:30, as 1, or as a date in January 1900, depending on how it was written. All three come from the same fact: Excel stores a duration as a number of days, and the number format decides whether that is shown as a time of day, a date, a whole number or total elapsed time. This page, part of Applying Number and Date Formats in Excel, writes durations from pandas and openpyxl so they display as intended.

Prerequisites

pip install pandas openpyxl xlsxwriter

tickets.xlsx has four support tickets with opened and closed timestamps; the report shows how long each was open.

Step 1 — See what pandas writes for a timedelta

Subtracting two datetime columns gives a timedelta column. Write it with to_excel and look at what reaches the file:

import pandas as pd
from openpyxl import load_workbook

tickets = pd.read_excel("tickets.xlsx")
tickets["Open for"] = tickets["Closed"] - tickets["Opened"]
print(tickets[["Ticket", "Open for"]].to_string(index=False))

tickets.to_excel("durations_default.xlsx", index=False)
ws = load_workbook("durations_default.xlsx").active
print([(round(c.value, 4), c.number_format) for c in ws["D"][1:]])

Each duration is stored as a fraction of a day — T-101's 26 hours 30 minutes is 1.1042 — with the number format 0, which rounds to whole days. In Excel the column shows 1, 0, 2 and 0: technically days, practically useless. The stored numbers are right; only the format needs changing.

Step 2 — Apply a bracketed hours format

Hours in square brackets mean total elapsed hours. [h]:mm shows 26:30; h:mm, without brackets, shows the time of day and wraps every 24 hours, so the same value shows 2:30. There is one catch when applying it: pandas gives every timedelta cell its own 0 format, and a cell's own format beats the column's, so xlsxwriter's set_column alone changes nothing. Rewrite the duration cells with the format attached:

with pd.ExcelWriter("durations.xlsx", engine="xlsxwriter", datetime_format="yyyy-mm-dd hh:mm") as writer:
    tickets.to_excel(writer, index=False, sheet_name="Tickets")
    wb, ws = writer.book, writer.sheets["Tickets"]
    elapsed = wb.add_format({"num_format": "[h]:mm"})
    for row, value in enumerate(tickets["Open for"], start=1):
        ws.write_number(row, 3, value.total_seconds() / 86400, elapsed)
    ws.set_column("B:C", 18)
    ws.set_column("D:D", 12)

check = load_workbook("durations.xlsx").active
print([(c.value, c.number_format) for c in check["D"][1:]])

Dividing the seconds by 86,400 gives the fraction of a day Excel expects. The datetime_format argument handles the Opened and Closed timestamps the same way — pandas applies it to every datetime cell it writes. With the openpyxl engine, loop over the column after writing and set cell.number_format = "[h]:mm" on each cell.

The column now reads 26:30, 7:45, 67:45 and 0:40. With a bracketed format, openpyxl also reads the cells back as timedelta values rather than floats — it recognises a duration format. [h]:mm:ss adds seconds; [mm] shows total minutes (1590 for T-101) and [ss] total seconds, useful for short machine cycle times.

Step 3 — Show decimal hours when people calculate with them

Hours worked are often multiplied by a rate, and 26:30 × 45 means nothing to most readers. Decimal hours — 26.5 — are easier to use. Because Excel stores durations in days, decimal hours are the duration times 24. In pandas, convert the timedelta directly:

tickets["Hours"] = (tickets["Open for"].dt.total_seconds() / 3600).round(2)
print(tickets[["Ticket", "Open for", "Hours"]].to_string(index=False))

T-101 is 26.5 hours and T-103 67.75. Write this column with a 0.00 format. When the workbook should keep both and stay live, write a formula instead — =D2*24 — so the decimal column follows any edit to the duration.

Step 4 — Total durations with formulas that keep the format

A SUM of durations is itself a duration, and needs the bracketed format too — the total of a week's hours is almost always over 24. Write the totals with openpyxl and check how they display:

from datetime import timedelta
from openpyxl import Workbook

wb = Workbook(); ws = wb.active
ws.append(["Ticket", "Open for", "Hours"])
for i, r in enumerate(tickets.itertuples(index=False), start=2):
    ws.append([r.Ticket, r[3].to_pytimedelta(), f"=B{i}*24"])
ws.append(["Total", "=SUM(B2:B5)", "=SUM(C2:C5)"])
for row in ws.iter_rows(min_row=2, min_col=2, max_col=3):
    row[0].number_format = "[h]:mm"
    row[1].number_format = "0.00"
wb.save("duration_totals.xlsx")
print(sum(tickets["Open for"], timedelta()), "=", tickets["Hours"].sum(), "hours")

openpyxl writes a Python timedelta as a fraction of a day, like pandas, and the format makes it display as elapsed time. Opened in Excel, the total shows 102:40 and 102.67 hours. Formatted h:mm instead, the same total would show 6:40 — the classic "my weekly hours don't add up" report.

When the sheet keeps the start and end times, the duration can also be a live formula: =C2-B2 subtracts two datetimes and gives a fraction of a day, so the same [h]:mm format applies. That keeps the duration correct if someone corrects a timestamp by hand, at the cost of the cached-value issue every Python-written formula has — programs reading the file see no result until a spreadsheet application has recalculated it.

Step 5 — Read durations back correctly

How openpyxl and pandas read a duration depends on its format. With a bracketed format it comes back as a timedelta; with a time-of-day format such as h:mm it comes back as a datetime in 1900, and with a general format as a plain number of days:

from datetime import timedelta
wb = Workbook(); ws = wb.active
for fmt in ["[h]:mm", "h:mm", "General"]:
    ws.append([fmt, timedelta(hours=26, minutes=30)])
    ws.cell(ws.max_row, 2).number_format = fmt
wb.save("read_back.xlsx")
for fmt, value in load_workbook("read_back.xlsx").active.iter_rows(values_only=True):
    print(f"{fmt:8} {value!r}")

The h:mm cell reads back as 1 January 1900, 02:30 — the duration has been interpreted as a date and lost its day. When reading a sheet somebody else formatted, convert numbers of days with pd.to_timedelta(values, unit="D"), and for datetimes near 1900, subtract datetime(1899, 12, 31) — the day before Excel's day 1 — to recover the duration, checking a known value first.

Negative durations

A negative duration — a job that finished before its target, a variance against planned hours — cannot be displayed as time in Excel's default 1900 date system: the cell fills with #####. Display such values as decimal hours, which can be negative, or as text built in Python, such as -2:15. LibreOffice happily shows negative times, so a file that looks fine there can still show ##### in Excel; test in Excel when negatives are possible.

Common pitfalls

• Leaving pandas' 0 format. Timedeltas show as whole days; set [h]:mm on each cell.
• Relying on set_column. pandas' own cell formats override column formats; format the cells.
• h:mm for durations. It wraps at 24 hours; always bracket the hours.
• Forgetting the total row. SUM results need the bracketed format too.
• Multiplying durations by rates. Convert to decimal hours first (×24).
• Negative times. They show as ##### in Excel; use decimal hours.
• Reading h:mm cells back. They arrive as 1900 datetimes; convert them explicitly.

Performance notes

Formats are applied per column with xlsxwriter or per cell with openpyxl; either is fast. Computing decimal hours with dt.total_seconds() is vectorised, so there is no reason to loop over rows.

Conclusion

Excel stores durations as fractions of a day, and the format decides how they appear. After writing a pandas timedelta column, replace its 0 format with [h]:mm or [h]:mm:ss, use [mm] for total minutes, and give totals the same bracketed format. Show decimal hours — duration × 24 — when people will calculate with the figures or when values can be negative, and read cells back with their format in mind.

Frequently asked questions

How do I show more than 24 hours in an Excel cell? Use a number format with the hours in square brackets, such as h:mm or h:mm:ss. The brackets tell Excel to show total elapsed hours; without them, h:mm shows only the hours past the last whole day, so 26:30 displays as 2:30.

Why does my pandas timedelta column show 1 or 0 in Excel? pandas writes a timedelta as a number of days — 26.5 hours becomes 1.104 — and gives it the format "0", which rounds to whole days. Set the column's number format to h:mm after writing.

How do I show a duration as decimal hours in Excel? Multiply the duration by 24, because Excel stores durations as fractions of a day. Write the decimal hours from pandas with total_seconds() / 3600, or use a formula such as =B2*24 with a number format like 0.00.

Why does a negative duration show as Excel's default 1900 date system cannot display negative dates or times, so a negative duration formatted as time appears as #####. Show negative differences as decimal hours, or as text built in Python.

Related

• Up one level: Applying Number and Date Formats in Excel — number formats in general.
• Format Dates in Excel Cells with Python — dates rather than durations.
• Consolidate Timesheets from Many Workbooks — hours worked in bulk.
• Handle the 1904 Date System in Excel Files — where negative times are allowed.