date formula to determine amount of vacation weeks

[bh24524]

I am looking for help on a formula that will look up a given seniority date contained in cell C11. The formula would be in D11. I want it to determine the number of vacation weeks a warehouse employee gets based on their hire date and length of time with the company. They get vacations based on the following schedule:

1 year = 1 week
3 years = 2 weeks
8 years = 3 weeks
14 years = 4 weeks
*20 years = 5 weeks
*25 years = 6 weeks

*All employees hired after June 5, 2016 cap at 4 weeks. Can this somehow be incorporated into the formula?

 

[jasonb75]

Sorry, my bad for not reading your post properly. ISBLANK is formatting the bottom row because the corresponding cell in column D is blank.
If you want the format to apply when D contains a number greater than 3, then try

=AND(D11<>"",D11<3)

With the formulas in the cells you only really need

=D11<3

, the other part acts as a failsafe if D is blank with no formula.

 

[bh24524]

Sorry, my bad for not reading your post properly. ISBLANK is formatting the bottom row because the corresponding cell in column D is blank.
If you want the format to apply when D contains a number greater than 3, then try

=AND(D11<>"",D11<3)

With the formulas in the cells you only really need

=D11<3

, the other part acts as a failsafe if D is blank with no formula.

Perfect! People call me the Excel Wizard here at work, but you guys are clearly the Excel Arch-Wizards :P Thank you very much for both of your help on this.

 

[jasonb75]

but you guys are clearly the Excel Arch-Wizards :P

@Tetra201 is, I just pretend to be one in my spare time ?
Many excel users are masters in their own field, it's that point when you have to step into the unknown and use unfamiliar methods that you realise how much you don't know.
If I see a question that relates to any kind of accounting / finance functions I tend to run a bit quick