hello in this video i show how to create leave tracker in excel
Leave Tracker is a regular task to track employees with their working days
With this way you can track individual leave, it will automatically count the days of absence by month and year for you.
You can reuse this file for different years
Here are the steps to create a leave tracker in Excel
Let's say I create a leave tracker for the year 2024
I track leave for 10 employees
The reasons for leave are vacation leave, sick leave, maternity leave and unpaid leave
This file allows you to track half day leave and full day leave
Let's start with January 2024
First you create a list of days of the month
January has 31 days
From the days of the month you determine the days of the week
Each day of the week is abbreviated to 3 letters
So every time you change the year, this file will update the days of the week for you
You can use this file in different years
As you can see January 1, 2024 is a Monday of the week
You copy the above formula for other days of the month and get the corresponding weekdays
Next you create drop-down lists for these cells
It ensures you choose the right reason for leave with the drop-down list
V, V1 and V2 are full day leave and half day leave
S, S1 and S2 are full day sick leave and half day sick leave
M is maternity leave and finally U is unpaid leave
After creating a drop-down list for one cell, you copy it to the remaining cells
The drop-down list has been applied to all remaining cells
I select any cell and check the results of the drop-down list
Next you highlight the cells by coloring the days marked as leave
I divided into different color groups
V, V1 and V2 are highlighted in green
With different colors, you will easily recognize the reason for each employee's leave
S, S1 and S2 you use a different color
Maternity leave is colored orange
Finally, unpaid leave is highlighted in brown
The text is white so you can easily see it on colored backgrounds
Different reasons for leave have been colored differently
I select any cell and check the coloring results
As you can see they have been colored as set above
#N/A
Here I count the number of leave days of the employee named Caroline Jenkins
First, count the number of days of absence with the reason of vacation leave and full day absence
Then add the half day leave in the morning
And finally, add half-day leave in the afternoon
So I counted Caroline Jenkins' vacation days in January
Next I counted the days of absence due to illness of this employee
I added some sick days in January
You just copy the formula and change the reason for leave to sick leave
And here are Caroline Jenkins' sick days
Next is counting maternity leave days
This only counts full day leave so it will be simpler than sick leave or vacation
The formula for counting maternity leave days is simpler
And finally count the days of unpaid leave
You copy the formula as above and change the reason for the leave
So you have finished counting the leave days for each different reason
You now sum up the absences in January for an employee named Caroline Jenkins
You just need to calculate the sum of the days of being absent for different reasons
After completing the count of days of absence of Caroline Jenkins, you apply to the remaining employees in the company
Of course in reality there are a lot of people who are not absent for the month and give zero results
So you will hide zero results by changing the text color to white
So you only see cells with absence days greater than 0
At this point you have completed tracking the absence of employees in January
Now you copy and apply to the rest of the year
However, February is different from year to year
For example in the year 2024 February has 29 days and is different from the year 2025
You use the following formula to determine the number of days in February in any year
As you can see in 2024 February has 29 days, but in 2025 February has 28 days
Because I cloned from January so the settings have not changed
At this point, you have completed tracking employee leave days in February
You do the same for the rest of the year
March has 31 days so I cloned January
You just need to update the days in March and the days of the week will automatically be updated
Next you clone and create leave day trackers for April to December
Because the steps are the same as in March, I will fast forward the steps so you don't have to go through the same steps over and over again.
And I recommend you to watch the important steps in this video
This is the employee's leave manager in December
The settings are the same as January
Now I show how to count the total absence days of each employee by year
You copy from any month and delete some unnecessary columns
Now you count Caroline Jenkins' leave days in 2024
You accumulate Caroline Jenkins' leave days for 12 months
After counting the leave days you copy the formula with other leave reasons
Next, you apply to other employees in the company
And finally count the days of unpaid leave
I have finished demonstrating how to create an employee leave tracker in Excel
Thanks for watching, don't forget to like and subscribe
Wish you have a useful leave tracking tool for yourself
You can add more employees, divide into work groups, add reasons for leave, and more customizations