Date & Time Formulas
Formula to List Weekend Dates Only (Saturday & Sunday Shift Schedule)
Calculate the upcoming Saturday on or after any start date via B2 + MOD(6 - WEEKDAY(B2, 2), 7) and step strictly across consecutive Saturday/Sunday pairs using 7 * INT((N - 1) / 2) + MOD(N - 1, 2).
Generate pure Saturday–Sunday on-call rosters without filtering 365 calendar rows!
Hospital weekend on-call rosters, stadium event calendars, and maintenance outage trackers need ONLY Saturdays and Sundays—skipping Monday through Friday completely! Notice the periodic rhythm of weekend dates starting from an anchor Saturday S_0: slot 1 is S_0 + 0 (Sat), slot 2 is S_0 + 1 (Sun), slot 3 jumps +6 days to S_0 + 7 (next Sat), slot 4 is S_0 + 8 (next Sun), and in general the N-th weekend day (N = 1, 2, 3, ...) is S_0 + 7 * INT((N - 1) / 2) + MOD(N - 1, 2)!
Even better, you can feed ANY arbitrary project start date B2 (even a Tuesday or Sunday!) because floored modulo MOD(6 - WEEKDAY(B2, 2), 7) steps forward 0 to 6 days to land squarely on the first Saturday (or if B2 is already Sunday and you want the next full weekend’s Saturday, +6 days)! Starting from Wednesday 2026-06-03: slot N = 1 lands on Saturday 2026-06-06, slot N = 2 on Sunday 2026-06-07, and slot N = 3 jumps cleanly to Saturday 2026-06-13!
| Weekend Slot Index N (Start = Wed 2026-06-03) | Offset 7*INT((N-1)/2) + MOD(N-1,2) | Generated ISO Date | Day of Week |
|---|---|---|---|
| Slot N = 1 (First Saturday) | +0 Days From Anchor Sat | "2026-06-06" | Saturday |
| Slot N = 2 (First Sunday) | +1 Day From Anchor Sat | "2026-06-07" | Sunday |
| Slot N = 3 (Second Saturday) | +7 Days From Anchor Sat | "2026-06-13" | Saturday |
N-th Upcoming Weekend Date (ISO "yyyy-mm-dd")
Finds anchor Saturday B2 + MOD(6 - WEEKDAY(B2, 2), 7) and advances to the N-th weekend shift day (B3 >= 1).
=IF(OR(NOT(ISNUMBER(B2)), B3 < 1), "Invalid Start or Slot N", TEXT((B2 + MOD(6 - WEEKDAY(B2, 2), 7)) + 7 * INT((B3 - 1) / 2) + MOD(B3 - 1, 2), "yyyy-mm-dd"))Identify Shift Day Label ("Saturday Shift" vs. "Sunday Shift")
Uses WEEKDAY(..., 2) on the generated weekend date (6 = Saturday, 7 = Sunday).
=IF(OR(NOT(ISNUMBER(B2)), B3 < 1), "Invalid Start or Slot N", IF(WEEKDAY((B2 + MOD(6 - WEEKDAY(B2, 2), 7)) + 7 * INT((B3 - 1) / 2) + MOD(B3 - 1, 2), 2) = 6, "Saturday Shift", "Sunday Shift"))Try the example
Use the original values or change them to test your understanding.