How to Calculate Annual Leave in Excel (with Detailed Steps)

When an employee works in an organization, he earns some leaves based on criteria. Leave is an essential part of corporate or job life. We can track and calculate the leave of each employee in a proper way using Excel. Let’s have a look at the entire process to calculate annual leave in Excel.

Steps to Calculate Annual Leave in Excel

We know that there are multiple types of leave an employee enjoy. Those leaves may vary from one company to another. In this article, we will show how to calculate each leave separately. Each step is discussed in detail in the below section.

📌 Step 1: Insert Employee Information

We are going to calculate the annual leaves of an organization consisting of 10 employees. In this section, we will insert all the basic information in Excel now.

• First, we input the Name and IDs of employees.

📌 Step 2: Create a List of Leaves Allowed by Company

• Now, we will input the names of months in the dataset.

• Before inserting the month name, change the format of the cells. Select all the cells of the Month column.
• Press the right button of the mouse.
• Click on the Format Cells option.

• Click on the Number tab.
• Choose our desired Type from the Custom field.

• Finally, press the OK button.

• Now, we add a list of leaves with their codewords.

📌 Step 3: Create a Leave Record

In this section, we will record the leaves of each employee in the dataset. This information will be further used to calculate monthly and annual leaves. We need to use data validation and some formulas in this section.

• First, we create a worksheet with different column names.

• Now, apply Data Validation for the Employee ID column.
• Click on Cell C5.
• Go to the Data tab.
• Click on the Data Validation option from the Data Tools group.

• The Data Validation window appears here.
• Choose List in the Allow box.
• Our source sheet is where we saved the basic information. We choose the Employee ID cells here.

• Press the OK button last.

We can see a drop-down button beside Cell C5.

• Extend this Data Validation to all the cells of that column.
• Click on the Drop-down button and see the options.

• Similarly, way to apply data validation for the Leave Code column.
• Here, our source is the cells of the leaves type.

• Data Validation was successfully applied for both columns.

• In the Name column, we will apply a VLOOKUP formula that will extract the names of employees based on the Employee ID.
• Apply the following formula to Cell D5.

• We will use another formula based on Employee ID and leave Date.
• Apply the formula below to Cell B5.

This form a unique ID combining the Employee ID and Date value.

• Now, input leaves information on the 1st row.

• Finally, all leave information of the employees and sort them date-wise.

📌 Step 4: Calculate Monthly Leaves

Now, we will calculate the monthly leave of each employee for each month indicating the dates on the calendar. We will use the Control Box option here to control the month selection.

• Click on the Developer tab first.
• Now, choose Combo Box from the Insert group.

• Now, place the Control Box in a certain position.
• Press the right button of the mouse.
• Click Format Control from the Context Menu.

• Click on the Control tab.
• We select a range at the Input range This range contains the name of the month.
• We will link a cell, that will show the serial number of that month based on the source.
• In the drop-down list, we put 12, because of 12 months of the year.

• Finally, press OK.

We can see the selected month from the drop-down and the corresponding month number is showing.

• We input a formula on Cell S4.

This will extract the starting date of the month the depending on month number.

• Input the ID and Name in the columns in the dataset.

• Go to cell D7 and input S4.

• Go to cell E7 and put the following formula.

• Press the Enter button.

We need to change the date format. That will help in the visibility of the dataset.

• Choose our desired format from the Custom option.

• Now, expand the cells to a total of 31 cells for 31 days of the month.

• We want to get the names of the days of the corresponding dates.
• Put the following formula on Cell D8.

• Hit Enter and expand that formula to the rest of the cells on the right side.

• Change the alignment of days.
• Click the Alignment tab.
• Rotate 90 degrees.

• Look at the dataset.

• Now, select Range D6:AH6.
• Click on Merge & Center.

• Input S4 on that cell.

• Look at the worksheet.

The start of the month is showing. But we need only the month’s name.

• Change the format of that cell from the Format Cells window.

• Only the month name is showing now.

• Now, change the font and background color on the rows based on elements.

• Now, we will show the date-wise leaves of each employee.
• Put the following formula on Cell D9.

• Expand the formula to all cells of the month section.

We can see leaves with corresponding codes showing.

• Now change the month name from March to February.

Leave dates have changed successfully. Now, we will calculate each type of leave for each employee.

• Add 5 new columns with leaves names and codes.

• Put the following formula on Cell AI19.

• Expand that formula to the rest of the cells.

• Now, we will apply Conditional Formatting that will show 0 (zero) values as shades.
• Select Range AI9:AM18.
• Go to the Conditional Formatting group.
• Choose Equal To from the Highlight Cells Rules.

• Put 0 on the marked box and select a Custom Format.
• Again, press the OK button.

• Finally, we will calculate the leaves of each employee.

• Add a new column named Total.
• Got o cell AN9 and put the following formula.

• Drag the Fill Handle icon.

We get the total leaves of each employee for a specific month.

📌 Step 5: Calculate Annual Leaves

In this section, we will calculate the annual leave.

• We copy the last worksheet and customize a new worksheet for calculation.

• We have two cells for Start and End We input dates here.
• As we want to calculate for the whole year, we start on 1st January 2022 and end on 31st December 2022.

• Now, we input a formula based on the COUNTIFS function at cell D9.

• Press the Enter button.

• Expand the formula to the whole leave area.

• Apply a SUM formula at cell I9 for total leaves.

• Press the Enter button and drag the Fill Handle icon.

• Again, apply the conditional formatting to disappear the 0 (zero).

Download Sample Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, we made a template of 10 employees to calculate annual leave in Excel in the simplest format.

<< Go Back to Leave Calculation | Formula List | Learn Excel