Round Timestamps into Time Slots with pandas

Staffing questions are slot questions: how many calls arrive between 09:00 and 09:15, which hour of the week is busiest, how many orders land in each shift. The raw data has exact timestamps — 09:07:42, 09:14:59, 09:15:00 — and the report needs them grouped into slots of equal length. pandas rounds timestamps to any frequency with dt.floor, dt.ceil and dt.round, and the rest is a group-by. Two details make the difference between a correct staffing table and a misleading one: slots with no events must appear as zeros, and slot boundaries must be handled consistently. This page covers both, plus heatmap tables and custom slots such as shifts. It belongs to Working with Dates and Times in Excel Data.

Prerequisites

pip install pandas openpyxl

calls.xlsx has a few hundred support calls received between 08:00 and 18:00 over five weekdays, each with a duration in seconds. Three hand-placed calls sit on and around the 09:15 boundary.

Step 1 — Floor, ceil and round

import pandas as pd

calls = pd.read_excel("calls.xlsx")
sample = calls[calls["Received"].dt.strftime("%Y-%m-%d %H:%M").between("2026-09-28 09:07", "2026-09-28 09:15")].copy()
sample["floor 15min"] = sample["Received"].dt.floor("15min")
sample["ceil 15min"] = sample["Received"].dt.ceil("15min")
sample["round 15min"] = sample["Received"].dt.round("15min")
print(sample[["Received", "floor 15min", "ceil 15min", "round 15min"]].to_string(index=False))

For time slots, floor is almost always the right choice: it labels each event with the start of the slot it falls in, so 09:07:42 and 09:14:59 belong to the 09:00 slot and 09:15:00 starts the next one. ceil labels with the slot's end — useful when a report says "calls up to 09:15" — and round sends each event to the nearest boundary, which splits a slot's events between two labels and is rarely what a staffing report means. The frequency string can be any multiple: "5min", "30min", "h", "4h".

Step 2 — Count and sum per slot

calls["Slot"] = calls["Received"].dt.floor("15min")
per_slot = calls.groupby("Slot").agg(Calls=("Received", "count"), Minutes=("DurationSec", lambda s: s.sum() / 60))
print(per_slot.head(8).round(1))
print("slots with at least one call:", len(per_slot))

The group-by gives call counts and handled minutes per 15-minute slot. But it only has rows for slots in which something happened: a quiet 15 minutes simply does not appear. A chart of this table draws a line straight over the gap, and an average per slot ignores the quiet ones and overstates the load. The fix is to build the full grid of slots first.

Step 3 — Include empty slots

days = calls["Received"].dt.normalize().unique()
grid = pd.DatetimeIndex([])
for day in days:
    grid = grid.append(pd.date_range(day + pd.Timedelta(hours=8), day + pd.Timedelta(hours=17, minutes=45), freq="15min"))
full = per_slot.reindex(grid, fill_value=0)
full.index.name = "Slot"
print(len(full), "slots in opening hours;", int((full["Calls"] == 0).sum()), "had no calls")
print("average calls per slot:", round(per_slot["Calls"].mean(), 2), "(busy slots only) vs",
      round(full["Calls"].mean(), 2), "(all opening-hour slots)")

The grid has every 15-minute slot from 08:00 to 17:45 on each day — 40 per day — and reindex(..., fill_value=0) adds the missing ones with zero calls. Building the grid per day keeps it to opening hours; a single date_range across the week would include the nights. The two averages show why this matters for staffing: the busy-slots-only figure is higher, because it never counts a quiet quarter-hour. Filling gaps in daily series follows the same pattern, as in Fill Missing Dates in a Time Series from Excel with pandas.

Step 4 — A weekday-by-hour heatmap table

The classic staffing view is a grid of weekdays against hours. Floor to the hour, then pivot:

calls["Weekday"] = calls["Received"].dt.day_name().str[:3]
calls["Hour"] = calls["Received"].dt.hour
heat = calls.pivot_table(index="Hour", columns="Weekday", values="Received", aggfunc="count", fill_value=0)
heat = heat.reindex(index=range(8, 18), columns=["Mon", "Tue", "Wed", "Thu", "Fri"], fill_value=0)
print(heat)
with pd.ExcelWriter("call_heatmap.xlsx", engine="xlsxwriter") as xw:
    heat.to_excel(xw, sheet_name="Heatmap")
    xw.sheets["Heatmap"].conditional_format(1, 1, len(heat), len(heat.columns),
                                            {"type": "3_color_scale"})

reindex on both axes guarantees all opening hours and all five weekdays appear in a fixed order, even if one had no calls.

Step 5 — Custom slots: shifts and uneven buckets

Not every slot has a fixed length. Shifts — early 08:00–11:00, midday 11:00–14:00, late 14:00–18:00 — are uneven, and pd.cut on the time of day assigns them:

minutes = calls["Received"].dt.hour * 60 + calls["Received"].dt.minute
calls["Shift"] = pd.cut(minutes, bins=[8 * 60, 11 * 60, 14 * 60, 18 * 60],
                        labels=["Early", "Midday", "Late"], right=False)
print(calls.groupby("Shift", observed=False).agg(Calls=("Received", "count"),
                                                  AvgMinutes=("DurationSec", lambda s: s.mean() / 60)).round(1))

Converting the time to minutes since midnight makes the bin edges readable, and right=False makes each bin include its start and exclude its end — the same convention as floor, so a call at exactly 11:00 belongs to Midday. observed=False keeps a shift in the output even if it had no calls. Binning numbers in general is covered in Bin Values into Ranges with pandas cut.

Step 6 — Excel's FLOOR and its boundary problem

In a spreadsheet, =FLOOR(A2, TIME(0,15,0)) floors a date-time to 15 minutes. Spreadsheets store times as fractions of a day in binary floating point, and a quarter of an hour (1/96 of a day) is not exactly representable, so a value on a boundary can come out a hair below it and fall into the previous slot — unless the implementation compensates. Compare with pandas on the boundary calls:

import subprocess
from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.active
boundary = pd.to_datetime(["2026-09-28 09:14:59", "2026-09-28 09:15:00", "2026-09-28 14:45:00", "2026-09-28 17:30:00"])
for i, t in enumerate(boundary, start=1):
    ws[f"A{i}"] = t.to_pydatetime()
    ws[f"B{i}"] = f"=FLOOR(A{i},TIME(0,15,0))"
    ws[f"C{i}"] = f"=FLOOR(A{i}+TIME(0,0,0.5),TIME(0,15,0))"
    for col in "ABC":
        ws[f"{col}{i}"].number_format = "hh:mm:ss"
wb.save("floor_check.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "recalc", "floor_check.xlsx"],
               check=True, capture_output=True)
res = load_workbook("recalc/floor_check.xlsx", data_only=True).active
for i, t in enumerate(boundary, start=1):
    print(t.time(), "| sheet FLOOR:", res[f"B{i}"].value, "| with ½-second nudge:", res[f"C{i}"].value,
          "| pandas:", t.floor("15min").time())

On these four boundary values LibreOffice's FLOOR agrees with pandas — its implementation rounds away tiny floating-point errors before flooring. Do not assume every spreadsheet does: run the same check in the Excel version your readers use, on real boundary values from your data, and if any row of the plain FLOOR column disagrees with pandas, add a fraction of a second before flooring, as the third column does. pandas stores timestamps as integers, so its floor has no floating-point edge case at all — one more reason to do the bucketing in Python and write the slot labels as values.

Performance notes

dt.floor, ceil and round are vectorised integer operations and run over millions of timestamps in milliseconds; the group-by and pivot are equally quick. Building the slot grid with a Python loop over days is fine for weeks or months of data; for years, build it in one go with pd.date_range over the whole period at slot frequency and filter to opening hours with between_time("08:00", "17:45"). The lambda used to sum minutes runs once per slot and is the slowest piece — summing seconds with "sum" and dividing afterwards is faster on large data.

Common pitfalls

• Using round for slots. It splits each slot's events between two labels; use floor.
• Missing empty slots. Group-by results skip quiet slots; reindex to a full grid before charting or averaging.
• A grid that spans the night. Build the grid per day within opening hours.
• Inconsistent bin edges. Use right=False in pd.cut so edges behave like floor.
• Time zones. Bucket in the zone where the work happens; a UTC 08:00 slot is 09:00 in London in summer.

Conclusion

Label each timestamp with the start of its slot using dt.floor("15min") (or "h", "30min", …), group and aggregate, and reindex to a full grid of opening-hour slots so quiet periods count as zeros. Pivot weekday against hour for a heatmap, use pd.cut with right=False for uneven slots such as shifts, and prefer doing the bucketing in pandas over Excel's FLOOR, whose floating-point times can misplace events that fall exactly on a boundary.

Frequently asked questions

How do I round timestamps down to 15 minutes in pandas? Use df"Received".dt.floor("15min"). Each timestamp is replaced by the start of its 15-minute slot, so 09:14:59 becomes 09:00 and 09:15:00 stays 09:15.

How do I count events per time slot including empty slots? Group by the floored timestamp and count, then reindex the result to a complete pd.date_range of slots with fill_value=0, so slots without events appear with a count of zero.

What is the difference between floor, ceil and round for timestamps? floor moves each timestamp to the start of its slot, ceil to the end, and round to the nearest boundary. For counting events per slot, floor is the usual choice.

Can a spreadsheet FLOOR put a time in the previous slot? Spreadsheets store times as binary fractions of a day, and slot lengths such as 15 minutes are not exactly representable, so a value exactly on a boundary can come out a tiny bit smaller than the boundary unless the implementation compensates. Test boundary values in your spreadsheet; adding a fraction of a second before flooring is a common safeguard. pandas uses integer timestamps and is not affected.

Related

• Up one level: Working with Dates and Times in Excel Data — dates and times in spreadsheet data.
• Calculate Working Hours Between Two Timestamps — durations inside opening hours.
• Group Excel Rows by Month and Quarter with pandas — the same idea at calendar scale.
• Build a Support Ticket SLA Report — slot counts in an operations report.