Nested IF Statement comparing Dates, getting #VALUE error

**jrace** · 05-13-2013, 10:15 AM

I am having trouble with a nested IF statement. What I would like is to be able to enter a date in cell E1, and determine the number of vacation hours per month an employee would earn based on their years of service. Cells M18:M21 contain the date that the employee would move to the next level of earning and cells N18:N21 contains the number of hours they will earn. In E2 I have the following formula:
=IF(E1<=M18,N18,""),IF(AND(E1>=M19,E1<M20),N19,""),IF(AND(E1>=M20,E1<M21),N20,""),IF(E1>=M21,N21,"") and I keep getting a #VALUE error. I have attached the spreadsheet.
Leave Balance Example.xlsx

**Fotis1991** · 05-13-2013, 10:20 AM

Try using this way.

=IF(E1<=M18,N18,IF(AND(E1>=M19,E1<M20),N19,IF(AND(E1>=M20,E1<M21),N20,IF(E1>=M21,N21,""))))

This gives 10 as result.

**jrace** · 05-13-2013, 10:23 AM

It works for that date but if I put in 12/17/1990 which should return 20 it still returns 10-that's the trouble I have been having with the statement!

**Fotis1991** · 05-13-2013, 11:27 AM

I did't see what are you trying to do. I just fixed the syntax of your formula.

tO YOUR ISSUE NOW.

12/17/1990(the date in E1) is less than your date in M18(17/12/2016)==E1+1461.

So the formula, correctly returns 10 as the first if of the formula, says: =IF(E1<=M18;N18

..and in our case N18=10

**jrace** · 05-13-2013, 12:34 PM

ah, I see. Do I need to use OR(AND(), AND()) instead? and I'm not sure how that would look.

**Fotis1991** · 05-14-2013, 02:03 AM

Perhaps YES, perhaps NO. As i look the formula i SEE that always will gives you 10 as result....

This is because in M18, your formula is =E1+1461, in M19, =E1+3287 ETC.....So always your formula will gives a 10 as result.....because ALWAYS, E1 will be smaller than M18!!

What is this E1+1461? Why do you use this? Can you explain with words which is your goal??

**jrace** · 05-14-2013, 10:01 AM

I would like to have the spreadsheet calculate the number of vacation hours an employee will accrue monthly. The accruals occur monthly on the anniversary date of the employee's hire. From 1-3 years of service they earn 6.67 per month, from 4 to 8 years they earn 10, from from 9-13 years they earn 13.34 hours per month, from 14-18 years they earn 16.67 and over 19 years earns 20. I would like to be able to enter the hire date in E1 and have the hours per month populate in B2. The E1+1462 and so on gives the date the employee advances to the next level of earning.