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.

Download Practice Workbook

Please download the workbook to practice yourself.

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.

3 Suitable Ways to Calculate Prorated Salary in Excel

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.

Read More: How to Calculate Annual Salary in Excel (with Detailed Steps)

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.

Read More: How to Create Salary Slip Format with Formula in Excel Sheet

Similar Readings

• Leave Salary Calculation in Excel (With Easy Steps)
• How to Model Salary Regression Analysis in Excel (2 Ways)
• Calculate Annual Salary in Excel (with Detailed Steps)
• How to Calculate DA on Basic Salary in Excel (3 Easy Ways)
• How to Create Tally Salary Slip Format in Excel (With Easy Steps)

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.

Read More: How to Make Salary Sheet in Excel with Formula (with Detailed Steps)

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.

Read More: How to Calculate Monthly Salary in Excel (with Easy Steps)

Things to Remember

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

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. However, you will find such interesting blogs on our website Exceldemy.com. 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.

Related Articles

• How to Calculate HRA on Basic Salary in Excel (3 Quick Methods)
• Per Day Salary Calculation Formula in Excel (2 Suitable Examples)
• Attendance Sheet with Salary in Excel Format (with Easy Steps)
• How to Calculate Income Tax on Salary with Example in Excel
• Calculate Gross Salary in Excel (3 Useful Methods)
• How to Calculate Net Salary in Excel (With Easy Steps)