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!
Bookmarks