• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating day allowance for sick leave

M

Micfree

New Member
I have played around with a formula (Column L), but my formula is falling in some rows-rows 23 and 41. The correct days working out manually are in column M.
An employee gets 10 days allowance, including in the last 12 months (Column E plus column H). After exceeding 10 days, they receive SSP- where the first 3 days are not paid.
 

Attachments

  • testsick.xlsx
    36.9 KB · Views: 4

Micfree​

You have there ... one employees data with many overlaps.
You've only eight correct values in M-column - what are other blanks? .
Only few row seems to be over ten days.
What is SSP? Your sheet has SSP-2
You wrote something about 12 months and You data covers May and June.
What are Your expected correct results?
 
Thank you Vletm,
Column L is my working. It seems to work but falls in rows 23 and 41. SSP means statutory sick pay. An employee gets 10 days allowance. For anything over 10, an employee gets SSP. For any over 10 days allowance, SSP is calculated as: first 3 days are unpaid( Called waiting times), then daily rate times the number of days exceeding 3 days. Daily rate is: N1/divided by work pattern (Column F). I have attached my expected result in column M.
10 days is cumulative, that is any days taken as off sick in the past plus now counts towards 10 days. As you see, some employees have already exceeded the 10 days limit, hence all the duration minus 3 is payable as SSP.
Let's take as an example: row 6 has already a cumulative of 17. As this exceeds 10, all duration(14-3) is SSP.
Row 23: has already a cumulative of 7 (Column H). As he still has 3 days left to make the 10 days allowance, 3 days out of 5 duration goes to cover it. Then we have left with 2 days. that is (10-7=3). 5 duration minus 3=2. So 2 days are paid as SSP.
 

Attachments

  • testsick.xlsx
    34.1 KB · Views: 2

Micfree​

Based Your writing to my reply.
I tested something ... check columns from P to T.
#P: If there is D then there are differences between Your E-column and result of D-C -columns. I use Q-column value
#R : Total days
#S : Days over 10+3 days
#T : Why You've I-column if ... no matter? This tries to show Your ... SSP
Your L-column formula has some connection with Your RTW (B-column) ... why?
... and I cannot use that L-column formula at all.
 

Attachments

  • testsick.xlsx
    36.5 KB · Views: 2
Thanks vletm
Column E- Duration is not simply the difference between columns C and D. It depends on work pattern shift. I can't use networkday function as some employee work on 4 On and 4 Off shifts. Column E figures are produced by software. If you look at my formula in column L, if RTW is "Unpaid" regardless of the cumulative figure, the person only gets SSP, but only if SSP is>3.
What I am trying to achieve is:
If RTW is "Unpaid" and duration is >3, calculate SSP.
If duration plus Cum (Columns E and H) is <=10, I do not need to work out SSP as the employee is covered by 10 days allowance.
If plus Cum (Column H) is >10 (Without adding duration), it is simply =duartion minus 3 times the rate.
Where I got stuck and gets tricky is when duration plus cum >10. Upto 10 is covered by allowance, then I need to work out SSP. my formula falls on at least two rows. For eg, row 41: duration is 10 and cum is 0.5. Only 0.5 SSP. Why? 0.5+9.5=10. Because I used 9.5 out of 10 duration to 10 days allowance, then left with 0.5 for SSP, but my logic falls.
 
Your E-column:
Yes, anything is not simply. Your sheet shows fixed values.
If that is the point ... then You can modify my Q-column formula.

Your L-column formulas:
a) As I comment I cannot use that L-column formula at all. ( You can see one reason below. )
Screenshot 2023-06-28 at 11.36.51.png
b) If it won't work with You ... then it won't work correct.

Seems You skipped my formulas ... okay.
Instead Your L-column 'long' formula,
You could try to solve Your expected results in parts; per different columns.
... then You could see - what is working as You want? ... as well as ... what should modify?
... and after that - You could combine those elements in one formula.
 
Sorry! I did not skip your formula. It is not working. You do not need to use my formula if you have a better solution. If you understand my request, please give me any formula which works.
 
I tried to do based Your given written text.
Please, reread my last four lines.
Hint: Sometimes eg 6.5 = 6.5 can be false too. Especially, if any of those 6.5 is result of formula.
 
I have removed some rows for simplicity, but your formula is not working as per attached.
 

Attachments

  • testsick-1.xlsx
    33.9 KB · Views: 2
It's working, but ...
as You've notice
... my formulas do not take care at all
# B-column [ RTW ]
# G-column [ Wait ]
... then there could be different results than Your L- or M-column values.
> You could try to solve Your expected results in parts; per different columns. <
 
Sorry! Could you look at the attached and see if Rows 13 and 17 are working? Remember my formula works except for these two rows.
 

Attachments

  • testsick-1.xlsx
    33.9 KB · Views: 4
Do You mean that those two ( RTW and Wait ) options can skip?
Formulas always works as those has written.
For my eyes, there are something with rows: 11, 14, 17, 24 & 30.
If Your formula gives something else than correct result - then it should modify.
 
You must log in or register to reply here.
Back
Top