Dates, Time & Schedules
Generate a Dynamic Sequence of Time Slots Every N Minutes (SEQUENCE & TIME)
Spill an exact clinic, interview, or conference schedule of N appointment slots spaced every M minutes using SEQUENCE(Slots, 1, TIME(Start_Hr, Start_Min, 0), Interval_Min / 1440).
Build 15-, 20-, or 30-minute booking grids without floating-point drift!
Because spreadsheet clock times are fractions of a 24-hour day (1 hour = 1 / 24, 1 minute = 1 / 1,440), generating a booking grid of 8 appointment start times beginning at 09:00 spaced every 30 minutes is a single dynamic-array call: SEQUENCE(8, 1, TIME(9, 0, 0), 30 / 1440)! Change the interval cell from 30 to 15 or 45 minutes and every time slot recalculates instantaneously!
You can also compute how many slots fit between Opening Hour (e.g., 09:00) and Closing Hour (e.g., 13:00) via INT(((End_Time - Start_Time) * 1440) / Interval_Min), and format any slot's serial number as portable 24-hour "HH:MM" text via RIGHT("00" & HOUR(t), 2) & ":" & RIGHT("00" & MINUTE(t), 2)!
| Start Time & Slot Count | Interval Step (Min / 1440) | First Slot | Final Spilled Slot |
|---|---|---|---|
| 09:00 Start, 8 Slots | 30 / 1440 (Half-hour) | 09:00 | 12:30 (Slot #8) |
| 09:00 Start, 8 Slots | 15 / 1440 (Quarter-hour) | 09:00 | 10:45 (Slot #8) |
| 08:30 Start, 6 Slots | 45 / 1440 (45-min) | 08:30 | 12:15 (Slot #6) |
Final Appointment Start Time ("HH:MM") of the Spilled Time Sequence
Generates B4 slots starting at TIME(B2, B3, 0) stepped by B5 / 1440 days and formats the final slot as HH:MM.
=IF(OR(B2 < 0, B2 > 23, B3 < 0, B3 > 59, B4 < 1, B5 <= 0), "Invalid Schedule Inputs", RIGHT("00" & HOUR(MAX(SEQUENCE(B4, 1, TIME(B2, B3, 0), B5 / 1440))), 2) & ":" & RIGHT("00" & MINUTE(MAX(SEQUENCE(B4, 1, TIME(B2, B3, 0), B5 / 1440))), 2))Total Schedule Span Covered by All Slots (Hours)
Multiplies slot count B4 by slot duration B5 (minutes) and divides by 60 minutes/hour.
=IF(OR(B4 < 1, B5 <= 0), "Invalid Schedule Inputs", ROUND((B4 * B5) / 60, 2))Try the example
Use the original values or change them to test your understanding.