DATEIF and IF statements

[neominds]

Greetings,

I am working an a timecard situation in which I need to account fo
accrued vacation days. But, I am running into some problems nesting th
IF statements. The employee is to accrue vacation time as follows:

1 week for 1 year worked
2 weeks for 2 years worked
3 weeks for 5-10 years worked
4 weeks for >10 years worked.

I can figure out how to nest them for the 1 yr., 2 yr., and 10 yr. bu
am having troubles figuring out the 5-10 yr. range. This is my formul
for the first 2 years.

=IF((AND((DATEDIF(F4,NOW(),"y")=1),F6="Accrual")),AE4*0.0192,(IF((AND((DATEDIF(F4,NOW(),"y")=2),F6="Accrual")),AE4*0.0384,0)))

F4 = the hire date

Anyone out there that can assist me? It's much appreciated...

Thanks,

Michae

 

[Harlan Grove]

neominds wrote...
....

1 week for 1 year worked
2 weeks for 2 years worked
3 weeks for 5-10 years worked
4 weeks for >10 years worked.

I can figure out how to nest them for the 1 yr., 2 yr., and 10 yr. but
am having troubles figuring out the 5-10 yr. range. This is my formula
for the first 2 years.

=IF((AND((DATEDIF(F4,NOW(),"y")=1),F6="Accrual")),AE4*0.0192,
(IF((AND((DATEDIF(F4,NOW(),"y")=2),F6="Accrual")),AE4*0.0384,0)))

F4 = the hire date

....

Ditch the unnecessary extra parentheses. Use a lookup.

=IF(F6="Accrual",AE4,0)*0.0192
*LOOKUP(DATEDIF(F4,NOW(),"Y"),{0;1;2;5;11},{0;1;2;3;4})