Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Convert Excel Time Values and Durations in Python

Excel stores times and durations as fractions of a day, and pandas returns them as time objects, timedeltas or 1900 dates depending on the cell format. Turn them all into timedeltas, handle shifts that cross midnight, parse durations typed as text and write durations back so Excel shows hours, not "1".
Dates & timesNumber & date formatspandas

Timesheets, call logs and machine run times all end up in Excel as times and durations — and all of them are stored the same way underneath: as a fraction of a day. 09:00 is 0.375, a 90-minute call is 0.0625, and a 30-hour job is 1.25. What pandas gives you back depends on how each cell was formatted: a datetime.time, a Timedelta, or a date in January 1900. This guide, part of Working with Dates and Times in Excel Data, converts every variant into timedeltas you can add, average and compare, and writes them back so Excel shows hours rather than a bare number.

What pandas returns for the same kind of value A cell holding 0.375 formatted hh:mm is read as the time 09:00. A cell holding 1.25 formatted [h]:mm is read as a Timedelta of 1 day 6 hours. The same 1.25 formatted h:mm is read as the datetime 1900-01-01 06:00. Durations typed as text such as 1h 15m stay strings, and decimal hours stay floats. Cell format pandas value 0.375 hh:mm time 09:00 1.25 [h]:mm Timedelta 1 day 06:00 1.25 h:mm datetime 1900-01-01 06:00 "1h 15m" text str 2.5 General float 2.5

Prerequisites

Bash
pip install pandas openpyxl

A timesheet with every variant — times of day, a shift that crosses midnight, durations with and without square brackets, durations typed as text and decimal hours:

Python
import datetime as dt

import pandas as pd
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["task", "start", "end", "break", "run_time", "typed", "hours_dec"])
for row in [("A", 0.375, 0.5208333, 0.0208333, 1.25, "2:30", 2.5),
            ("B", 0.875, 0.0625, 0.0104167, 0.0625, "45 min", 0.75),
            ("C", 0.5, 0.75, 0.0208333, 2.5, "1h 15m", 1.25)]:
    ws.append(list(row))
for row in ws.iter_rows(min_row=2):
    row[1].number_format = row[2].number_format = "hh:mm"
    row[3].number_format = "h:mm"
    row[4].number_format = "[h]:mm"
wb.save("timesheet.xlsx")

df = pd.read_excel("timesheet.xlsx")
print(df.dtypes.to_dict())

start, end and break come back as object columns of datetime.time values, run_time — formatted with square brackets — as a proper timedelta64 column, typed as text and hours_dec as floats.

Step 1 — Turn times of day into timedeltas

datetime.time objects cannot be subtracted from each other. Convert them to timedeltas since midnight by counting their seconds:

Python
def time_to_td(series):
    seconds = series.map(lambda t: t.hour * 3600 + t.minute * 60 + t.second + t.microsecond / 1e6
                         if pd.notna(t) else None)
    return pd.to_timedelta(seconds, unit="s").round("s")

start = time_to_td(df["start"])
end = time_to_td(df["end"])
print(start.tolist()[:2])     # [Timedelta('0 days 09:00:00'), Timedelta('0 days 21:00:00')]

The function counts the seconds since midnight and rounds to the nearest whole second. That rounding matters: a time typed as 12:30 is stored as a binary fraction that openpyxl reads as 12:29:59.997, and truncating instead of rounding would lose a second on every such value. Drop the .round("s") only if your data genuinely records fractions of a second.

Step 2 — Shifts that cross midnight

Task B starts at 21:00 and ends at 01:30. Subtracting gives a negative duration. Add a day wherever the end is earlier than the start:

Python
worked = end - start
worked = worked.where(worked >= pd.Timedelta(0), worked + pd.Timedelta(days=1))
breaks = time_to_td(df["break"])
net = worked - breaks
print((net / pd.Timedelta(hours=1)).round(2).tolist())      # [3.0, 4.25, 5.5]

Dividing a timedelta by pd.Timedelta(hours=1) gives decimal hours, the form payroll and billing systems usually want. The rule "negative means next day" assumes no shift is 24 hours or longer; for those, store full start and end datetimes rather than times of day.

Once everything is a timedelta, totals and averages are ordinary pandas operations, and they stay exact — no fractions of a day to round:

Python
print(net.sum(), "|", net.mean())       # 0 days 12:45:00 | 0 days 04:15:00
print(net.sum() > pd.Timedelta(hours=12))                 # True

Comparisons with pd.Timedelta(hours=12) read like the business rule they implement — "more than twelve hours this week" — which is harder to see in a formula comparing a cell with 0.5.

A shift from 21:00 to 01:30 The shift starts at 21:00 and ends at 01:30 the next morning. Subtracting the times gives minus 19.5 hours, and adding one day gives the correct 4.5 hours worked before the 15-minute break is deducted. 21:00 start 00:00 midnight 01:30 end 4:30 −19:30 + 24:00

Step 3 — Durations formatted without square brackets

The break column holds breaks of 30 and 15 minutes formatted as h:mm, so they come back as times of day — 00:30 and 00:15 — which Step 1 already handled. The real trap is a duration over 24 hours with that format. openpyxl then returns a date in January 1900:

Python
from openpyxl import load_workbook

wb = Workbook()
ws = wb.active
ws["A1"] = 1.25                 # 30 hours
ws["A1"].number_format = "h:mm"
wb.save("wrapped.xlsx")
value = load_workbook("wrapped.xlsx").active["A1"].value
print(value)                    # 1900-01-01 06:00:00
print(value - dt.datetime(1899, 12, 31))    # 1 day, 6:00:00

Excel shows "6:00" for that cell too — the h:mm format wraps at 24 hours — but the stored value is 30 hours. Subtracting 31 December 1899, the day before serial number 1, recovers it. Check every duration column for values that came back as datetime, because a job that ran for 30 hours can otherwise be reported as six. A quick df[col].map(type).value_counts() shows at a glance whether a column mixes times, timedeltas and datetimes. Excel's serial numbers in general are covered in Fix Excel Serial Numbers Showing Instead of Dates.

Step 4 — Durations typed as text and decimal hours

pd.to_timedelta parses many text forms directly — "1h 15m", "45 min", "90 minutes" — but not "2:30", which it expects as "2:30:00". Pad colon forms with seconds, then parse:

Python
typed = df["typed"].str.strip().str.replace(r"^(\d+):(\d{2})$", r"\1:\2:00", regex=True)
parsed = pd.to_timedelta(typed, errors="coerce")
print(parsed.tolist())
# [Timedelta('0 days 02:30:00'), Timedelta('0 days 00:45:00'), Timedelta('0 days 01:15:00')]
print(df.loc[parsed.isna(), "typed"].tolist())     # [] - nothing failed

hours = pd.to_timedelta(df["hours_dec"], unit="h")
print(hours.tolist()[0])                            # 0 days 02:30:00

errors="coerce" turns anything unparseable into NaT; list those values rather than letting them drop out of a total. Decimal hours convert with unit="h". Decide once which unit a numeric column is in: 2.5 could be hours, days or minutes, and only the sheet's owner knows.

Step 5 — Write durations back so Excel shows hours

pandas writes timedeltas to Excel as a number of days with a plain number format, so 1.25 days appears as "1" in the cell. Set a duration format on those cells after writing:

Python
out = pd.DataFrame({"task": df["task"], "net": net, "run_time": df["run_time"]})
out.to_excel("hours.xlsx", index=False)

wb = load_workbook("hours.xlsx")
ws = wb.active
for row in ws.iter_rows(min_row=2, min_col=2, max_col=3):
    for cell in row:
        cell.number_format = "[h]:mm"
wb.save("hours.xlsx")
cell = load_workbook("hours.xlsx").active["C2"]
print(cell.value, cell.number_format)    # 1 day, 6:00:00 [h]:mm

The square brackets make Excel show 30:00 instead of wrapping to 6:00, and the stored value — a fraction of a day — lets Excel users sum and average the column with ordinary formulas. If the report is for a payroll system rather than people, write decimal hours instead; see Format Numbers in Thousands and Millions in Excel for number formats in general.

A timesheet from cells to totals and back Times of day become timedeltas since midnight, overnight shifts get a day added, wrapped durations read as 1900 dates are recovered, text durations are parsed, everything is summed as timedeltas and written back with an [h]:mm format. Times → timedelta since midnight Fix overnight +1 day if negative Recover wrapped − 1899-12-31 Parse text 1h 15m, 2:30 Write [h]:mm number_format

Common pitfalls

  • Summing time objects. datetime.time values cannot be added; pandas raises or silently returns an object column. Convert to timedeltas first.
  • Formats that wrap at 24 hours. h:mm shows 30 hours as 6:00 in Excel and openpyxl reads it as a 1900 date. Use [h]:mm for durations everywhere.
  • Floating-point seconds. 0.5208333 days is 12:29:59.997; round to whole seconds or minutes before comparing durations.
  • The 1904 date system. Workbooks created on older Macs count days from 1904, so the same serial number means a different date. Durations are unaffected, but a wrapped duration recovered with the 1899-12-31 trick needs the 1904 epoch instead; openpyxl exposes it as wb.epoch.
  • Durations stored as text by an export. Some systems write "02:30:00" as a string. pd.to_timedelta parses it directly, so check the column type before assuming a conversion is needed.
  • Mixing time zones. Times of day have no zone; if shifts span a daylight-saving change, store full timestamps — see Handle Timezones in Excel Timestamps with Python.

Conclusion

Excel times and durations are fractions of a day, and their Python type depends on the cell format. Convert times of day to timedeltas since midnight, add a day to negative shift lengths, recover wrapped durations that arrive as 1900 dates, parse text durations after padding colon forms, and convert decimal hours with unit="h". When writing back, give duration cells an [h]:mm format so Excel shows hours, not a bare number of days.

Frequently asked questions

Why does pandas read an Excel duration as a time instead of a timedelta? The type depends on the cell's number format. A cell formatted h:mm or hh:mm is read as a time of day, while h:mm (with square brackets) is read as a timedelta. Convert time objects to timedeltas by counting the seconds since midnight.

Why does a 30-hour duration come back as 1900-01-01 06:00? Excel stores 30 hours as 1.25 days. With a plain h:mm format, openpyxl treats the value as a date and time — day 1 of the 1900 date system at 06:00. Subtract datetime(1899, 12, 31) to recover the duration.

How do I calculate hours for a shift that ends after midnight? Subtract start from end; if the result is negative, add one day. Then divide the timedelta by pd.Timedelta(hours=1) to get decimal hours.

Why do my durations show as 1 or 0 in Excel after writing them with pandas? pandas writes timedeltas as a number of days with a plain number format. Set the cells' number format to h:mm:ss afterwards so Excel displays them as hours and minutes.

More on dates & times

More on dates & times →