Date & Time
Group times into 15-minute, 30-minute, or multi-hour buckets
Round call and event timestamps down to the start of any N-minute interval using FLOOR(TimeVal, Minutes / 1440).
Snap fractional times to BucketMinutes / 1440 multiples
Because 1 day contains 1,440 minutes, a 30-minute slot equals 30 / 1440 of a day and a 3-hour block equals 180 / 1440 (3 / 24).
FLOOR(CallTime, BucketMins / 1440) rounds 14:47 down to the start of its 30-minute bucket (14:30) and rounds 14:22 down to the start of its 3-hour block (12:00)—leaving exact boundary times like 14:30 unchanged.
| CallTime (A) | BucketMins (B) | Bucket Start (FLOOR) | Bucket End (Start + Interval) |
|---|---|---|---|
| 14:47 | 30 | 14:30 | 15:00 |
| 14:22 | 180 | 12:00 | 15:00 |
| 14:30 | 30 | 14:30 | 15:00 |
30-minute and 3-hour time-bucket formulas
Grouping raw logs by this bucket column lets COUNTIFS and pivot summaries chart arrival volume per half-hour or shift block.
=LET(b, FLOOR(A2, B2 / 1440), HOUR(b) & ":" & RIGHT("00" & MINUTE(b), 2))3-hour shift bucket check
With BucketMins = 180, 14:22 maps into the 12:00–15:00 afternoon window.
=LET(b, FLOOR(A3, B3 / 1440), HOUR(b) & ":" & RIGHT("00" & MINUTE(b), 2))Inputs and interpretation
Use a positive bucket length. A bucket includes its start and excludes its end; compute the end as FLOOR(time, interval)+interval, because CEILING leaves exact boundaries unchanged. The displayed HOUR:MINUTE label is text and drops the date. Preserve the numeric timestamp bucket when grouping multiple dates, and account for midnight rollover. These 30/180-minute examples divide a day evenly.
Try the example
Use the original values or change them to test your understanding.