The dataset showcases Employee name, Joining Date, and Ending Date.
Method 1 – Using the YEARFRAC Function to Calculate the Prorated Salary in Excel
Two new columns were added to the dataset: Year Fraction and Amount.
Steps:
- Select E5.
- Enter the following formula in the selected cell.
=YEARFRAC(C5,D5,1)- Press Enter.
- The year fraction will be displayed.
- Drag down the Fill Handle to see the result in the rest of the cells.
This is the output.
- Select F5 and enter the following formula.
=$C$12*E5
- Press Enter.
This is the output.
- Drag down the Fill Handle to see the result in the rest of the cells.
This is the output.
Method 2 – Calculating the Prorated Salary in Excel by Using the DAYS Function.
Steps:
- Select E5
- Enter the following formula in the selected cell.
=DAYS(D5,C5) + 1
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
This is the output.
- Enter the following formula in F5.
=($C$12/365)*E5
- Press Enter.
You will see the result in F5.
- Drag down the Fill Handle to see the result in the rest of the cells.
This is the output.
Method 3 – Adding a Generic Formula to Calculate the Prorated Salary in Excel
Steps:
- Select E5.
- Enter the following formula in the selected cell.
=D5-C5+1
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
This is the output.
- Enter the following formula in F5.
=($C$12/365)*E5- Press Enter.
This is the output.
- Drag down the Fill Handle to see the result in the rest of the cells.
How to Prorate the Semi-Monthly Salary
Steps:
- Select E5.
- Enter the following formula.
=$C$12/2
- Drag down the Fill Handle to see the result in the rest of the cells.
This is the output.
Download Practice Workbook
Download the workbook to practice.
<< Go Back to Salary | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
