Sickness Spreadsheet with triggers

[numptynuts]

Good afternoon.

I have just started working for the NHS in Rehabilitation Services. I have asked if they have a sickness tracker and surprisingly, they don't.

The sickness triggers for short term sickness is four periods of absence in a rolling 12 month period OR
10 working days absence in a rolling 12 months period.

I'm trying to create a spreadsheet that will record the day they called in sick and then allow for me to record the day they report back in for for work. I want it then to reflect if the staff member has hit any of the triggers. Triggers will be pro rated accordingly for part-time employees or those working compressed hours.

I have about 25 staff working under me.

Any help appreciated in order to get me started.

Many Thanks

[davsth]

Well you need to come up with what you might like it to look like with some sample data, see yellow banner
Is all sickness just in a few columns eg start end name

How do we know what is a working day (do they work over weekends?)

If I was sick say 12-dec- 19-dec 19, 1-mar-20, 1-jun-20, and 18-20 dec 20 would this be 4 occurrences in a rolling year or 3?


Is covid related sickness in the spreadsheet? as I believe it is excluded from sickness calculations

if you give a few examples in a sheet people will help you, at the moment you ae asking for an answer without giving the details you require

[numptynuts]

Hi There.

Apologies for the lack of clarity. It would need to take Covid into account. Having only just started I am using the company's sickness policy as a guide and it just states "Four periods of absence in a rolling 12 month period OR 10 working days absence in a rolling 12 months period".

I have attached a simple spreadsheet and would like some information on how to get a formula to automatically work out if they have triggered. I would like to be column H.
They currently only work weekdays, Monday-Friday.

[davsth]

simply this could be wrong as just as you don't know exact details it is impossible for us to know them as well


You will also get people triggering for the 5th time who have already triggered for the 4th


=COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,">"&(C2-365)) will tell you the number of periods of sickness from the start dates (this may or may not be correct)


sumifs($f$2:F2,$A$2:A2,A2,$B$2:B2,B2,$C$2:C2,">"&(C2-365)) will tell you the days sick although days should be working days which they are not in your example, you have put a total of 7 days but this includes weekends try =NETWORKDAYS(C2,E2)


your example does not contain data and the outcomes you would wish, just one line of information, so none of the scenarios you would wish to highlight


An if statement could flag the instances where each scenario breaches. However without actual values and rules that you know to be true, it is difficult to progress, as speculating on the answers wastes evrybodies time. Personally I would not wnat to highlight an employee for too higher sickness if my calculations were based on false asumptions, would you?

Learn your rules, explain them in english create date in the table which higlights evry occassion you wish to flag, and some better formulas can be written

[numptynuts]

Ok. well thanks for trying to help. I do appreciate the advice.

[davsth]

It can be done, its not too difficult. But if you were flagging this with no spreadsheet you would be applying some rules/policies. All the spreadsheet will do is apply the same rules. but if you can not articulate the rules to apply its hard for the formula to be written. why not come back when you discover them!