Afia Kona
If you are looking for per day salary calculation formula in Excel, this article is for you. Here, we will walk you through 2 easy and suitable methods to do the task effortlessly.
Download Practice Workbook
2 Formulas to Calculate Per Day Salary in Excel
The following table has Name, Designation, and Salary columns. We will describe the per day salary calculation formula in Excel by using 2 easy methods. Here, we used Excel 365. You can use any available Excel version.
1. Applying Generic Formula to Calculate Per Day Salary in Excel
In this method, we will apply the generic formula for the per day salary calculation formula in Excel.
We know, 1 month=30 days.
Therefore, when we divide the salary by 30, we get per day salary.
Steps:
- First, we will type the following formula in cell E5.
=D5/30
Formula Breakdown
- Here, we simply divide cell D5 by 30.
- $8000/30 → becomes
- Output: $266.67
- Explanation: Here, $266.67 is the Per Day Salary.
- After that, press ENTER. Then, you can see the result in cell E5.
- Afterward, we will drag down the formula with the Fill Handle tool.
As a result, you can see the calculated per day salary in the Per Day Salary column.
Read More: How to Make Salary Sheet in Excel with Formula (with Detailed Steps)
Similar Readings
- How to Calculate HRA on Basic Salary in Excel (3 Quick Methods)
- Create a Monthly Salary Sheet Format in Excel (with Easy Steps)
2. Using Combined Formula to Calculate Per Day Salary in Excel
The following table has Name, Designation, Basic Salary, Allowances, Net Salary, and Per Day Salary columns. We will use this table to show the per day salary calculation formula in Excel.
To calculate Net Salary, first, we will calculate PF(Provident Fund), Tax, and Total Deduction. We will use the IF function to calculate Tax. and we will use the SUM function to calculate Total Deduction.
After that, we will calculate Net Salary by adding Basic Salary and Allowances and subtracting Total Deduction from them.
Afterwards, we will divide the Net Salary by 30 for the per day salary calculation formula in Excel.
Steps:
- Firstly, we will calculate PF. Here, we want PF for each employee as 10% of their Basic Salary.
Therefore, we type the following formula in cell D17.
=C17*10%
Formula Breakdown
- C17*10% → simply multiplies cell C17 with 10%.
- $8000*10% → becomes
- Output: $800.
- Explanation: Here, $800 is the PF of a particular employee.
- Furthermore, we will press ENTER. Then, you can see the result in cell D17.
- Later, we will drag down the formula with the Fill Handle tool.
Therefore, you can see the complete PF column.
Now, we will calculate Tax by using the IF function.
- Next, we will type the following formula in cell E17.
=IF(C17>=6000,C17*12%,C17*8%)
Formula Breakdown
- IF(C17>=6000,C17*12%,C17*8%) → the IF function returns C17*12% when C17>=6000 is TRUE, otherwise it returns C17*8%.
- IF(8000>=6000,8000*12%,8000*8%) → becomes
- Output: $960
- Explanation: As the logical_test is TRUE. Therefore, the result becomes 8000*12% which is $960.
- After that, press ENTER. Then, you can see the result in cell E17.
- Afterward, we will drag down the formula with the Fill Handle tool.
Later, will calculate Total Deduction by using the SUM function.
- Therefore, we will type the formula in cell F17.
=SUM(D17,E17)
Formula Breakdown
- SUM(D17,E17) → adds cell D17 with cell E17.
- SUM(800,960) → becomes
- Output: $1760
- Explanation: Here, $1760 is the Total Deduction of a particular employee.
- Later, press ENTER. Then, you can see the result in cell F17.
- In the end, we will drag down the formula with the Fill Handle tool.
Next, we will calculate Net Salary.
- Here, we will type the following formula.
=D5+E5-F17
Formula Breakdown
- D5+E5-F17 → simply adds cell D5 with cell E5 and then subtracts F17 from the summation.
- 8000+1000-1760 → becomes
- Output: $7240.
- Explanation: Here, $7240 is the Net Salary of a particular employee.
- After that, press ENTER. You can see the result in cell F5.
- Moreover, we will drag down the formula with the Fill Handle tool.
Finally, we will calculate Per Day Salary by using a division formula.
- Furthermore, we will type the following formula in cell G5.
=F5/30
Formula Breakdown
- Here, we simply divide cell F5 by 30.
- $7240/30 → becomes
- Output: $241.33
- Explanation: Here, $241.33 is the Per Day Salary of a particular employee.
- Next, press ENTER. Then, you can see the result in cell E5.
- Finally, we will drag down the formula with the Fill Handle tool.
As a result, you can see the calculated per day salary in the Per Day Salary column.
Read More:Â How to Calculate DA on Basic Salary in Excel (3 Easy Ways)
Practice Section
You can download the above Excel file to practice the explained methods.
Conclusion
Here, we tried to show you 2 methods for the per day salary calculation formula in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website ExcelDemy to explore more.
