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

Get FREE Advanced Excel Exercises with Solutions!

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.
`=IFERROR(VLOOKUP(C5,'Data Set'!B5:C14,2,FALSE),"")`

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

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.
`=INDEX('Data Set'!E5:E16,'Monthly Leave Record'!M4)`

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.
`=IF(D7="","",IF(EOMONTH(\$S\$4,0)<D7+1,"",D7+1))`

• 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.
`=TEXT(D7,"ddd")`

• 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.
`=IFERROR(VLOOKUP(\$B9&D\$7,Record!\$B:\$F,5,FALSE),"")`

• 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.
`=COUNTIF(\$D9:\$AE9,AI\$8)`

• 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.
`=SUM(AI9:AM9)`

• 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.

Â

`=COUNTIFS(Record!\$E:\$E,">="&'Annual Leave'!\$E\$4,Record!\$E:\$E,"<="&'Annual Leave'!\$G\$4,Record!\$C:\$C,'Annual Leave'!\$B9,Record!\$F:\$F,'Annual Leave'!D\$8)`

• 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).

Read More: How to Calculate Half Day Leave in Excel

Conclusion

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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

1. Hi,

I try use below mentioned formula but why I cant add 2 lookup value..(\$B9&D\$7)

=IFERROR(VLOOKUP(\$B9&D\$7,Record!\$B:\$F,5,FALSE),””)

• Hello KO,
Thank you very much for your comment. The formula you mentioned in the comment is working smoothly from our side. If you share your workbook, we will be able to find out the solution to your problem. You can mail us at [email protected].

2. Is there any formula for a drop down list using different type of leaves in the attendance sheet that data will not able to move if I jump in to different month.

• Dear Prince,
Thank you very much for reading our article.
According to your query, 1st you wanted to know about a formula to create a drop-down list that will be used to select different types of leaves. You will get that in Step 3 of this article. In our Excel file, we selected leave using the drop-down list in Record sheet. Also when you move to any month leave information will be based on the Record sheet. So, information will not move from one month to another. Try this and hope you will get the solution.
Otherwise, send your Excel file with what you want to get and we will try to provide a solution. You can mail us at [email protected].
Thanks
Joyanta Mitra
ExcelDemy

Advanced Excel Exercises with Solutions PDF