I have searched high and low and I cannot find an anwer to my dilemma and I
have tried a multitude of examples from various sources. My company accrues
PTO on bi-weekly basis as follows:

1 to 5 Years 4.62 Hours Biweekly
6 to 15 years 6.15 Hours Biweekly
15+ years 7.70

On one of the anniversary years, the employee changes the accrual of the
vacation on the paycheck of his anniversary. Example Employee Hire Date is
4/5/04, the employee will accrue 4.62 hours per paycheck until the paycheck
of 4/10/09 where he will start accruing at 6.15 for the remainder of the
paychecks for that year.

My Columns are
A1 Name
B1 Date of Hire
C1 Anniversary Date

I have tried HLookup to an array, I have tried IF formula's but nothing
gives me the right results. Is anyone out there able to help?????
--
Regards - Mariela

Mariela wrote:
I have searched high and low and I cannot find an anwer to my dilemma and I
have tried a multitude of examples from various sources. My company accrues
PTO on bi-weekly basis as follows:

1 to 5 Years 4.62 Hours Biweekly
6 to 15 years 6.15 Hours Biweekly
15+ years 7.70

On one of the anniversary years, the employee changes the accrual of the
vacation on the paycheck of his anniversary. Example Employee Hire Date is
4/5/04, the employee will accrue 4.62 hours per paycheck until the paycheck
of 4/10/09 where he will start accruing at 6.15 for the remainder of the
paychecks for that year.

My Columns are
A1 Name
B1 Date of Hire
C1 Anniversary Date

I have tried HLookup to an array, I have tried IF formula's but nothing
gives me the right results. Is anyone out there able to help?????

Set up your accrual definitions somewhere like as follows (it is
important to include the zero row). Select this range and give it the
name ACCRUALS (Ctrl+F3 to define a named range).

col1 col2
0 0
1 4.62
6 6.15
15 7.70

This should return the correct accrual amount on the check date in column C:

=VLOOKUP(DATEDIF(B1,C1,"Y"),ACCRUALS,2,TRUE)

Thank you smartin for your quick response. This gives me the accrual amount
that should be calculated on their anniversary date (column C), which is
good, I think this gets me one step closer to what I need.

However, I need a formula that will calculate the accrual for the year
taking into account the change of accrual as of the anniversary date.
--
Regards - Mariela


"smartin" wrote:

Mariela wrote:
I have searched high and low and I cannot find an anwer to my dilemma and I
have tried a multitude of examples from various sources. My company accrues
PTO on bi-weekly basis as follows:

1 to 5 Years 4.62 Hours Biweekly
6 to 15 years 6.15 Hours Biweekly
15+ years 7.70

On one of the anniversary years, the employee changes the accrual of the
vacation on the paycheck of his anniversary. Example Employee Hire Date is
4/5/04, the employee will accrue 4.62 hours per paycheck until the paycheck
of 4/10/09 where he will start accruing at 6.15 for the remainder of the
paychecks for that year.

My Columns are
A1 Name
B1 Date of Hire
C1 Anniversary Date

I have tried HLookup to an array, I have tried IF formula's but nothing
gives me the right results. Is anyone out there able to help?????

Set up your accrual definitions somewhere like as follows (it is
important to include the zero row). Select this range and give it the
name ACCRUALS (Ctrl+F3 to define a named range).

col1 col2
0 0
1 4.62
6 6.15
15 7.70

This should return the correct accrual amount on the check date in column C:

=VLOOKUP(DATEDIF(B1,C1,"Y"),ACCRUALS,2,TRUE)

The way I would approach it would be to incrementalize (is that a word?)
everything first: figure the accruals by pay period by employee in a big
table, then gross up results by employee by year. It occurred to me that
you should separate check date from anniversary date.

Hope this makes sense... let me know if not.

Mariela wrote:
Thank you smartin for your quick response. This gives me the accrual amount
that should be calculated on their anniversary date (column C), which is
good, I think this gets me one step closer to what I need.

However, I need a formula that will calculate the accrual for the year
taking into account the change of accrual as of the anniversary date.

Thanks smartin, with your suggestion yesterday and breaking it down to a few
coulmns adn VLookup, I was able to get the results I needed. Thanks for your
help!
--
Regards - Mariela


"smartin" wrote:

The way I would approach it would be to incrementalize (is that a word?)
everything first: figure the accruals by pay period by employee in a big
table, then gross up results by employee by year. It occurred to me that
you should separate check date from anniversary date.

Hope this makes sense... let me know if not.

Mariela wrote:
Thank you smartin for your quick response. This gives me the accrual amount
that should be calculated on their anniversary date (column C), which is
good, I think this gets me one step closer to what I need.

However, I need a formula that will calculate the accrual for the year
taking into account the change of accrual as of the anniversary date.