date formula to determine amount of vacation weeks

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
224
Office Version
  1. 2013
  2. 2007
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?
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,969
Office Version
  1. 365
Platform
  1. Windows
Do years go exactly to the date or does it round to next fiscal period?

For example, if you had a 1/1/2020 in C11, would you count 1 year on 1/1/21 or 1/4/21 (4/1/21 using US dates)?
 

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
224
Office Version
  1. 2013
  2. 2007
Hi, thanks for the reply. They would go to the date. I might have another piece to add to this in a separate cell concerning if it happens to be current year that they will earn an additional week but we will come to that later.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,969
Office Version
  1. 365
Platform
  1. Windows
Assuming that C11 is hire date and that it is being calculated to the current date, try
Excel Formula:
=MAX(IF(C11>DATE(2015,6,5),4),LOOKUP(DATEDIF(C11,TODAY(),"Y"),{0,1,3,8,14,20,25},{0,1,2,3,4,5,6}))
 
Solution

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
224
Office Version
  1. 2013
  2. 2007

ADVERTISEMENT

Yep that works but unfortunately now I have another detail and apologize because I didn't even think of it til now(contract spec and all that). Is there any way that if they are earning an additional week in current year, that it could show that current year week number? Example is someone might be earning a 3rd week in October. This formula would only display a 2 as it is.

I'll just also go ahead an add the other item I mentioned might be a possibility. In regard to it showing that current year number, if the corresponding cell in column E could show an asterisk if that date in current year has not yet passed. So as above, if someone earns a 3rd week in October, Cell D would display a 3 for that vacation week amount and then cell E would have an asterisk next to it because we haven't come to October yet.

Would this somehow be able to be integrated?
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,969
Office Version
  1. 365
Platform
  1. Windows
I'm not sure that I follow.

Using your example, the formula will show 2 until October, then change to 3 once the contract anniversary date has passed.
Are you saying that you want it to show 3 for the whole year?
 

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
224
Office Version
  1. 2013
  2. 2007

ADVERTISEMENT

I'm not sure that I follow.

Using your example, the formula will show 2 until October, then change to 3 once the contract anniversary date has passed.
Are you saying that you want it to show 3 for the whole year?
That is correct. Basically what this is for, is this is part of a chart that changes employee numbers, names, and dates based on a department that is selected in an entirely different cell. These charts then get printed out and sent down to different warehouse shifts in February for the employees to pick their weeks of vacation for the year. Well, in Column D it should list the number of weeks they have available including if they will earn an additional week in the current year. 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 - that the 3rd one comes available at a later date in the current year and we've not yet come to that date. Once we come to that date of course, there would be no asterisk anymore in column E. Again I do apologize for having missed that detail in my initial explanation, I was only looking at time table of earnings but not thinking of the actual display itself when I gave the info. I hope that clarifies?


Something else might not be working right with this. I just switched it to a department that has someone who started on 6/28/2015 and its actually has a 4 in the number of weeks for him. 4 would be what he caps at, but he wouldn't earn that 4th week until 14 years from that date
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,969
Office Version
  1. 365
Platform
  1. Windows
Ok, bear with me on that one. Nothing reliable is coming to mind straight away, I might need to give it a bit of thought.

One thing I have just realised, I should have used MIN instead of MAX in the current formula. With MAX in the formula anyone who started after June 5th 2016 will get minimum 4 weeks, not be capped 4 weeks :eek:

I only noticed it when I added the formula to a blank sheet to do a couple of quick tests for the extra bit.
 

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
224
Office Version
  1. 2013
  2. 2007
Ok, bear with me on that one. Nothing reliable is coming to mind straight away, I might need to give it a bit of thought.

One thing I have just realised, I should have used MIN instead of MAX in the current formula. With MAX in the formula anyone who started after June 5th 2016 will get minimum 4 weeks, not be capped 4 weeks :eek:

I only noticed it when I added the formula to a blank sheet to do a couple of quick tests for the extra bit.
Absolutely no problem. :) I definitely thank you for your help on this! basically, we have a program that does already, but it is an old program that runs on the language of Fox Pro and we are getting upgrades to our Windows and we are told the program might possibly not be compatible with it. The support on the program is not very reliable, and so I am endeavoring to see if it can all be done thru Excel. Piece by piece I am getting closer but this is currently my hurdle lol.
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,969
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top