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

Step 1 – Insert Employee Information

We are going to calculate the annual leaves of an organization consisting of 10 employees.

• Input the Names and IDs of employees.

Step 2 – Create a List of Leaves Allowed by Company

• Input the names of months in a separate column.

• Select all the cells of the Month column.
• Right-click and select the Format Cells option.

• Click on the Number tab.
• Choose a date Type from the Custom field.

• Press the OK button.

• Add a list of leaves with their codewords.

Step 3 – Create a Leave Record

• Create a worksheet with the following columns: Leave ID, Employee ID, Name, Date, Leave Code.

• 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.
• The source sheet is the basic information. Choose the Employee ID cells here.

• Press the OK button.

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

• Apply data validation for the Leave Code column.
• The source is the cells of the leave types.

• Data Validation was successfully applied for both columns.

• Apply the following formula to Cell D5.

• Apply the formula below to Cell B5.

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

• Input leave information in the first row.

• Insert the leaves per your requirements.

Step 4 – Calculate Monthly Leaves

• Click on the Developer tab.
• Choose Combo Box from the Insert group.

• Place the Control Box in a table.
• Right-click on the Combo Box and select Format Control.

• Click on the Control tab.
• Select a range of month names at the Input range.
• Link a cell that will show the serial number of that month based on the source.
• For Drop down lines, put 12 because of 12 months of the year.

• Press OK.

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

• Use this formula on Cell S4.

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

• Input the IDs and Names 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.

• Choose the format dd from the Custom options.

• Expand the cells to a total of 31 cells for 31 days of the month.

• Put the following formula in Cell D8.

• Hit Enter and AutoFill that formula to the right to cover all days.

• Click the Alignment tab.
• Rotate 90 degrees.

• Look at the dataset.

• Select D6:AH6.
• Click on Merge & Center.

• Input S4 on that cell.

• Look at the worksheet.

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

• Only the month name is showing now.

• Change the font and background color for the various table elements.

• Put the following formula in Cell D9.

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

We can see leaves with corresponding codes showing.

• Change the month name from March to February.

• Leave dates have changed successfully.
• Add 5 new columns with leave names and codes.

• Put the following formula in Cell AI19.

• Expand that formula to the rest of the cells.

• Select AI9:AM18.
• Go to the Conditional Formatting group.
• Choose Equal To from Highlight Cells Rules.

• Put 0 in the box and select a Custom Format.
• Press the OK button.

• We will calculate the leaves of each employee.

• Add a new column named Total.
• Go to cell AN9 and put the following formula.

• Drag the Fill Handle icon down.

Step 5 – Calculate Annual Leaves

• Copy the last worksheet and customize a new worksheet for calculation.

• We have two cells for Start and End. We input dates here. We start on 1st January and end on 31st December.

• Input this formula in 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 down.

• Apply the conditional formatting to remove the 0 (zero) values.

Download the Template

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