Business-Day SLAs, Holiday Calendars & Receivables Aging

Who this is for

Stage 5: Calendar Cutoffs, Tenures & Business-Day SLAs — Designed for learners mastering weekday, workday & public-holiday calendars, networkdays turnaround clocks & sla breach gates, ar days past due, ifs aging buckets & sumifs summaries, weighted reserves, milestone cascades & capacity hours through interactive spreadsheet examples.

What you will practise

• Extract ISO weekday numbers (1–7) in B2:B5 using WEEKDAY(A2, 2), then flag "Weekend" vs "Workday" in C2:C5 using IF.
• Compute Promised Delivery Date in D2:D5 using WORKDAY(OrderDate, LeadWorkdays), then compute total Calendar Span in E2:E5 using DAYS(D2, B2).
• Compute Holiday-Aware Settlement Date in D2:D5 using WORKDAY with locked $G$2:$G$3, then compute extra calendar days added by holidays in E2:E5 (=D2 - WORKDAY(B2, C2)).
• Compute Inclusive Workdays in D2:D5 using NETWORKDAYS(B2, C2, $G$2:$G$3), then compute Net Turnaround Workdays in E2:E5 as =D2 - 1.
• Compute Actual Net Workdays in E2:E5 via NETWORKDAYS(B2, C2, $H$2:$H$3) - 1, then flag "Breach" vs "On Time" in F2:F5 via IF(E2 > D2, ...).
• Compute Days Until Friday in B2:B5 using MOD(5 - WEEKDAY(A2, 2) + 7, 7), then compute Settlement Friday Date in C2:C5 as =A2 + B2.
• Compute Weekly Cohort Monday in C2:C5 via =A2 - WEEKDAY(A2, 2) + 1, then sum revenue for the 2026-05-04 weekly cohort (E2) in F2 using SUMIF.
• Compute Days Past Due in D2:D5 using MAX(0, $F$2 - B2), then sum overdue invoice balances (Days Past Due > 0) in F4 using SUMIF.

Your learning path

1. Sum the last 30 days (or rolling N days) with SUMIFS

   Total revenue or orders inside a rolling 30-day window and avoid the classic RefDate - 30 vs RefDate - 29 off-by-one boundary mistake.

2. Sum timesheet hours by week and project (7-day window SUMIFS)

   Roll up daily employee time entries into weekly project totals using half-open [WeekStart, WeekStart + 7) date criteria.

3. Sum by month and year with SUMIFS and EOMONTH

   Aggregate daily transaction logs into monthly totals dynamically from a Year and Month cell without helper columns.

4. Sum If Begins With Prefix or Ends With Suffix (Wildcard SUMIFS Formula)

   Total general-ledger accounts or SKU codes starting with a prefix (Prefix & "*") or ending with a regional/currency suffix ("*" & Suffix)—or matching BOTH simultaneously—using wildcard SUMIFS.

5. Sum If Blank ("") vs. Not Blank ("<>") With SUMIF & SUMIFS

   Total open accounts-receivable invoices where Paid_Date is blank using SUMIF(D2:D6, "", C2:C6), settled invoices where Paid_Date is non-blank using "<>", and multi-criteria open region balances with SUMIFS.

6. Sum If Not Equal To ("<>") Single or Multiple Exclusions (SUMIFS)

   Total ledger amounts while excluding cancelled, refunded, or intercompany categories using SUMIF(Cat, "<>" & Excl, Amt) and multi-exclusion SUMIFS(Amt, Cat, "<>" & Excl1, Cat, "<>" & Excl2).

7. SUMIFS between two dates: total amounts inside a reporting window

   Add invoice payments on or after a start date and on or before an end date by joining comparison operators to boundary cells with &. Verify both endpoints and channel filters.

8. Count & Sum Odd vs Even Numbers in a Range (MOD, ISODD & SUMIF)

   Test integer parity with MOD(n, 2) or ISODD/ISEVEN and count or sum odd-numbered vs even-numbered street addresses, lot IDs, and quantities cleanly across any spreadsheet engine.