Date & Time
Hotel and rental stay cost: split Sun–Thu weekday vs Fri–Sat weekend nights
Count exact off-peak (Sun–Thu) and peak (Fri–Sat) stay nights between Check-In and Check-Out dates using a +1 shifted NETWORKDAYS trick.
Why shifting NETWORKDAYS(CheckIn + 1, CheckOut) isolates Fri–Sat nights
This example property charges peak rates on Friday and Saturday nights. Standard NETWORKDAYS instead excludes Saturday and Sunday.
Adding +1 day to every stay night (from CheckIn through CheckOut - 1) maps Friday night onto Saturday and Saturday night onto Sunday—while mapping Sunday–Thursday nights onto Monday–Friday! Thus NETWORKDAYS(CheckIn + 1, CheckOut) counts exact Sun–Thu nights, and TotalNights - OffPeakNights counts exact Fri–Sat peak nights.
| CheckIn (A) | CheckOut (B) | Total Nights | Sun–Thu ($150) | Fri–Sat ($240) | Stay Total |
|---|---|---|---|---|---|
| 2026-05-07 (Thu) | 2026-05-11 (Mon) | 4 (Thu,Fri,Sat,Sun) | 2 (Thu, Sun) | 2 (Fri, Sat) | $780 |
| 2026-05-08 (Fri) | 2026-05-10 (Sun) | 2 (Fri, Sat) | 0 | 2 (Fri, Sat) | $480 |
Total stay cost and peak/off-peak night split formulas
Bind tot = B2 - A2 and wk = NETWORKDAYS(A2 + 1, B2) inside LET to multiply both nightly rates cleanly.
=IF(B2<A2, "Invalid dates", IF(B2=A2, 0, LET(tot, B2 - A2, sunThu, NETWORKDAYS(A2 + 1, B2), sunThu * C2 + (tot - sunThu) * D2)))Count peak Friday + Saturday nights only
Subtract Sun–Thu nights from total elapsed nights (CheckOut - CheckIn).
=IF(B2<A2, "Invalid dates", IF(B2=A2, 0, (B2 - A2) - NETWORKDAYS(A2 + 1, B2)))Inputs and interpretation
Use numeric date-only values with no time fraction, checkout on or after check-in, and nonnegative rates. Same-day stays cost zero under this nightly model; day-use fees and taxes are separate policies. Do not add ordinary holidays to the shifted NETWORKDAYS calculation without mapping their dates too.
Try the example
Use the original values or change them to test your understanding.