Sick Hours Accrued

[Taresa]

Hello! I need to create a formula that will calculate 1 hour of sick time for every 30 hours that the employee worked, with a running total. So in the spreadsheet that I provided it would be Total Hours/30. They have an annual maximum of 56.00 hours per year, but I can do that manually. As always, thank you very much, you guys have been invaluable helping me!

 

[earthworm]

Is this is what you want ?

Book1
	A	B	C	D
1	Date	Regular Hours	Sick Hours Accrued	Running Total
2	25/08/2021	64	2	2
3	26/08/2021	71	2	4
4	27/08/2021	29	0	4
5	28/08/2021	52	1	5
6	29/08/2021	78	2	7
7	30/08/2021	65	2	9
8	31/08/2021	24	0	9
9	01/09/2021	52	1	10
10	02/09/2021	35	1	11
11	03/09/2021	60	2	13
12	04/09/2021	46	1	14
13	05/09/2021	85	2	16
14	06/09/2021	35	1	17
Sheet1

Cell Formulas
Range	Formula
C2:C14	C2	=INT(B2/30)
D2:D14	D2	=SUM(C$2:C2)
A3:A14	A3	=A2+1

 

[Taresa]

Perfect! Thank you!

 

[Dave Patton]

Possible alternative based on "Total Hours/30"
You can also include the min(56, formula

T202109a.xlsm
	A	B	C	D	E	F
1	Date	Regular Hours	Sick Hours Accrued		Accrued	Cumulative accrued
2		1200	40		40	40
3	1-Jun-21	64	42		2	42
4	15-Jun-21	71	44		2	44
5	29-Jun-21	29	45		0	44
6	13-Jul-21	52	47		1	45
7	27-Jul-21	78	49		2	47
8	10-Aug-21	65	51		2	49
9	24-Aug-21	24	52		0	49
10	7-Sep-21	52	54		1	50
11	21-Sep-21	35	55		1	51
12	5-Oct-21	60	56		2	53
13	19-Oct-21	46	56		1	54
14	2-Nov-21	85	56		2	56
15	16-Nov-21	35	56		1	56
16
1d

Cell Formulas
Range	Formula
E3:E15	E3	=INT(B3/30)
F3:F15	F3	=MIN(56,SUM($E$2:E3))
C2:C15	C2	=MIN(56,INT(SUM($B$1:B2)/30))