Sick leave accrual

[FPNWOPS]

Hello.

I need assistance building a simple spreadsheet to track sick leave that has a 1 hour for every 40 worked accrual rate. This needs to be a rolling balance set-up so if a PT employee works 25 hours one week and 30 the next, the accrual will be triggered by the 40 hour mark and start the next 40 hour clock with the remaining 15 hours of that 2 week example. The time can be used in 1/4 hour increments and a balance has to be shown to the employee monthly. We pay once a month for hours worked and can track the sick leave hours in line with that period structure.

Thanks.

JC

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date Hired		Period End Date			Hours worked	Bal not = 40 muiltiple	Vac hrs accrued	Sick leave hrs accrued	Vac hrs used	Sick leave hrs used	Vac hrs balance	Sick leave hrs balance
2	30-May-17		31-Jan-18			100		0	2.5	8	1	-8	1.5
2b

Cell Formulas
Range	Formula
I2	=((C2-A2)>90)*SUM($F$1:F2)/40-SUM($I$1:I1)
M2	=N(M1)+I2-K2

 

[FPNWOPS]

Yeah!!! So there's no need to post a formula in cell G2 (the plan was to hide the cell) to keep track hours that were not a multiple of 40 in the period? Essentially, the calc happens on a rolling total of hours so that for each 40, there's 1 earned. If 20 hours are left over in the period end (2 multiples of 40 used to calc 2 hours) where 100 were worked, that 20 hours stays in he mix to be added onto the next period; and so on.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Date Hired		Period End Date			Hours worked	Bal not = 40 muiltiple	Vac hrs accrued	Sick leave hrs accrued
2	30-May-17		31-Jan-18			20		0	0
3			28-Feb-18			15			0
4			31-Mar-18			45			2
2b

Cell Formulas
Range	Formula
I2	=(($C$2-$A$2)>90)*INT(SUM($F$1:F2)/40-SUM($I$1:I1))
I3	=(($C$2-$A$2)>90)*INT(SUM($F$1:F3)/40-SUM($I$1:I2))
I4	=(($C$2-$A$2)>90)*INT(SUM($F$1:F4)/40-SUM($I$1:I3))

Edit If hours are less than 40, this version shows 0.