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 and he/she took 3 days leave, Here I need the exact formula to calculate number of payable days in that month. I have attached the Sample Template here
I guess payable days shall exclude weekends, if so what they are (Sat+Sun, Sun only, etc). Same about public holidays. Bit more details please, with desirable result and why it shall be such.
- I will send you a direct message
- 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- 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.
