Rolling Sickness

**OxJ** · 01-06-2017, 12:46 PM

Has anyone created a twelve month rolling sickness spreadsheet that would be prepared to share?

**FDibbins** · 01-06-2017, 12:53 PM

Hi, welcome to the forum

I guess if anyone has actually done this, they would know what you mean

For the rest of us, I think we need lots more detail on what you are trying to do, what does your data look like, what do you expect, etc

**OxJ** · 01-06-2017, 01:04 PM

I saw a similar question in this forum although it was some time ago so thought I would join and try and get the spreadsheet but its obviously not as easy as that. I need to establish an individuals absence from a certain date to current sickness absence to check when they hit the 4 weeks date as the employee would not then receive firm's sick pay. For example an employee has gone on long term sick on 16th Dec 2016 so I looked back at 2016 and 2015 attendance records to see when the 4 weeks expired. The employee had odd days off in 2016 (nothing in 2015) and will hit the 4 weeks date at the end of January. This is simple to work out by just looking at the attendance records. However if the employee returns and then goes off again for example on 1st March 2017 I have to look at the same data but the date changes from the 16th Dec working back to the 1st March working back and this could happen again with further absences. Not sure if this explains what I am trying to do or not.

**FDibbins** · 01-06-2017, 01:13 PM

Perhaps this will get you started?

	A	B	C	D
1	Date
2	12/1/2016	x	11/7/2016	8
3	12/3/2016
4	12/5/2016	x
5	12/7/2016
6	12/9/2016
7	12/11/2016	x
8	12/13/2016
9	12/15/2016
10	12/17/2016
11	12/19/2016	x
12	12/21/2016
13	12/23/2016
14	12/25/2016
15	12/27/2016	x
16	12/29/2016	x
17	12/31/2016	x
18	1/2/2017	x
19	1/4/2017
20	1/6/2017
21	1/8/2017	x
22	1/10/2017
23	1/12/2017	x
24	1/14/2017	x
25	1/16/2017	x
26	1/18/2017
27	1/20/2017
28	1/22/2017	x
29	1/24/2017
30	1/26/2017	x
31	1/28/2017	x
32	1/30/2017	x

C2=TODAY()-60
this would be your start date. To keep the sample small, I just -60 from TODAY(), but if you wanted to go back a year, that would be
=EDATE(TODAY(),-12)

Then to pull the data...
D2=COUNTIFS($B$2:$B$32,"x",$A$2:$A$32,"<="&TODAY(),$A$2:$A$32,">="&$C$2)

Let me know how you make out?

**AliGW** · 01-06-2017, 01:35 PM

Originally Posted by OxJ

Has anyone created a twelve month rolling sickness spreadsheet that would be prepared to share?

It's not a condition I'd heard of until today ...

Sorry - couldn't resist!

Perhaps if you attach here the spreadsheet you downloaded and explain how you need to adapt it.

**newdoverman** · 01-06-2017, 04:07 PM

Here is an example that you can play with. I have entered the dates to 31/12/2020 in column A and have entered a 1 beside each date that represents a sick day off. I entered 1/1/2015 to start with so that the formula had a year back to start with. This formula is entered in C3 and filled down the column. It calculates the time taken within a 1 year period on a "rolling" basis.

Formula:

Please Login or Register  to view this content.

This formula starts off calculating the start date to be considered in column A ($A$2:$A$1829>=EDATE(A3,-12). The end date is the date that you are looking at. For example if you were looking at Jan 2 2017 the starting date would have been calculated by the above formula and the value in column C is the total for the rolling year to that date.
The end date is calculated with ($A$2:$A$1829<=A3). Both of these dates are then multiplied by the values in the corresponding cells in column C. All of this within the SUMPRODUCT function will give a total for the time period.

**OxJ** · 01-11-2017, 08:38 AM

Thank you for your help. I've received another spreadsheet so will try them both out and see which fits my requirements. I am self taught in excel so I need to get my head around some of the formula's.

**OxJ** · 01-11-2017, 08:38 AM

Like you I am a self taught enthusiast of excel and always learning. I have received two examples so need to get my head around the formula's and see which will be suitable.