How to Track Employee Vacation Time in Excel (With Easy Steps)

This article shows how to track employee vacation time in Excel. A proper employee vacation tracking system can help your team members track how many vacation days they have spent and how many they have remaining. Here, we will take you through an easy and convenient method on how to track employee vacation time in Excel.

Here, we have information about some employees of an organization. In the dataset, columns C, D, E, and F contain the consecutive Employee ID, Employee Name, Designation, and Total Vacation.

Now, we wanna create an automatic employee vacation time tracker in Excel. Before proceeding any further, save the Excel file first.

• First, go to the File tab from the ribbon.

• Secondly, choose the desired file path, give the File Name, choose Excel Workbook from the Save as Type drop-down list, and lastly, click on Save.

Now, the spreadsheet file is saved on our local PC.

Step 01. Inserting the Name of the Month to Track Employee Vacation Time in Excel

Here, we want to track yearly employee vacations in Excel. We will separate our tracker into 12 months and will count the vacations month-wise. Also, we’ll create a summary of the vacations for the whole year in the end. We have to insert the name of the month on the top portion of the format. To do so, follow our steps below.

• First, select cell F2. Write down the formula below and press ENTER.

Formula Breakdown:

This formula returns the sheet name in the selected cell.

• CELL(“filename”,A1): The CELL function gets the complete name of the worksheet
• FIND(“]”,CELL(“filename”,A1)) +1: The FIND function will give you the position of ] and we’ve added +1 because we require the position of the first character in the name of the sheet.
• 255: Excel’s maximum word count for the sheet name.
• MID: The MID function uses the text’s position from start to end to extract a specific substring.

At this point, we can see the name of our Sheet on this cell with 2022.

Note: While typing this formula, make sure to enter any cell references on this sheet. Otherwise, the formula won’t work properly. For example, here we’ve entered the reference of cell A1.

• After that, change the name of the sheet to Jan. As we wanna make the employee vacation sheet for the month of Jan’22. We can easily see that the month’s name is automatically input into cell F2 after changing the Sheet Name.

• After that, we have to set the starting date for this month. Select cell C4 and input the formula below.

Here, F2 represents the month of Jan 2022.

A date that is stored as text can be changed into a serial number that Excel can identify as a date using  the DATEVALUE function. Then, use the Custom Format option to show the date as in the image below.

• After that, select cell G4 to show the ending date of that month. Write down the formula in the cell and press ENTER.

Here, C4 represents the starting date of the month.

The EOMONTH function is used to determine the end of the month.

Step 02. Creating Individual Date and Day

To mark the vacations, we must create different cells of different dates and days.

Follow the steps below.

• Select cell F7. Type the formula below and press ENTER.

• Then, press CTRL + 1 to open the Format Cells wizard. Select Custom and type d in the Type box. Next, click on OK.

• At this point, the date looks like the image below.

• To input the second date, select cell G7. Write the formula below and press ENTER.

Here, F7 and G4 represent the first date of the month and the last date of the month successively.

Formula Breakdown:

We’ve applied a logical test using the IF function here. If the date in cell F7 is less than the month’s end date, then the formula returns the next date from the date in cell F7. Otherwise, it will show nothing.

• Use the Fill Handle tool and drag it to cell AJ7 to exhibit the remaining days of the month.

• After that, select the columns in the F: AJ range. Then, go to the Home tab. Select Cells > Format > Column Width.

• Type 4 in the Column Width box and click on OK.

• However, we have our column at the perfect width because we will just put V into these cells.

• Later, select cell F6 and type the formula below. Then, press ENTER.

• At this point, indicate the other days till the date of 31 using the Fill Handle Tool. From the Home tab, click on the small arrow in the Alignment group > write down 90 in the Degrees box and click OK.

• However, the name of the day rotates vertically with this action.

• After that, fill the cells with your preferred colors and apply All Borders to the cells.

Step 03. Formatting Weekly Holidays

As Sunday is the weekly holiday, we don’t have to input anything into those cells. We will highlight those cells with Conditional Formatting.

• At first, select cell F8. Then, go to the Home tab and select Conditional Formatting > New Rule.

• At this moment, the New Formatting Rule dialog box will open. Firstly, select the options as in the image below. Then, write the formula in the formula box and click on Format.

• Then, the Format Cells dialog box opens. After that, select the Fill tab > Choose your preferred color and click on OK.

• It returns to the New Formatting Rule dialog box. After that, click on OK.

• Now, use the Fill Handle tool to expand this formatting in cells in the F8:AJ17 range. The cells under the columns of the Sun are highlighted in red.

Step 04: Inserting Vacation in Cells as V

The main objective of this article is to create an employee vacation time tracker in Excel. For this, we have to input vacation days into cells in the F8:AJ17 range.

• Firstly, select cells in the F8:AJ17 range. Go to the Data tab and select Data Tools > Data Validation.

• At this point, the Data Validation dialog box opens. Now, go to Settings. Select List from the drop-down list in the Allow box. Write down V in the Source box. Now, click on OK.

Here, V means “vacation”.

• Secondly, click on cell G8. You can see a drop-down list containing just V. That means we could just input this text only.

• Thirdly, place the vacation days of all individuals in their respective cells. A portion of vacation marking is like below.

• Fourthly, select the cells in the F8:AJ17 range. After that, go to the Home tab. Then, select Conditional Formatting > Highlight Cells Rules > Text that Contains.

• Then, in the Text That Contains dialog box, write down V in the Format cells that contain the text box. Choose the formatting as in the image below. Lastly, click OK.

• However, cells containing V reflect the defined highlighting.

Read More: How to Create Employee Monthly Leave Record Format in Excel

Step 05: Counting Vacation Days and Vacations in Hand

In this phase, we’ll count the total number of vacations taken this month.

• Firstly, select cell AK8 and type in the formula below. Then, press the ENTER key.

Here, F8:AJ8 represents the cell range to input V.

In this case, we used the COUNTIF function to count V in the above range.

• Secondly, select cell AL8. Then, type the formula below and press ENTER.

• Then, format the cells with effective colors to make the data more visible.

• Finally, use the Freeze Panes tool to freeze the columns in the A:E range and freeze the rows in the 1:7 range. To do this, select cell F8 first and go to the View tab. Select Window group > Freeze Panes drop-down menu > Freeze Panes tool.

• The cells on the left and above F8 become frozen. It is very helpful as our sheet is expanded horizontally.

Step 06: Following Steps 3-5 for Other Months

Now, we’ll create the same type of sheet for the remaining months of the year.

• Initially, right-click on the Sheet Name of the Jan worksheet. Then, select Move or Copy.

• Secondly, select the options as shown in the image below and click on OK.

• After that, write down Feb in our 2nd Sheet Name. You can see that the worksheet is totally changed according to the February month. The weekly days were set up as consecutive dates. Also, you will notice the start and end date of the month changed automatically.

• But we’ve to do the Conditional Formatting again to highlight the holidays. Though it’s troublesome, we’ve to do it manually for each sheet.

• Firstly, select cell F8. Then, go to the Home tab and select Conditional Formatting > Manage Rules.

• At this moment, the Conditional Formatting Rules Manager opens. First, choose This Worksheet from the Show formatting rules for list, and then, select the range to apply the formatting and click on OK.

• Finally, we are successful in highlighting the weekly holidays again.

• There is a slight change in the formula for counting the Vacations in Hand in this worksheet. At first, select cell AL8 and write down the formula below. Then, press the ENTER key.

Here, we are subtracting the Vacation Taken in February month from the Vacation in Hand of month January.

Step 07: Creating a Summary to Track Employee Vacation Time in Excel

Lastly, we will create a summary of the whole year in a single worksheet named Summary.

• Firstly, we calculated the Vacation Taken in the year. To do this, select cell G6 and write down the formula below. Then, press the ENTER key.

• Finally, we showed the Vacations in Hand at the present time. Hence, select cell H6 and type the formula below. After that, press ENTER.

Read More: How to Create Leave Tracker in Excel

Benefits of Employee Vacation Time Tracker

💡 As this tracker template is completely automatic, we don’t need to make a format for the month every time. We can just copy our created worksheet first and change the name of the worksheet to convert it into another month’s tracker.

💡 Also, we can use this template for other years. We just have to edit the formula in cell F2.

In the above formula, just write 2023 in place of 2022. Magically, this template will be compatible until 2023 also.

💡 This template is completely ready to use. You can assign different Vacation days for individual employees. Just edit the cells in the F6:F15 range in the Summary worksheet.

💡 Besides these, you can add more employees according to your needs. Just use the Fill Handle tool to get results in those new cells.

🌟 So, what’s holding you back? Download the template now. Use it and turn the race in your favor.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Conclusion

Thank you for reading this article. We strongly believe this would be beneficial to track the employee vacation time in Excel. Please let us know in the comment section if you have any queries or suggestions.

Related Articles

• How to Create Employee Leave Record Format in Excel
• How to Create Maternity Leave Calculator in Excel

<< Go Back to Excel Employee Leave Tracker | Excel HR Templates | Excel Templates