Proper Spreadsheets

Templates › Free to download

Team Annual Leave Planner

Made for managers and small businesses planning time off for a team.

Plan and track time off for a small team: allowances, bookings, approvals and who is away. The summary carries a chart that redraws itself as you type.

Tabs
3
Columns
16
Fill themselves in
16

Freeno card, no email, no sign up

Download it free

  • Opens in Excel, Google Sheets and Numbers
  • Free, with nothing to sign up for
  • No macros, nothing to install
  • Describe your own when you are ready

Yours to keep, change and share. Built exactly as every spreadsheet here is, so it is a fair picture of what you would get from describing your own.

The workbook's summary and the table behind it
A row typed into the real file. Every figure that changes is the spreadsheet's own arithmetic.
The summary page, every figure calculated from the tables
The chart, drawn from the totals in the table beside it
What is inside: the tabs, the columns and what fills itself in
Where you type, and the shaded columns that work themselves out
The guide sheet that opens first and explains the file
Everything included in the download

Everything inside, before you pay

This is the whole design: every tab, every column and how each calculated one is worked out. White cells are yours to fill in; the shaded ones work themselves out and are locked so a formula cannot be overwritten by accident. There is room for 430 rows.

Team

One row per person. Booked, left and sick figures count this calendar year and ignore declined bookings. Room for 30 rows.

ColumnTypeWorked out as
Nametext
Roletext
Holiday Allowancenumber
Holiday BookednumberIF(ISBLANK([name]),"",SUMIFS([Leave!working_days],[Leave!person],[name],[Leave!leave_type],"Holiday",[Leave!status],"<>Declined",[Leave!first_day],">="&DATE(YEAR(TODAY()),1,1),[Leave!first_day],"<="&DATE(YEAR(TODAY()),12,31)))
Holiday LeftnumberIF(OR(ISBLANK([name]),ISBLANK([allowance])),"",[allowance]-SUMIFS([Leave!working_days],[Leave!person],[name],[Leave!leave_type],"Holiday",[Leave!status],"<>Declined",[Leave!first_day],">="&DATE(YEAR(TODAY()),1,1),[Leave!first_day],"<="&DATE(YEAR(TODAY()),12,31)))
Sick DaysnumberIF(ISBLANK([name]),"",SUMIFS([Leave!working_days],[Leave!person],[name],[Leave!leave_type],"Sick",[Leave!status],"<>Declined",[Leave!first_day],">="&DATE(YEAR(TODAY()),1,1),[Leave!first_day],"<="&DATE(YEAR(TODAY()),12,31)))
Off TodaytextIF(ISBLANK([name]),"",IF(COUNTIFS([Leave!person],[name],[Leave!status],"<>Declined",[Leave!first_day],"<="&TODAY(),[Leave!last_day],">="&TODAY())>0,"Yes","No"))
Off In Next 14 DaystextIF(ISBLANK([name]),"",IF(COUNTIFS([Leave!person],[name],[Leave!status],"<>Declined",[Leave!first_day],"<="&(TODAY()+14),[Leave!last_day],">="&TODAY())>0,"Yes","No"))

Leave

One row per booking. Enter the working days it uses, leaving out weekends and bank holidays. Room for 400 rows.

ColumnTypeWorked out as
Personchoice (from Team › name)
Typechoice (list)
First Day Offdate
Last Day Offdate
Working Daysnumber
Approvalchoice (list)
WhentextIF(OR(ISBLANK([first_day]),ISBLANK([last_day])),"",IF([last_day]<[first_day],"Check dates",IF([first_day]>TODAY(),"Upcoming",IF([last_day]<TODAY(),"Taken","Off now"))))
Notestext

Summary

Who is away and how leave is being used. Day totals count this calendar year and ignore declined bookings.

MeasureWorked out as
People off todayCOUNTIF([Team!off_today],"Yes")
First person off todayIFERROR(INDEX([Team!name],MATCH("Yes",[Team!off_today],0)),"Nobody")
People off in the next 14 daysCOUNTIF([Team!off_next_14],"Yes")
Bookings waiting for approvalCOUNTIF([Leave!status],"Pending")
Holiday days takenSUMIFS([Leave!working_days],[Leave!leave_type],"Holiday",[Leave!status],"<>Declined",[Leave!first_day],">="&DATE(YEAR(TODAY()),1,1),[Leave!first_day],"<="&DATE(YEAR(TODAY()),12,31))
Sick days takenSUMIFS([Leave!working_days],[Leave!leave_type],"Sick",[Leave!status],"<>Declined",[Leave!first_day],">="&DATE(YEAR(TODAY()),1,1),[Leave!first_day],"<="&DATE(YEAR(TODAY()),12,31))
Training days takenSUMIFS([Leave!working_days],[Leave!leave_type],"Training",[Leave!status],"<>Declined",[Leave!first_day],">="&DATE(YEAR(TODAY()),1,1),[Leave!first_day],"<="&DATE(YEAR(TODAY()),12,31))
Other days takenSUMIFS([Leave!working_days],[Leave!leave_type],"Other",[Leave!status],"<>Declined",[Leave!first_day],">="&DATE(YEAR(TODAY()),1,1),[Leave!first_day],"<="&DATE(YEAR(TODAY()),12,31))
Total holiday allowance leftSUM([Team!holiday_left])
People over their holiday allowanceCOUNTIF([Team!holiday_left],"<0")