SSP calculation to Look up between a range of dates with linked periods

I having been developing my sickness tool for quite a while, but just want to find a way to calculate ssp for people.

Essentially I have a complete list of data for all employees and a list is made in a named range called sickhist, which populates with that individuals sickness record.

Column 5 of this range has start date, 7 end date and 9 duration (which is workdays) column 10 is calendar days.

SSP is a rolling balance, so you get 28 weeks allowance but if you return to work for 8 weeks you go back to having 28 weeks allowance.

Another example is if you are off for 4 weeks you would have 24 weeks ssp left then you came back for 6 weeks and went off for anoth 10 weeks you would have 14 weeks ssp remaining.

I want to display the ssp expiry date. So the sickhist field will have 12 months data in it I need to find a way to sum the durations for the sickness for any sick within the last 8 weeks, and any linked periods who have a period of sickness within the last 8 weeks.

A linked period is where a person goes off sick again within 8 weeks from returning to work.

I will put together an example spreadsheet.

cheers,

Andy

1.

Please Login or Register  to view this content.

into Q18 and copied upwards should do it.

What if you had several sequential 3 day sick leaves? If the ssp should not deduct with them then that's okay. Things might get tricky (more ifs I guess :D) if from the 4th day - the first day of second absence - ssp ought to be deducted while 4 being the smallest possible number of deducted days.

2. Hmmm, well, as I put a seed of 28 weeks into Q19, maybe the easiest way to get this done is to find out the remaining ssp days at the beginning of the year and for e.g. put that number into R19 and change Q19 into 28*7-R19?

Ok I have managed to get it to calculate with the assumption there is no linked period going over 12 months sickness history.

It takes in to account the 8 weeks for a fresh SSP allowance

It only includes periods greater than 3 days

It can handle linked periods

The main problems I have now is that the date ranges may be seperate but may be 1 period of sickness. So basically this makes a problem if the durations aren't more than 3 days but are continuous.

For example:

Please Login or Register  to view this content.

As you can see all of these are 3 days or less, so with the current calculation method, no ssp days will be used, however in reality these are linked as there should be 7 days ssp used.

I didn't quite get that S col formula so I made a little addition into previously modified file.

It's not even near ready-for-use but this is the way I approached the problem. As you see in the file I made additional columns (again) and what I was trying to start is to check whether there are those less than four day periods. If it's possible that there might be like x number of one day periods one after another (who would even address sick leaves like that?), then the monitoring needs to be expanded into x number of periods.

However if I was to done this I probably would expand that into 3 periods and modify myself those chained short term absences. Hope this helps a bit.

i have had another go, I could do with a second opinion to whether it's right or not. It now looks if the next working day after the end date of the previous sick line is equal to the start day of that sickness line and includes it in the cumulative sick duration if it is.

The only thing I can think of that is not right is, this rule ignores the greater than 3 day rule if it is a linked period, regardless of the fact that the greater than 3 day rule applies to the total of the linked period.