Date & Time
Generate & Snap Time Slots at Specific Minute Intervals (15, 20, or 30 Min)
Compute total appointment slots in a shift, map any integer slot index to exact HH:MM clock time, and snap arbitrary arrival timestamps upward to the next scheduled interval.
Exact integer-minute arithmetic avoids binary floating-point time drift
Spreadsheet clock times are fractions of a 24-hour day (1,440 minutes = 1.0). Repeatedly adding TIME(0, 15, 0) down a column accumulates IEEE-754 binary rounding noise that can cause exact XLOOKUP or >= comparisons to miss by 10^-16 days.
Computing total minutes from midnight via ROUND(time_val * 1440, 0), performing integer division or ceiling arithmetic on whole minutes, and dividing back by 1440 (or formatting via TIME(INT(mins / 60), MOD(mins, 60), 0)) guarantees bit-exact slot boundaries for 15-, 20-, or 30-minute dispatch grids.
| Scheduling Task | Integer-Minute Formula | Example (08:30–12:00, 15-min step) |
|---|---|---|
| Total Available Slots | INT((ROUND(End*1440,0) - ROUND(Start*1440,0)) / Step) | 14 slots |
| k-th Slot Start Time (0-based) | (ROUND(Start*1440,0) + k * Step) / 1440 | k = 3 -> 09:15 |
| Snap Arrival to Next Slot | (-INT(-ROUND(Arr*1440,6) / Step) * Step) / 1440 | 09:04 -> 09:15 |
Shift Slot Capacity & k-th Slot Clock Time
Convert start and end times to whole minutes since midnight and zero-pad INT(mins / 60) and MOD(mins, 60) with RIGHT("0" & ..., 2) so slot timestamps always render in canonical 24-hour HH:MM format.
=LET(mins, ROUND($B$2 * 1440, 0) + D2 * $B$4, RIGHT("0" & INT(mins / 60), 2) & ":" & RIGHT("0" & MOD(mins, 60), 2))Snap Arbitrary Timestamp Upward to Next Interval Slot
Divide arrival minutes by step_minutes inside -INT(-x) so exact on-time arrivals stay on their slot while 1-minute-late arrivals advance to the next slot.
=LET(mins, -INT(-ROUND(G2 * 1440, 6) / $B$4) * $B$4, RIGHT("0" & INT(mins / 60), 2) & ":" & RIGHT("0" & MOD(mins, 60), 2))Try the example
Use the original values or change them to test your understanding.