Leave & Rota Planning: PTO Accruals, Holiday Liability & Shift Coverage

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.

• Work out each employee's completed years of service, look up the matching entitlement tier, count the days they are employed in 2026 and pro-rate the entitlement for joiners and leavers, rounded to half days.
• Calculate how much leave each employee has earned by 30 September, what they can still take now, how many unused days move into 2027 under the cap and how many are forfeited.
• Count how many working days each leave request really costs, given each person's own rest days and the public holidays, find the date they are back at work and flag requests that exceed the balance.
• Value every employee's unused leave at their daily rate, roll the liability up by cost centre with SUMIFS and add employer on-costs for the total the finance team accrues at year end.
• Count each employee's absence spells and days in a rolling 12-month window, turn them into a Bradford score and let a formula suggest the next step: OK, a conversation or a formal review.
• Check a weekly rota against the minimum staffing per shift, total each person's scheduled hours from the shift lengths and highlight every shift that is short.
• Turn a night auditor's start and end times into hours worked, apply night and weekend premiums with HOUR and WEEKDAY, price each shift and find the overtime above the weekly contract.
• Turn the front desk's December bookings into a people × days calendar, count how many people are off each day, flag the days above the limit and let a formula name the busiest day.