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?
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?
Last edited:
