DATEIF and IF statements
 

Greetings,

I am working an a timecard situation in which I need to account for
accrued vacation days. But, I am running into some problems nesting the
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. 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

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

Thanks,

Michael


--
neominds
------------------------------------------------------------------------
neominds's Profile: http://www.excelforum.com/member.php...o&userid=25204
View this thread: http://www.excelforum.com/showthread...hreadid=394778

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})