Speaker 1: Hello, in this video I show how to create leave tracker in Excel.

Speaker 2: 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.

Speaker 1: 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.

Speaker 2: 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 three 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 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 list 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. For each different leave reason, you color the cells differently. I divide it 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 said above. Next, you count the employee's days off from work. 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 6 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 I will have zero results by changing the text color to white. So you only see cells with absence days greater than zero. 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. So you can 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. Continue with March 2024. Continue with March 2024. 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 number of full year absence days of each employee. 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. Thank you for watching.