Team leave calendar in Google Sheets: avoid clashes
Short answer: Keep one Leave Log (name, team, start, end, status) and build the calendar from it with COUNTIFS. A date falls inside a request when start ≤ date and end ≥ date. Count that for each person and each day to get who's off and how many are out, then use the same overlap test on every new request to flag a clash before you approve it.
Formulas tested in a real spreadsheet · Google Sheets + Excel · Updated November 2026 · By Hazelshelf
Set up once
Leave Log tab with columns A Name, B Team, C Start, D End, E Status (Pending / Approved / Denied). Add a row for every request, and never type into the calendar itself.
Step by step
1. Build the calendar grid
Names go down column A from A3. Put the first date in B2 and =B2+1 in C2, then fill right for the month. Put this in B3 and fill across and down:
=IF(COUNTIFS(Log!$A:$A,$A3, Log!$C:$C,"<="&B$2, Log!$D:$D,">="&B$2, Log!$E:$E,"Approved")>0, "OFF", "")
2. Add a people-off row
Under the names. Copy it with "Pending" to see what's waiting for approval on each day.
=COUNTIFS(Log!$C:$C,"<="&B$2, Log!$D:$D,">="&B$2, Log!$E:$E,"Approved")
3. Colour it
Select the grid, go to Format › Conditional formatting › Custom formula, and add =B3="OFF" (fill it hazel) and =WEEKDAY(B$2,2)>5 (grey weekends). On the count row, =B$7>=2 turns red when two or more people are out.
4. Flag clashes on each request
In Log column F. It counts other approved people on the same team whose dates overlap. Then in G2: =IF(F2>=Settings!$B$20,"Clash","OK"), where B20 is how many people can be out at once. Drop the team criterion to check the whole company.
=COUNTIFS($A$2:$A$500,"<>"&A2, $B$2:$B$500,B2, $C$2:$C$500,"<="&D2, $D$2:$D$500,">="&C2, $E$2:$E$500,"Approved")
5. Show who's off today
Works in Google Sheets and Excel 365. In our test, Ana (Ops, Dec 21–24) and Ben (Ops, Dec 23–31) both flagged Clash, and this cell returned "Ana, Ben" for Dec 23.
=TEXTJOIN(", ",TRUE, FILTER(Log!A2:A500, (Log!C2:C500<=TODAY())*(Log!D2:D500>=TODAY())*(Log!E2:E500="Approved"), "nobody"))
6. Share it view-only
It opens in the Google Sheets phone app, so people can check it before booking an appointment. Keep edit access for whoever approves leave.
Which option fits
| Option | Who's off | Clash warning | Balances | Cost |
|---|---|---|---|---|
| Shared Google/Outlook calendar | Yes | No, spot it by eye | No | Free |
| DIY sheet (this guide) | Yes, plus a daily count | Yes (COUNTIFS) | Only if you add them | Free, ~1 hour |
| Hazelshelf Leave & PTO Tracker | Month + 12-month views | Yes, max-off setting, team or company | Yes, with accrual and carryover | $14 once |
| HR / leave software | Yes | Yes | Yes | Usually per user, per month |
Skip the build
The Leave & PTO Tracker has this set up already: a team calendar with a people-off count, clash warnings before you approve, Google Form requests, and balances that update themselves (see how accrual works).
Google Sheets + Excel · up to 50 people · no macros · 14-day help-or-refund
FAQ
How do I see who is off on a given day in Google Sheets?
Use FILTER on your leave log for rows where start ≤ the date, end ≥ the date, and status is Approved, and wrap it in TEXTJOIN to get one line like "Ana, Ben".
How do I check if two leave requests overlap?
Two date ranges overlap when the first starts on or before the day the second ends, and ends on or after the day the second starts. In a sheet, that's COUNTIFS(start,"<="&this_end, end,">="&this_start).
How do I stop too many people being off at the same time?
Decide on a maximum per team, count the overlapping approved requests for each new request, and flag it when the count reaches the maximum. Check that flag before you approve anything.
Can staff see the leave calendar on their phone?
Yes. Share the Google Sheet as view-only and it opens in the Sheets app. Keep edit access for whoever approves leave.
Should weekends and holidays count as days off?
Not toward balances. Count working days with NETWORKDAYS.INTL(start, end, 1, Holidays), which skips Saturday–Sunday and any dates in your holiday list. Use weekend code 7 for a Friday–Saturday weekend.
Is a spreadsheet enough for a small team?
For about 5–50 people with one approver, usually yes. Switch to software when you need automatic notifications, many approvers, or payroll sync.
More guides: How to track PTO in Google Sheets · PTO accrual formulas