Pro workbook: Log, Balances, Calendar, Setup, Formulas. Sample data, real formulas.
| Leave log | ||||||
| One row per request. Working days are counted for you: weekends out, holidays from Setup out, only Approved counts toward balances. | ||||||
| Year | 2026 | |||||
| Person | Type | From | To | Days | Status | Note |
| Mara | Annual | 2026-02-16 | 2026-02-20 | 5 | Approved | Ski week |
| Teo | Sick | 2026-03-04 | 2026-03-05 | 2 | Approved | |
| Priya | Annual | 2026-04-02 | 2026-04-07 | 3 | Approved | Easter — Good Friday in range |
| Jonas | Annual | 2026-05-22 | 2026-05-29 | 5 | Approved | Bank holiday Monday in range |
| Mara | Annual | 2026-07-13 | 2026-07-24 | 10 | Approved | Summer |
| Nora | Parental | 2026-08-03 | 2026-08-28 | 20 | Approved | |
| Sam | Annual | 2026-08-31 | 2026-09-04 | 4 | Approved | Starts on a bank holiday |
| Teo | Annual | 2026-09-14 | 2026-09-18 | 5 | Approved | |
| Priya | Sick | 2026-09-09 | 2026-09-09 | 1 | Approved | |
| Jonas | Unpaid | 2026-09-21 | 2026-09-25 | 5 | Pending | Awaiting sign-off |
| Mara | Annual | 2026-09-28 | 2026-10-02 | 5 | Approved | |
| Nora | Annual | 2026-10-19 | 2026-10-23 | 5 | Declined | Team offsite that week |
| Sam | Annual | 2026-12-21 | 2026-12-31 | 7 | Approved | Christmas, two holidays in range |
| Balances | ||||||||
| Approved working days per person and type, against the annual allowance on Setup. | ||||||||
| Person | Allowance | Annual | Sick | Unpaid | Parental | Other | Left | Used |
| Mara | 25 | 20 | 5 | 80.0% | ||||
| Teo | 25 | 5 | 2 | 20 | 20.0% | |||
| Priya | 28 | 3 | 1 | 25 | 10.7% | |||
| Jonas | 22 | 5 | 17 | 22.7% | ||||
| Nora | 25 | 20 | 25 | 0.0% | ||||
| Sam | 20 | 11 | 9 | 55.0% | ||||
| 0.0% | ||||||||
| 0.0% | ||||||||
| 0.0% | ||||||||
| 0.0% | ||||||||
| 0.0% | ||||||||
| 0.0% | ||||||||
| Team | 145 | 44 | 3 | 20 | 101 | 30.3% | ||
| Pending requests | 1 | |||||||
| Pending days | 5 | |||||||
| Left = allowance − Annual. Sick, unpaid, parental and other are counted but don't draw from the allowance. Red when overdrawn, yellow at three days or fewer; sick days yellow at five or more. |
| Who's off | |||||||||||||||||||||||||||||||||
| One month: people down the side, days across. A letter per approved day — A annual, S sick, U unpaid, P parental, O other. Weekends and holidays shaded. | |||||||||||||||||||||||||||||||||
| Month — type the 1st | Days in month | ||||||||||||||||||||||||||||||||
| September 2026 | 30 | ||||||||||||||||||||||||||||||||
| Person | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | Days | ||
| Mara | A | A | A | 3 | |||||||||||||||||||||||||||||
| Teo | A | A | A | A | A | 5 | |||||||||||||||||||||||||||
| Priya | S | 1 | |||||||||||||||||||||||||||||||
| Jonas | |||||||||||||||||||||||||||||||||
| Nora | |||||||||||||||||||||||||||||||||
| Sam | A | A | A | A | 4 | ||||||||||||||||||||||||||||
| Off per day | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||||||||||||||||||
| Blue: approved leave (letter = type), red: sick. Weekends grey, holidays pink, days past the month's end darker. 'Off per day' turns yellow at two or more people away. |
| Setup | |||||||||
| The year, the team with annual allowances, the leave types, the statuses and the public holidays. | |||||||||
| Year | 2026 | ||||||||
| Person | Allowance (days) | Type | Letter | Status | Holiday | Name | |||
| Mara | 25 | Annual | A | Approved | 2026-01-01 | New Year's Day | |||
| Teo | 25 | Sick | S | Pending | 2026-04-03 | Good Friday | |||
| Priya | 28 | Unpaid | U | Declined | 2026-05-25 | Spring bank holiday | |||
| Jonas | 22 | Parental | P | 2026-08-31 | Summer bank holiday | ||||
| Nora | 25 | Other | O | 2026-12-25 | Christmas Day | ||||
| Sam | 20 | 2026-12-28 | Boxing Day (substitute) | ||||||
| Twelve people, five types, forty holidays. The letters are what the Calendar shows; keep them to one character. | |||||||||
| Sample holidays are UK-style — replace with yours. Holidays on weekends don't double-count: the day formula already skips weekends, so list weekday holidays only. | |||||||||
| How the formulas work | ||
| Every formula in this workbook, in plain words. Nothing is hidden and nothing needs a macro. Formulas are shown without the leading = sign. | ||
| Where | Formula | What it does |
| Log · Saturdays in range | IF(D6-C6<MOD(6-WEEKDAY(C6,2),7),0,INT((D6-C6-MOD(6-WEEKDAY(C6,2),7))/7)+1) | MOD(6−weekday,7) is the distance from the start date to the first Saturday; if the range is shorter than that there are none, otherwise one every seven days after it. The same with 7 counts Sundays. |
| Log · Days | IF(OR(C6="",D6="",D6<C6),0,D6-C6+1-Saturdays-Sundays-COUNTIFS(hol_dates,">="&C6,hol_dates,"<="&D6)) | Calendar days minus weekends minus holidays in the range. No NETWORKDAYS: it isn't in every app the same way, and this is visible arithmetic. |
| Balances · per type | SUMIFS(Log!$E$6:$E$205,Log!$A$6:$A$205,$A5,Log!$B$6:$B$205,"Annual",Log!$F$6:$F$205,"Approved",Log!$C$6:$C$205,">="&DATE(Setup!$B$3,1,1),Log!$C$6:$C$205,"<="&DATE(Setup!$B$3,12,31)) | Days for this person, this type, approved, starting in the year on Setup — so last year's rows can stay in the log. |
| Balances · Left | B5-C5 | Allowance minus approved annual leave. Other types are counted but don't draw down. |
| Calendar · a day cell | IF(COUNTIFS(names,$A6,types,"Annual",status,"Approved",from,"<="&date,to,">="&date)>0,"A", … "S", … "") | Is there an approved request of this type for this person whose range contains the day? Nested once per type, first match wins. The date is built from the month cell and the day number in row 5. |
| Calendar · Days (row) | SUMPRODUCT(LEN(B6:AF6)) | Every letter is one character and every empty result is zero characters, so the sum of lengths is the count of days. COUNTA would count the formulas themselves; a "<>" test on formula blanks behaves differently across apps. |
| Calendar · Off per day | SUMPRODUCT(LEN(B6:B17)) | The same down a column: how many people are away that day. |
| Calendar · shading | Conditional format: =WEEKDAY(DATE(YEAR($A$4),MONTH($A$4),B$5),2)>5 · =COUNTIF(hol_dates,DATE(…))>0 | Weekend and holiday rules rebuild the date from month + day number; leave letters are coloured by rules that test the cell's own value. |