Sickness Tracker Help

**reespring2016** · 07-25-2016, 06:48 AM

Hi,
Please be gentle with me if this doesn't make sense; this is the first time posting in a forum like this and I'm not that tech savvy.

I need help to create a spread sheet that highlights when staff hit a trigger point in the absence policy. I have moderate knowledge of excel and I've searched online for a solution for this particular problem to no avail.
I don't have a draft spread sheet to supply a screen shot. But I have broken down exactly what I'm trying to do.

The policy rules/how I wish the spread sheet to work are as follows:
1. If staff member has more than 3x instances of absence within 12 weeks, highlight trigger point 1.
2. If staff member has more than 5x instances of absence within 52 weeks, highlight trigger point 2.
I also need to show if they hit the same trigger again within a 12 month period, e.g. if they were sick 3x in 12 weeks and then a 4th time in the next week, but haven't hit trigger point 2 because it hasn't been 5 instances of absence yet.

I think this could probably be solved by a long formula that adds number together number of rows of data on one hand and calculates number of days/weeks on another, but I'm so lost with how to accomplish this.

Also I would need it to demonstrate this example - a staff member sick twice in April-May. They have not hit trigger point 1. They are sick again in October. They have not hit a trigger for 1 as the time period between May and October is greater than 12 weeks. However if they're sick twice more for the year, trigger point 2 has been hit.

Many thanks for your contributions if you're able to help with the above... apologies that I don't at least have a draft to supply.

**AliGW** · 07-25-2016, 06:54 AM

You need to provide the basic workbook using the layout you are anticipating. You can't really expect anyone to build the whole thing from scratch for you, whether you are a newbie or not.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**reespring2016** · 07-25-2016, 07:10 AM

Thank you AliGW, I know it is a lot to ask but the example of what I have isn't even close to what I actually need. But I've attached my really rubbish example as advised.
Even if someone could just advise of a helpful formula for the dates, or whether it's conditional formatting I should be using, or whether this is just a ridiculous expectation; that would be great. I can work with advice and am not necessarily asking for someone to take their free time to build a workbook for me.

Thanks again.