# IF Formula not working...

1. ## IF Formula not working...

Hi folks, im new to this forum, so apologies if im not posting this in the right place I am attempting to write an IF formula that will calculate staff holiday entitlements for me. Entitlements are based on date of joining company (B5), contracted hours (B3) and number of Bank Holidays in the year (B7). I've attempted the following formula:

=IF(B5<=DATE(1992,3,31),SUM(((B3/5)*29))+((B3/5)*B7)),IF(AND(B5>=DATE(1992,4,1),B5<=DATE(2002,3,31),SUM(((B3/5)*27))+((B3/5)*B7)),IF(AND(B5>=DATE(2002,4,1),B5<=DATE(2007,3,31),SUM(((B3/5)*25))+((B3/5)*B7)),IF(AND(B5>=DATE(2007,4,1),B5<=DATE(2009,4,31),SUM(((B3/5)*24))+((B3/5)*B7)),IF(B5>=DATE(2009,5,1),SUM(((B3/5)*21))+((B3/5)*B7),"ZERO"))))

This formula returns a VALUE! error message.

Can anyone help me correct this formula? any advice, much approciated

Thanks!  Register To Reply

2. ## Re: IF Formula not working...

Each of your AND() scenarios is missing its closing paren....

Maybe this:

=IF(B5<=DATE(1992,3,31),((B3/5)*29)+((B3/5)*B7),
IF(AND(B5>=DATE(1992,4,1),B5<=DATE(2002,3,31)),((B3/5)*27)+((B3/5)*B7),
IF(AND(B5>=DATE(2002,4,1),B5<=DATE(2007,3,31)),((B3/5)*25)+((B3/5)*B7),
IF(AND(B5>=DATE(2007,4,1),B5<=DATE(2009,4,31)),((B3/5)*24)+((B3/5)*B7),
IF(B5>=DATE(2009,5,1),((B3/5)*21)+((B3/5)*B7),"ZERO")))))  Register To Reply

3. ## Re: IF Formula not working...

Thank you!!  Register To Reply

4. ## Re: IF Formula not working...

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.  Register To Reply