IF Formula Calculating Employee Benefits Packages

[Justinian]

I need an formula to calculate employee vacation packages. The requirements are:

- 12 months of service = 40 hours vacation
- 24 months of service = 80 hours vacation
- 60 months of service = 120 hours vacation
- 180 months of service = 160 hours vacation
- 240 months of service = 200 hours vacation

I have the employees 2014 anniversary date but I cannot figure out how to sturcture my IF formula. So basically, if an employee was hired 1/8/06, I need the formula to spit out the appropriate vacation hour balance based on the chart above, which would be 120 hours because eight years of service falls between 60 months and 180 months.

Any ideas?

 

[Justinian]

I mean this:

=IF(AND(X24>=X20+365*1,X24<X20+365*2),40,IF(AND(X24>=X20+365*2,X24<X20+365*5),80,IF(AND(X24>=X20+365*1,X24<X20+365*15),120,IF(AND(X24>=X20+365*1,X24<X20+365*20),160,IF(X24>=X20+365*20,200,"")))))

 

[Justinian]

Geez, it will not let me post the complete formula but it sounds like you already know what it is.