Calculating vacation accruals based on actual hours worked

[Heather Young]

I need some help...I am trying to create a formula that will calculate vacation hours accrued for actual hours worked in a week? I created a formula for personal time but that one is based on a calendar year and I need the vacation based on anniversary dates for example:

Employee A has been with the company 5 yrs with his anniversary date as 09/21/2017 in which his vacation will reset back to 80 hrs on 09/21/2017. Now that is based on 40 hrs work weeks but the issue we are having is that some employees are working between 30-34 hrs a week but still will get the full 80 hrs once their anniversary date hits.

Based on our type of business we can not convert to a calendar year so I need to create a formula that I can enter the number of hours worked each week to give me a weekly accrual balance.

Does anyone have any suggestions???

 

[CalcSux78]

When you accrue 80 hrs per year (based on a 40 hr work week), your total hrs worked is 2080 hrs and equates out to 1/26th hrs earned per hour worked.

Excel 2010
	A	B	C
7	Hrs/Week	Tot Hrs	Accrual
8	40	2080	80
9	34	1768	68
10	30	1560	60
Sheet1

Cell Formulas
Range	Formula
B8	=A8*52
C8	=B8/26

I would also look into what happens when overtime is worked within a week, or if sick time/vacation time is used. If you factor in vacation time not earning vacation time, you would have 1/25th of an hour earned per work hour. Sick time wold further reduce the calculation.

 

[Heather Young]

Well you made it look simply

Thanks