Leave Salary means the salary an employee gets even at the time of leave. Leave Salary Calculation is quite vital for any business or company and it varies with different companies. But there are some common procedures to calculate it. In this article, we will discuss the procedures for Leave Salary Calculation in Excel.
Leave Salary Calculation in Excel: Step-by-Step Procedures
We can easily calculate the Leave Salary in Excel by following a few steps. Here we are showing the procedure of Leave Salary Calculation step by step.
Step 1: Creating the Dataset
In this step, we will create the dataset for Leave Salary Calculation which should include the employee names and a few quantities like Total working Days, Total Leave etc. for the calculation of the Total Leave Salary.
- First, we create a dataset having the joining and last working date of 4 employees like below.
Step 2: Calculating Total Days, Months and Years of Working
In this step, we will calculate the Total Days, Total Years and Total Months of working for each employee.
- For calculating the total working days we write the following formula in cell C7.
=C6-C5
- Then we press Enter.
- Further, use the Fill Handle to copy the formula on the cell’s right side.
- For calculating the total years we have to write the following formula in cell C8 and press Enter.
=C7/365
- Use the Fill Handle to copy the formula up to cell F8.
- After that, we will calculate the total months of service by writing the following formula in cell C9.
=C8*12
- Afterward, we hit Enter.
- Use the Fill handle to copy the formula in the cells beside.
Step 3: Leave Per Months Calculation
In this step, we will calculate the leave per month for the employees.
- First, we input the Yearly Leave in the range of cells C10:F10. This data varies with company policy and the country’s existing law.
- Now, to calculate the Leave Per Month we write the following formula in cell C11 and hit Enter.
=C10/12
- Further, use the Fill Handle to copy the formula to the cells on the right side of that row.
Step 4: Calculating Total Leaves
We will calculate the total leaves for each employee in this step.
- We have to write the following formula in cell C12 and press Enter.
=C9*C11
- Next, use the Fill Handle tool up to cell F12.
Step 5: Leave Salary Calculation
Now we will calculate the Leave Salary of each employee in this step.
- We ought to input the gross salary of the employees in the range of cells C13:F13.
- Then we write the following formula in cell C14 to get Salary Per Leave and hit Enter.
=C13*12/365
- After that, use the Fill Handle to copy the formula in the cells beside.
- Lastly, we write the following formula in cell C15 to get the Total Leave Salary.
=C12*C14
- Next, hit Enter.
- Use the Fill Handle to copy the formula in the suitable side cells of that row.
- Finally, we get the Total Leave Salary.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
Leave Salary Calculation is quite vital for any company or business. In this article, we have shown the procedures for Leave Salary Calculation in Excel. If you have any queries or suggestions, please let us know by commenting.
<< Go Back to Salary | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hi Dear,
It was really helpful formula and its working excellent.
Thank you and have a wonderful years a head.
Hello Sam Arthur,
You are most welcome.
Regards
ExcelDemy