Monitoring sick pay over a rolling 12 month period - with two levels of pay

[HRJon]

Hi All

I've been staring at this for two days and I'm stuck, hope you can help me.

The company I work for looks at sickness for a rolling 12 month period. In that period, we pay 100% pay for the first 65 days of absence, 50% pay for the subsequent 65 days of absence, and then 0 after that (based on a full time employee, pro-rated for part time).

So we have someone off on long term sick at the moment, who exhausted their 100% entitlement, and are now on 50%. However, as the year rolls on the 100% payment they had 12 months ago will "drop off" meaning they're entitled to 100% payment again. Basically I need a sheet which will tell us on each day whether they should be receiving 100%, 50% or 0% pay based on the previous 12 months, and can switch between the three.

Like I said, I've tried myself but everything I've tried ties me up in circular references. Any help would be most appreciated!!

HR Jon

 

[HRJon]

These are the dates of the individual if it helps (full time, 5 days per week)

START DATE END DATE TOTAL

12/08/2022​	19/08/2022​	6.0​
31/08/2022​	31/08/2022​	1.0​
16/09/2022​	16/09/2022​	1.0​
13/10/2022​	14/10/2022​	2.0​
11/11/2022​	14/11/2022​	2.0​
21/12/2022​	06/01/2023​	13.0​
09/05/2023​	21/06/2023​	32.0​
25/07/2023​	Ongoing

 

[Anonymous1378]

Something like this?

Book1
	A	B	C	D	E
2	66		12-Aug-22	19-Aug-22	6
3	50%		31-Aug-22	31-Aug-22	1
4			16-Sep-22	16-Sep-22	1
5			13-Oct-22	14-Oct-22	2
6			11-Nov-22	14-Nov-22	2
7			21-Dec-22	06-Jan-23	13
8			09-May-23	21-Jun-23	32
9			25-Jul-23	Ongoing
Sheet3

Cell Formulas
Range	Formula
A2	A2	=LET(rolldate,TODAY(),_a,WORKDAY(--rolldate,SEQUENCE(ABS(NETWORKDAYS(rolldate,EDATE(rolldate,-12)+1)),,0,-1)),COUNT(IF((_a>=TRANSPOSE(C2:C9))*(_a<=TRANSPOSE(D2:D9)),_a,"")))
A3	A3	=INT(130/A2)/2
E2:E8	E2	=NETWORKDAYS(C2,D2)

 

[HRJon]

Something like this?

Book1
	A	B	C	D	E
2	66		12-Aug-22	19-Aug-22	6
3	50%		31-Aug-22	31-Aug-22	1
4			16-Sep-22	16-Sep-22	1
5			13-Oct-22	14-Oct-22	2
6			11-Nov-22	14-Nov-22	2
7			21-Dec-22	06-Jan-23	13
8			09-May-23	21-Jun-23	32
9			25-Jul-23	Ongoing
Sheet3

Cell Formulas
Range	Formula
A2	A2	=LET(rolldate,TODAY(),_a,WORKDAY(--rolldate,SEQUENCE(ABS(NETWORKDAYS(rolldate,EDATE(rolldate,-12)+1)),,0,-1)),COUNT(IF((_a>=TRANSPOSE(C2:C9))*(_a<=TRANSPOSE(D2:D9)),_a,"")))
A3	A3	=INT(130/A2)/2
E2:E8	E2	=NETWORKDAYS(C2,D2)

Thank you, that's helpful and I think we're on the right track!

I was hoping for a list of days which could be used to show day by day what they should be paid. Something along the lines of this;

		Enter Sick Here	Total Sick Days YTD	Paid?	Rate
Fri	12/08/2022​	1​	1​	Yes	100%
Mon	15/08/2022​	1​	2​	Yes	100%
Tue	16/08/2022​	1​	3​	Yes	100%
Wed	17/08/2022​	1​	4​	Yes	100%
Thu	18/08/2022​	1​	5​	Yes	100%
Fri	19/08/2022​	1​	6​	Yes	100%

We'd enter the first day of sick in the period into the cell with the red text (in this case 12/08/22), then a list of working days would create below going out maybe 12-24 months. We'd enter an indicator in the "Enter Sick Here" column to show they're absent (in this case 1), the paid column will then work out if they're paid for that day or not, and the rate column would work out the rate of pay. The idea being that once we get to 12 months, they'd start receiving 100% pay again.

Apologies, I'd had a very long day and was very tired when I made my OP, so I probably didn't explain myself very well!

 

[Anonymous1378]

That sounds significantly easier than what I just did, what's the issue?

Book1
	A	B	C	D	E
1	08-Dec-22		0	Yes	100%
2	09-Dec-22	1	1	Yes	100%
3	12-Dec-22		1	Yes	100%
4	13-Dec-22	1	2	Yes	100%
5	14-Dec-22		2	Yes	100%
6	15-Dec-22	1	3	Yes	100%
7	16-Dec-22		3	Yes	100%
8	19-Dec-22		3	Yes	100%
9	20-Dec-22	1	4	Yes	100%
10	21-Dec-22		4	Yes	100%
11	22-Dec-22		4	Yes	100%
12	23-Dec-22	1	5	Yes	100%
13	26-Dec-22		5	Yes	100%
14	27-Dec-22		5	Yes	100%
15	28-Dec-22		5	Yes	100%
16	29-Dec-22	1	6	Yes	100%
17	30-Dec-22		6	Yes	100%
18	02-Jan-23	1	7	Yes	100%
19	03-Jan-23		7	Yes	100%
20	04-Jan-23	1	8	Yes	100%
21	05-Jan-23		8	Yes	100%
22	06-Jan-23		8	Yes	100%
23	09-Jan-23	1	9	Yes	100%
24	10-Jan-23	1	10	Yes	100%
25	11-Jan-23		10	Yes	100%
26	12-Jan-23	1	11	Yes	100%
27	13-Jan-23	1	12	Yes	100%
28	16-Jan-23		12	Yes	100%
29	17-Jan-23		12	Yes	100%
30	18-Jan-23	1	13	Yes	100%
31	19-Jan-23		13	Yes	100%
32	20-Jan-23	1	14	Yes	100%
33	23-Jan-23		14	Yes	100%
34	24-Jan-23	1	15	Yes	100%
35	25-Jan-23		15	Yes	100%
36	26-Jan-23		15	Yes	100%
37	27-Jan-23	1	16	Yes	100%
38	30-Jan-23	1	17	Yes	100%
39	31-Jan-23		17	Yes	100%
40	01-Feb-23	1	18	Yes	100%
41	02-Feb-23	1	19	Yes	100%
42	03-Feb-23	1	20	Yes	100%
43	06-Feb-23	1	21	Yes	100%
44	07-Feb-23	1	22	Yes	100%
45	08-Feb-23		22	Yes	100%
46	09-Feb-23	1	23	Yes	100%
47	10-Feb-23	1	24	Yes	100%
48	13-Feb-23	1	25	Yes	100%
49	14-Feb-23	1	26	Yes	100%
50	15-Feb-23	1	27	Yes	100%
51	16-Feb-23	1	28	Yes	100%
52	17-Feb-23	1	29	Yes	100%
53	20-Feb-23	1	30	Yes	100%
54	21-Feb-23		30	Yes	100%
55	22-Feb-23	1	31	Yes	100%
56	23-Feb-23	1	32	Yes	100%
57	24-Feb-23	1	33	Yes	100%
58	27-Feb-23		33	Yes	100%
59	28-Feb-23	1	34	Yes	100%
60	01-Mar-23		34	Yes	100%
61	02-Mar-23	1	35	Yes	100%
62	03-Mar-23		35	Yes	100%
63	06-Mar-23	1	36	Yes	100%
64	07-Mar-23		36	Yes	100%
65	08-Mar-23		36	Yes	100%
66	09-Mar-23	1	37	Yes	100%
67	10-Mar-23	1	38	Yes	100%
68	13-Mar-23		38	Yes	100%
69	14-Mar-23	1	39	Yes	100%
70	15-Mar-23	1	40	Yes	100%
71	16-Mar-23	1	41	Yes	100%
72	17-Mar-23	1	42	Yes	100%
73	20-Mar-23	1	43	Yes	100%
74	21-Mar-23		43	Yes	100%
75	22-Mar-23	1	44	Yes	100%
76	23-Mar-23	1	45	Yes	100%
77	24-Mar-23	1	46	Yes	100%
78	27-Mar-23	1	47	Yes	100%
79	28-Mar-23	1	48	Yes	100%
80	29-Mar-23	1	49	Yes	100%
81	30-Mar-23	1	50	Yes	100%
82	31-Mar-23	1	51	Yes	100%
83	03-Apr-23		51	Yes	100%
84	04-Apr-23	1	52	Yes	100%
85	05-Apr-23	1	53	Yes	100%
86	06-Apr-23	1	54	Yes	100%
87	07-Apr-23		54	Yes	100%
88	10-Apr-23	1	55	Yes	100%
89	11-Apr-23	1	56	Yes	100%
90	12-Apr-23	1	57	Yes	100%
91	13-Apr-23	1	58	Yes	100%
92	14-Apr-23	1	59	Yes	100%
93	17-Apr-23	1	60	Yes	100%
94	18-Apr-23	1	61	Yes	100%
95	19-Apr-23	1	62	Yes	100%
96	20-Apr-23	1	63	Yes	100%
97	21-Apr-23	1	64	Yes	100%
98	24-Apr-23	1	65	Yes	100%
99	25-Apr-23	1	66	Yes	50%
100	26-Apr-23	1	67	Yes	50%
Sheet1

Cell Formulas
Range	Formula
C1:C100	C1	=SUMIFS(B$1:B$522,A$1:A$522,">="&EDATE(A1,-12),A$1:A$522,"<="&A1)
D1:D100	D1	=IF(C1<=130,"Yes","No")
E1:E100	E1	=IFERROR(MIN(INT(130/C1)/2,1),1)