Leave Salary Calculation in Excel (With Easy Steps)

Step 1 – Creating the Dataset

The dataset for the Leave Salary Calculation should include the employee names and a few quantities like Total working Days, Total Leave etc. for the calculation of the Total Leave Salary.

• We created a dataset with the joining and last working date of 4 employees like below.

Step 2 – Calculating Total Days, Months, and Years of Working

• For the total working days, copy the following formula in cell C7:

• Press Enter.
• Use the Fill Handle to copy the formula to the right.

• For calculating the total years, insert the following formula in cell C8 and press Enter.

• Use the Fill Handle to copy the formula up to cell F8.

• Calculate the total months of service by inseting the following formula in cell C9.

• Hit Enter and use the Fill handle to copy the formula in the cells to the right.

Step 3 – Leave Per Months Calculation

• Input the Yearly Leave in the range of cells C10:F10. This data varies with company policy and the law.

• To calculate the Leave Per Month, copy the following formula in cell C11 and hit Enter.

• Use the Fill Handle to copy the formula to the cells on the right side of that row.

Step 4 – Calculating Total Leaves

• Copy the following formula in cell C12 and press Enter.

• Drag the Fill Handle tool up to cell F12.

Step 5 – Leave Salary Calculation

• Input the gross salary of the employees in the range of cells C13:F13.

• Copy the following formula in cell C14 to get the Salary Per Leave and hit Enter.

• Use the Fill Handle to copy the formula in the cells to the right.

• Insert the following formula in cell C15 to get the Total Leave Salary.

• Hit Enter and use the Fill Handle to copy the formula through the row.

Download Practice Workbook

You can download the practice workbook from here or use it as a template.

<< Go Back to Salary | Formula List | Learn Excel