How to Calculate Prorated Salary in Excel (3 Suitable Ways)

While using Excel, sometimes you may need to calculate salary of employees if you run a company. To calculate the salary, you may need to calculate prorated salary in Excel. In this article, you will be able to know how to Calculate Prorated Salary in Excel. I will show three suitable ways and describe the whole procedure step by step with necessary illustrations. Hope you will enjoy the article and, by practicing the workbook, you can increase your Excel skills.

What Is Prorated Salary?

Prorating of salary means a distribution of salary among the employees according to their working hours or working days. We can say this as a weighted distribution. If an employee works 20 days over 30 days, he or she will get the salary for those days only instead of the whole month’s salary.

Calculate Prorated Salary in Excel: 3 Suitable Ways

In this article, I will show three suitable and easy methods to calculate prorated salary in Excel. I will consider a dataset like the one given below. The dataset is about Employee Information of ABC Company. You can see the dataset having three columns B, C, & D called Employee name, Joining Date, and Ending Date. The dataset ranges from B4 to D10. You can also see that there is another data of Annual Salary which is $15000.00. With this dataset, I will show how to calculate Prorated Salary in Excel.

1. Use of YEARFRAC Function to Calculate Prorated Salary in Excel

This is the first method to get the Prorated Salary in Excel. I added two new columns in the dataset called Year Fraction and Amount. I will use the YEARFRAC function here. Follow the procedure step by step. I have attached the necessary illustrations here for your better understanding.

Steps:

• Select the E5 cell first.

• Then, write down the following formula in the selected cell.

• After that, press the Enter button.

• Consequently, you will find the year fraction.
• Then drag the Fill-handle from E5 to E10 to copy the formula.

• Hence, you will get the all-year fractions.

• Now select the F5 cell and copy the following formula in it.

• After pressing Enter, you will get the result like the picture. Copy down the formula from E5 to E10 cells.

• As a result, you will get the prorated salary of the employees.

2. Calculating Prorated Salary in Excel by Using DAYS Function

This is the second method of this article. I will use the DAYS function here to calculate prorated salary in excel. The dataset will remain the same as the previous one. Let’s follow the steps described below.

Steps:

• First, select the E5

• Then, copy the following formula in the selected cell.

• After pressing Enter, you get the difference of days.
• Then, drag the Fill-handle from E5 to E10.

• As a result, you will get all the differences of days in the E5 to E10 cells.

• Then, write down the following formula in the F5 cell:

• After that, press Enter.
• Therefore, you will get the result in the F5 cell.
• Then, copy down the formula from F5 to F10.

• Hence, you will find the desired result in the F5 to F10 cells.

3. Inserting Generic Formula to Calculate Prorated Salary in Excel

This is the last method I am going to describe in this article. Here, I will introduce a generic formula to calculate the prorated salary in Excel. This is a very simple way. I hope you will understand the method easily. Follow the steps given below.

Steps:

• Select the E5 cell first.
• Then, write down the following formula in that cell.

• After that, press the Enter button.
• After getting the result, use the Fill Handle to copy formula from E5 to E10.

• Consequently, you will get the result just like the picture given below.

• Now, apply the following formula in the F5 cell.

• After that, press Enter.

• You will get the result like the picture given below.

• After copying down the formula from F5 to F10 cell, you will get the result just like the picture given below.

How to Prorate Salary for Semi-Monthly

Sometimes, it becomes important to prorate salary for semi-monthly. In this section of this article, I will show how to prorate salary semi-monthly. I will consider the same dataset mentioned earlier. I am taking every employee of this company will get the semi-monthly prorated salary. Only a new column called Amount was added here. Follow the steps described below.

Steps:

• Select the E5 cell first.

• Then, write down the following formula in that cell and copy it down from E5 to the E10 cells.

• As a result, you will get the prorated semimonthly salary in the E column.

Things to Remember

• I have used the 365 in DAYS function to divide the annual salary to convert it into per day’s salary.

Download Practice Workbook

Please download the workbook to practice yourself.

Conclusion

In this article, I have tried to explain how to Calculate Prorated Salary in Excel. I hope, you have learned something new from this article. Now, extend your skill by following the steps of these methods. I hope you have enjoyed the whole tutorial. If you have any kind of queries feel free to ask me in the comment section. Don’t forget to give us your feedback.

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