Forum Discussion
Days Calculation for Payroll
Need a Formula for Calculating exact days for Salary calculation Example: If employee DOJ is on July 13,2023 and Payroll end date is July 31,2023 and employee allowed to take 1 day leave in month ...
Hi,
Just want to make sure I've understood correctly.
If there are 31 Days in the month and..
1) They're allowed one leave day, but don't take that leave day, then its 31 days payable?
2) They're allowed one leave day, and take that leave day, then still 31 days payable?
3) They're allowed one leave day, but take three leave days, then only 29 days payable?
If thats correct then..
Use a MIN Formula to work out the minimum of either (Leaves allowed less Leaves taken, or 0)
Then do 'Total days in month' plus that MIN formula
In your example it would be =H2+MIN(I2-J2,0), which returns 29 days payable
Thanks
Just want to make sure I've understood correctly.
If there are 31 Days in the month and..
1) They're allowed one leave day, but don't take that leave day, then its 31 days payable?
2) They're allowed one leave day, and take that leave day, then still 31 days payable?
3) They're allowed one leave day, but take three leave days, then only 29 days payable?
If thats correct then..
Use a MIN Formula to work out the minimum of either (Leaves allowed less Leaves taken, or 0)
Then do 'Total days in month' plus that MIN formula
In your example it would be =H2+MIN(I2-J2,0), which returns 29 days payable
Thanks
we need to Consider the date of Joining as well!
- There is a lot of unknowns for me to give you a solid answer.
If this sheet is ONLY going to have employees who joined in the Payroll month you're calculating, then you could use =H2+MIN(I2-J2,0)-(DAY(C2)-1) as a quick dirty way of calculating this.
In your example, this will subtract 12 from the total (This is assuming the day they joined (13th) is still payable)
But again, this will fall apart if you come to repopulate this come August and still have a DOJ in July.