How to easily create Leave tracking in Google Sheets, by NETVN82 on YouTube. 8 min 48 s.
- Video length
- 8 min 48 s
- Time to do it
- 30 minutes
- Difficulty
- Medium
- Steps
- 9
What you need
Steps
Open a blank Google Sheets file and name it Leave Tracker. Add one tab per month and set up a grid with employee names down the left column and dates across the top row.
Put the year in a single cell at the top of each sheet. Use an EOMONTH formula to find the last day of the month so February and 30-day months resize on their own.
Fill the date row with a plus-one formula starting from the first of the month, then format those cells to show only the day number.
Add a WEEKDAY formula beside each date to flag Saturdays and Sundays, then grey out those columns so weekend leave is not counted.
Select all the leave cells and open Data, then Data validation. Add a drop-down list with your leave types, such as Sick, Vacation and Unpaid.
With the same cells selected, open Format, then Conditional formatting. Add a rule for each leave type using Text is exactly and give each one a different fill colour.
Add a Half day option to the drop-down and write your totals so a half day counts as 0.5 instead of a full day.
Create a summary tab with employee names in rows and leave types in columns. Use COUNTIF to total each leave type for each person across the monthly tabs.
Change the year cell to test a new year, then check February and a 30-day month to confirm the calendar and totals still line up.
Tips and common mistakes
Freeze the top rows and the name column so the headers stay visible while you scroll through the year.
Type leave as text codes rather than numbers so your COUNTIF rules stay simple and consistent.
Protect the calendar and formula cells so staff can only edit their own leave entries.
Keep one file per year and copy it for the next, so old records are never overwritten.
Questions
How do I count half days in the totals?
Add a Half day option to the drop-down and use COUNTIF to count it separately. Add that count multiplied by 0.5 to the full-day total.
Can I reuse the file for next year?
Yes. Change the year cell and clear the leave entries. The date formulas rebuild the calendar for the new year.
Are weekends included in the leave totals?
No. The WEEKDAY formula flags Saturday and Sunday, so you can grey those columns out and leave them out of your summary formulas.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
