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?

 

[bh24524]

Which version of excel do you need this to work with?

I notice that you have 2 versions shown on your profile, neither of which I have been able to get this working with yet.
I have a formula that looks like it works (not tested much yet), but it uses the LET and UNIQUE functions which are only available in the most recent updates of office 365. Looking at the complexity of that formula I think that older versions are going to be extremely difficult to make this work with, something as far back as 2007 might even be impossible.

=LET(x,DATE(YEAR(TODAY()),MONTH(C11),DAY(C11)),y,LOOKUP(DATEDIF(C11,x+{-1,1},"Y"),{0,1,3,8,14,20,25},{0,1,2,3,4,5,6}),z,y&IF(COUNT(UNIQUE(y))=2,{"","*"},""),INDEX(z,1+(TODAY()<x)))

and that is without the 4 week cap on more recent hires.

So at work, we have Excel 2013. that's what I'm using this for. They are also talking of upgrading our Office version but I don't know to what. My guess would be 2016 but I am not certain as of yet. That should be coming within the next 2 months rumor has it.

 

[jasonb75]

With that in mind I'll work on using 2013, I can't think of any additional features in 2016 that would help with this.
I've asked (or perhaps challenged would be more accurate) another member to take a look at it as well, hopefully we can come up with a solution between us.

 

[Tetra201]

... So one employee for example on these charts may have had a 2 in his vacations for last year on the chart and that would have been correct as that was all he had at that point. This year though since he will be earning a 3rd week say in October, the chart would show a "3" with an asterisk in column E and this basically designates that in total for the year, he will have 3 weeks of vacation but the asterisk flags them that not all 3 weeks are currently available...

See if the following formulas (based on jasonb75's Post #4) work for you:
Cell D11

=LOOKUP(DATEDIF(C11,DATE(YEAR(TODAY()),12,31),"Y"),{0,1,3,8,14,20,25},IF(C11>DATE(2016,6,5),{0,1,2,3,4,4,4},{0,1,2,3,4,5,6}))

Cell E11

=IF(DATEDIF(C11,DATE(YEAR(TODAY()),12,31),"Y")>DATEDIF(C11,TODAY(),"Y"),"*","")

 

[bh24524]

With that in mind I'll work on using 2013, I can't think of any additional features in 2016 that would help with this.
I've asked (or perhaps challenged would be more accurate) another member to take a look at it as well, hopefully we can come up with a solution between us.

See if the following formulas (based on jasonb75's Post #4) work for you:
Cell D11

=LOOKUP(DATEDIF(C11,DATE(YEAR(TODAY()),12,31),"Y"),{0,1,3,8,14,20,25},IF(C11>DATE(2016,6,5),{0,1,2,3,4,4,4},{0,1,2,3,4,5,6}))

Cell E11

=IF(DATEDIF(C11,DATE(YEAR(TODAY()),12,31),"Y")>DATEDIF(C11,TODAY(),"Y"),"*","")

Thank you both. I will test these tomorrow at work when I have the file in front of me. Keep you posted...

 

[jasonb75]

@Tetra201 Thanks for your help in resolving this one, I haven't evaluated it in excel but it looks like it should do what was asked for.

Think my downfall was trying to fit it all into a single formula.

 

[bh24524]

See if the following formulas (based on jasonb75's Post #4) work for you:
Cell D11

=LOOKUP(DATEDIF(C11,DATE(YEAR(TODAY()),12,31),"Y"),{0,1,3,8,14,20,25},IF(C11>DATE(2016,6,5),{0,1,2,3,4,4,4},{0,1,2,3,4,5,6}))

Cell E11

=IF(DATEDIF(C11,DATE(YEAR(TODAY()),12,31),"Y")>DATEDIF(C11,TODAY(),"Y"),"*","")

Alright so I tested it and both formulas actually not functioning as intended. I changed the shift codes to see several people and how it was treating them and here is what I'm seeing:

For this particular shot, it is only giving a maximum of 4 weeks but those particular employees were hired before June 5, 2016 so they would actually be entitled to 6 weeks. Also the asterisk is showing for all of the employees even the ones with two weeks and actually shouldn't because this current year was not a year that they earned an additional week of vacation.

In this next shot, this IS functioning correct, several of them will earn one week of vacation and there is an asterisk showing because we haven't come to the anniversary yet in the year:

Sorry for how annoying these nuances are in this scenario. Gotta love CBA's with the Unions...

 

[Tetra201]

On the "only giving a maximum of 4 weeks" issue: it looks like your dates are text strings, not numbers (you can test this via =ISNUMBER(C11)).
Here is a slightly modified formula that should take care of this:

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

On the asterisk issue: I will take a look at it later today.

 

[bh24524]

On the "only giving a maximum of 4 weeks" issue: it looks like your dates are text strings, not numbers (you can test this via =ISNUMBER(C11)).
Here is a slightly modified formula that should take care of this:

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

On the asterisk issue: I will take a look at it later today.

Thanks. That fixed the 4 week issue. I shifted departments again and found that it is also showing correctly if someone is accruing a 3rd week of vacation at a later date this year, so it seems that is fixed as well. Thanks for that!

 

[Tetra201]

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}))),"*","")

 

[jasonb75]

@Tetra201 I apologise for dumping my partially answered question on you, I was getting nowhere with practical ideas for the second part of the question and needed some fresh eyes to see through the trees and find the forest. I was going to take another look at the thread but thought better to step back rather than confuse things with 2 sets of answers running parallel.

Thanks for your support