Formula or other to determine amount of vacation weeks

[bh24524]

Hello, so I am looking to see if a formula or other could assign an asterisk to when someone will be due a new vacation in the year based on how long they have been with the company. There are several conditions though, if they are Union employees or non-union, and in the case of Union depending on if they were hired after a certain year. I will explain those below the schedule of when employees actually earn their vacation.

How many years from hire date (UNION)
1 year = 1 week of vacation
3 years = 2 weeks vacation
8 years = 3 weeks vacation
14 years = 4 Weeks vacation
20 years = 5 weeks vacation
25 years = 6 weeks vacation

Here are the special conditions mentioned above for Union employees:

If they were hired before 2002, they can get up to 6 weeks
If they were hired on or after 2002 but before 2016, they can get up to 5 weeks
If they were hired in 2016 or after, they can only get up to 4 weeks

Here is the schedule of vacations for Non-Union employees

6 months = 1 week vacation
1 year = 2 weeks vacation
5 years = 3 weeks vacation however, they do not get this extra week until January 1st of what would be their 6th year
14 years = 4 weeks vacation however, they do not get this extra week until January 1st of what would be their 15th year
19 years = 5 weeks vacation however, they do not get this extra week until January 1st of what would be their 20th year

So in our vacation software which is unfortunately very out of date, the way it works is it does calculate those amount of weeks due as mentioned. We run a report which will generate charts by department that has their total amount of weeks they are entitled to and a blank box next to it where the asterisk mentioned above will go. However, the way it is programmed it will only give an asterisk in that separate box to those who are to get ONE week in that year. It does not work for any of the years after that.

So that is where maybe a formula could come in I hope. Bear in mind, Union charts are separated from Non-union charts so it may end up that I would have 2 formulas. The number of weeks they earn starts in D11 and goes down from there by how many employees are on that chart. The asterisk would start in D12 and go down from there depending on how many employees are on that chart. So based on the information and criteria I have given above, is there some formula or other I can utilize in the excel charts that are generated that will put the asterisk in the cells if they are getting an additional week that year?

 

[oldbrewer]

How many years from hire date (UNION)
1 year = 1 week of vacation											unionyrs	vac
3 years = 2 weeks vacation												0	0
8 years = 3 weeks vacation												2	1
14 years = 4 Weeks vacation											2	1
20 years = 5 weeks vacation											2	1
25 years = 6 weeks vacation											2	1
														3	5
Here are the special conditions mentioned above for Union employees:								17	6
														99	6
If they were hired before 2002, they can get up to 6 weeks
If they were hired on or after 2002 but before 2016, they can get up to 5 weeks
If they were hired in 2016 or after, they can only get up to 4 weeks								nonunionyrs	vac
														0	0
Here is the schedule of vacations for Non-Union employees									0.5	1
														1	1
6 months = 1 week vacation											5	3
1 year = 2 weeks vacation												14	4
5 years = 3 weeks vacation however, they do not get this extra week until January 1st of what would be their 6th year			19	5
14 years = 4 weeks vacation however, they do not get this extra week until January 1st of what would be their 15th year			99	5
19 years = 5 weeks vacation however, they do not get this extra week until January 1st of what would be their 20th year
			JOINED	SERVICE	UNIONMAX	NONUMAX	vacallowance
	name1	U	1990	28	6	5	6	####		with 2 tables defining allowances
	name2	NONU	1990	28	6	5	5
	name3	U	1994	24	5	5	6			formula in #####
	name4	NONU	1994	24	5	5	5
	name5	U	1998	20	5	5	6			=IF(C24="U",VLOOKUP(E24,uniontable,2),VLOOKUP(E24,nonuniontable,2))
	name6	NONU	1998	20	5	5	5
	name7	U	2000	18	4	4	6
	name8	NONU	2000	18	4	4	4			you can adjust the values in the lookup tables
	name9	U	2003	15	4	4	5
	name10	NONU	2003	15	4	4	4
	name11	U	2005	13	4	3	5
	name12	NONU	2005	13	4	3	3
	name13	U	2009	9	4	3	5
	name14	NONU	2009	9	4	3	3
	name15	U	2011	7	3	3	5
	name16	NONU	2011	7	3	3	3
	name17	U	2014	4	3	3	5
	name18	NONU	2014	4	3	3	1
	name19	U	2016	2	2	3	1
	name20	NONU	2016	2	2	3	1
	name21	U	2017	1	1	2	0
	name22	NONU	2017	1	1	2	1
	name23	U	2018	0	0	0	0
	name24	NONU	2018	0	0	0	0

<colgroup><col span="5"><col><col><col><col span="3"><col><col span="2"><col><col span="3"></colgroup><tbody>
</tbody>

 

[Oaktree]

There is a hidden function within Excel called DATEDIF that could be perfect for this. I say "hidden" because you won't find it in Excel help, but you can use it like a normal function.

If column A has type ("Union" or "Non-Union") and column B has start dates (Excel dates, not just years), try this in C2:

=IF(A2="Union",MIN(VLOOKUP(DATEDIF(B2,TODAY(),"y"),{1,1;3,2;8,3;14,4;20,5;25,6},2),CHOOSE(MATCH(YEAR(B2),{1900,2002,2016}),6,5,4)),VLOOKUP(DATEDIF(B2,DATE(YEAR(TODAY()),1,1),"m"),{6,1;12,2;60,3;168,4;228,5},2))

Here, DATEDIF(B2,TODAY(),"y") will return the integer value of full years between the start date and today. Similarly, DATEDIF(B2,DATE(YEAR(TODAY()),1,1),"m") will return the integer value of full months between the start date and Jan 1 of the current year.

I'd recommend putting the two arrays in tables on a different sheet instead of listing them within the { }s of the formula above, but this illustrates that you actually *can* do this calculation within one formula if you wanted to.

 

[bh24524]

There is a hidden function within Excel called DATEDIF that could be perfect for this. I say "hidden" because you won't find it in Excel help, but you can use it like a normal function.

If column A has type ("Union" or "Non-Union") and column B has start dates (Excel dates, not just years), try this in C2:

=IF(A2="Union",MIN(VLOOKUP(DATEDIF(B2,TODAY(),"y"),{1,1;3,2;8,3;14,4;20,5;25,6},2),CHOOSE(MATCH(YEAR(B2),{1900,2002,2016}),6,5,4)),VLOOKUP(DATEDIF(B2,DATE(YEAR(TODAY()),1,1),"m"),{6,1;12,2;60,3;168,4;228,5},2))

Here, DATEDIF(B2,TODAY(),"y") will return the integer value of full years between the start date and today. Similarly, DATEDIF(B2,DATE(YEAR(TODAY()),1,1),"m") will return the integer value of full months between the start date and Jan 1 of the current year.

I'd recommend putting the two arrays in tables on a different sheet instead of listing them within the { }s of the formula above, but this illustrates that you actually *can* do this calculation within one formula if you wanted to.

To be honest, I don't know that either of these solutions would work. Let me clarify further, there are many departments which cause the excel charts to be generated. There are many charts from the union and there are many different ones from the non-union. These charts get sent down for employees to put X's in the dates across the boxes. I'm really just looking for a function for the asterisk that I mentioned, one that I can just copy to all those charts. I know that seems tedious but it will actually save a lot more time than me just looking at every person on every chart to determine if they are getting a vacation that year. Is there perhaps a different solution? As I said if we need one formula for teh union charts and one for the non-union charts, I can live with that.

 

[oldbrewer]

In every company that I ever worked for, holidays were booked by submitting a holiday request for one or more days. If approved, an "X" was placed in a box for each day in a spreadsheet maintained by HR that was read only to employees. That spreadsheet had the annual entitlement and days remaining for that holiday year.My solution allows you to calculate that annual holiday entitlement.

 

[Oaktree]

I do think the principles of my previous post would allow you to get the asterisk you're looking for.

Instead of just using this portion of the formula from my previous post to return the number of vacation weeks an employee receives:

=MIN(VLOOKUP(DATEDIF(B2,TODAY(),"y"),{1,1;3,2;8,3;14,4;20,5;25,6},2),CHOOSE(MATCH(YEAR(B2),{1900,2002,2016}),6,5,4))

consider changing the TODAY() part to the relevant end dates.

For example, if you want to return an asterisk only where people have more vacation weeks this year than they did last year, you could use something like:

=IF(MIN(VLOOKUP(DATEDIF(B2,TODAY(),"y"),{1,1;3,2;8,3;14,4;20,5;25,6},2),CHOOSE(MATCH(YEAR(B2),{1900,2002,2016}),6,5,4))>MIN(VLOOKUP(DATEDIF(B2,EDATE(TODAY(),-12),"y"),{1,1;3,2;8,3;14,4;20,5;25,6},2),CHOOSE(MATCH(YEAR(B2),{1900,2002,2016}),6,5,4)),"*","")

Not sure if you're looking to specifically use today's date or if you should instead use Jan 1 of this year and last year... regardless, I wouldn't dismiss this approach just yet.