Calculating Accrued Vacation

Jim Fox · Mar 28th, 2002, 06:07 PM

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 · Mar 28th, 2002, 08:13 PM

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 · Mar 29th, 2002, 04:46 PM

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 · Mar 29th, 2002, 05:49 PM

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)

Mar 28th, 2002, 08:13 PM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	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. __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 28th, 2002, 06:07 PM	#1
Jim Fox New Member Join Date: Mar 2002 Location: Bel Air, MD, USA Posts: 2	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.

Mar 29th, 2002, 04:46 PM	#3
Jim Fox New Member Join Date: Mar 2002 Location: Bel Air, MD, USA Posts: 2	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.

Mar 29th, 2002, 05:49 PM	#4
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	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 weeksrate = amount =Weeks(A1,TODAY())VLOOKUP(A4,rRates,LOOKUP(DATEDIF(D1,TODAY(),"y"),{0,5,10;0,2,3}),1)