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