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?

 

[Tetra201]

?

 

[bh24524]

On the asterisk issue: my original formula for column E did miss an important component. See if the following updated formula works as required:

=IF(AND(DATEDIF(C11,DATE(YEAR(TODAY()),12,31),"Y")>DATEDIF(C11,TODAY(),"Y"),OR(DATEDIF(C11,DATE(YEAR(TODAY()),12,31),"Y")=IF(C11-DATE(2016,6,5)>0,{1,3,8,14},{1,3,8,14,20,25}))),"*","")

Here's some findings:

For the highlighted rows, that would actually be one that would qualify for displaying the asterisk. It's odd, it seems to be working in the case of employees who will accrue their 1st week of vacation - it is putting an asterisk for them, but not those ones earning subsequent week numbers. I also tested the dates on ones with 1 week of vacation which do have asterisks, changing their dates to an earlier January date of last year that has already passed this year so like January 5th for example. For them, the asterisk disappeared which is great, so that is at least working for that.

I feel like we're definitely getting closer. Its just the puzzle of why it is doing it for the 1st week but not others.

 

[Tetra201]

In my test sheet, it works as intended. Please check formulas in your sheet(s).

 

[bh24524]

In my test sheet, it works as intended. Please check formulas in your sheet(s).
View attachment 30272

I have the formula in E18 for this one. I copied it so you could see. Had I missed something? Could the cell formatting of the date maybe be the issue? Mind you I don't know if this changes anything but the date that is there is actually a lookup from another tab. I wouldn't think that'd make a difference but not sure.

 

[jasonb75]

As you're not using office 365 you might need to array confirm it.

Select E18.
Press f2
Press Ctrl, Shift, and Enter together.
Then fill down.

 

[bh24524]

As you're not using office 365 you might need to array confirm it.

Select E18.
Press f2
Press Ctrl, Shift, and Enter together.
Then fill down.

ahhh YES!! that is exactly it. I forgot about ctrl + Shift + enter. Good catch! Now it is working as it should. Thank you both so very much for your assistance. I'm still not done but at least this hurdle has been overcome!

 

[jasonb75]

I forgot about ctrl + Shift + enter.

We all do now, the dynamic arrays in 365 are making us lazy

 

[bh24524]

We all do now, the dynamic arrays in 365 are making us lazy

Haha, understandable! So here's the next step, and this is smaller but on the far right of the sheet, there is a column for a special type of week of vacation. A week that an employee can pick to be half days. They are only eligible for this however if they have 3 or more weeks. I am looking to utilize a conditional format that will blacken the cell if they are not eligible or leave it uncolored if they are either eligible or the corresponding cell in D is blank. I did the following formula:

=IF(OR(ISBLANK(D11),D11<3),"no","yes")

- (I won't be using "no" or "yes" in the columns obviously - just did that as a test to make sure I could get it to work), but I am not sure how to rework it into conditional formatting. Would you happen to know what I'd need to shorten it to? Or would "isblank" even be right since there would be formulas in the cell?

 

[jasonb75]

I've only had a very quick glance over what you've asked, but from what I can see it should be as simple as

=OR(ISBLANK(D11),D11<3)

The way conditional formatting formulas work, any formula that returns TRUE or a numeric value other than 0 (positive, negative, date, time, etc) will apply the formatting.
Anything that returns FALSE, 0, any kind of text or error will be ignores and the conditional format will not be applied.

Once this bit is done, it might be a good idea to start another thread for any additional steps. Too many side questions can get messy if you have to refer back to an earlier part.

 

[bh24524]

I've only had a very quick glance over what you've asked, but from what I can see it should be as simple as

=OR(ISBLANK(D11),D11<3)

The way conditional formatting formulas work, any formula that returns TRUE or a numeric value other than 0 (positive, negative, date, time, etc) will apply the formatting.
Anything that returns FALSE, 0, any kind of text or error will be ignores and the conditional format will not be applied.

Once this bit is done, it might be a good idea to start another thread for any additional steps. Too many side questions can get messy if you have to refer back to an earlier part.

Okay no problem I can certain do that for additional steps. I think though that maybe ISblank is not the way to go. When used that, here was what I got:

Now I know that it is coloring the cell below the other 2 because the corresponding row in D isn't truly blank I guess since it has a formula. Is there another workaround?