Flexitime & Time Banks: Daily Balances, Core Hours, Carry-Over Caps, Time Off in Lieu & Annualised Hours Contracts

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Turn start and end times into worked hours, handle a shift that runs past midnight with MOD, compute the plus/minus against the daily target, add up the week in one formula and show balances as ±h:mm.
• Flag starts after the core window opens and finishes before it closes, count late starts per team with COUNTIFS and count every record that breaks the core window at least once, without counting a double breach twice.
• Build the uncapped running bank with an anchored SUM, apply the upper and lower caps with MIN and MAX, measure the hours lost above the cap each week and total them for the quarter in one SUMPRODUCT.
• Cap each month-end balance to the fictional carry-over limits, work out how many hours are paid out above the limit, value the payout at the hourly rate and a payout factor, and count the balances that sit below the lower limit.
• Convert time-bank balances into whole half days of TOIL, find the last day of each TOIL block with WORKDAY and a closure-day list, compute the balance left after the request and check whether each request is covered.
• Give each day of the log its ISO week, add up the actual hours per week with a date window in SUMIFS, compare the year-to-date hours with the plan and work out the average weekly hours still needed to reach the annual contract.
• Scale the full-time week by each FTE share, spread it over the person’s working days, count the calendar days each person is employed in 2026 and pro-rate the annual target for part-time staff and mid-year joiners.
• Average the balances per team, flag every balance outside the review thresholds, count the balances to review per location and chart how the company’s total bank has moved over the year.