Calculating Accrued Vacation

[Jim Fox]

Hello,
I'm new to Excel, so please bear with me.
I want to calculate the accrual of vacation time, which accrues every 14 days at a rate of 4 hours per pay period (the pay period is every 14 days). I have two columns of data; column A contains pay period 1 thru 26, and col B contains the pay period ending date (26 of those, 01/06/02 thru 12/22/02). I want to determine the current pay period based on the date, and post that pay period number (such as pay period 6)to a specific cell. Can anyone tell me how to go about this?...Hope this was not too confusing.

 

[Anne Troy]

I guess it is a bit confusing. I can't figure out whether you want to calculate the vacation accrual days or if you want to put the pay period in a cell.

Tell us where you want to calculate the accrual days or tell us what the dates covered by pay period 1.

I think that ought to help us sort it out.

 

[Jim Fox]

Thanks for the response.
I actually want to "examine" the list of pay period dates (in Excel), determine which is the current pay period, then calculate the vacation accrual from that information for each employee.The first pay period end date was 01/06/02, the second was 01/20/02, and continues every 14 days until it reaches 12/22/02 which is the last pay period end date in 2002. Each employee will accrue vacation at a different rate dependent upon how may years of service he/she has with the company.

 

[Dave Patton]

You could create a table of rates and
edit a formula that captures relevant information and completes the calc.

accrual starts A1
User Defined Function Weeks calcs # of weeks
(see post I made a few days ago).

Could probably use countif or ...
A4 person's name
D1 date person started
rRates range of rates
Lookup determines relevant column
Vlookup determines relevant rate

weeks*rate = amount

=Weeks(A1,TODAY())*VLOOKUP(A4,rRates,LOOKUP(DATEDIF(D1,TODAY(),"y"),{0,5,10;0,2,3}),1)