Spreadsheet: 726449d1617247362-employee-service-milestones-and-vacation-entitlement-datedif-and-index-and-match-vacation_entitlement_report_for-sharing
Sheet: ee info

 |  |  | 2022-03-31 00:00:00
Seniority [NAME] | Current Annual Vacation Entitlement (hours) | Job Category | Years of Service (Yrs.) | Years of Service (Yrs & Mths) | From 1-Apr-22 | Mar-22 hours
2015-10-17 00:00:00 | 140 | Prof/Tech - 4-Prof/Tech - 4
2008-11-24 00:00:00 | 175 | Admin - 4-Admin - 4
2010-11-22 00:00:00 | 210 | Mgt - 3-Mgt - 3
2010-05-19 00:00:00 | 175 | Prof/Tech - 3-Prof/Tech - 3
2003-08-26 00:00:00 | 175 | Admin - 3-Admin - 3
2021-04-01 00:00:00 |  | Prof/Tech - 4-Prof/Tech - 4
2007-01-26 00:00:00 | 175 | Admin - 4-Admin - 4
2005-09-19 00:00:00 | 175 | Admin - 4-Admin - 4
2008-01-23 00:00:00 | 175 | Admin - 3-Admin - 3
2003-03-03 00:00:00 | 175 | Admin - 4-Admin - 4
2010-10-04 00:00:00 | 175 | Admin - 4-Admin - 4
2011-05-10 00:00:00 | 140 | Admin - 4-Admin - 4
2012-07-09 00:00:00 | 140 | Prof/Tech - 2-Prof/Tech - 2
2014-11-17 00:00:00 | 140 | Admin - 3-Admin - 3
2007-07-23 00:00:00 | 210 | Exec - 2-Exec - 2
2014-10-23 00:00:00 | 175 | Admin - 4-Admin - 4
2015-07-20 00:00:00 | 140 | Prof/Tech - 2-Prof/Tech - 2
2019-05-06 00:00:00 | 105 | Admin - 4-Admin - 4
2016-07-04 00:00:00 | 175 | Exec - 2-Exec - 2
2019-08-26 00:00:00 | 105 | Prof/Tech - 3-Prof/Tech - 3
2019-07-29 00:00:00 | 105 | Admin - 3-Admin - 3
2019-07-29 00:00:00 | 105 | Mgt - 1-Mgt - 1
2019-08-12 00:00:00 | 105 | Admin - 3-Admin - 3
2019-07-22 00:00:00 |  | Entry-Entry
2014-03-03 00:00:00 | 140 | Mgt - 2-Mgt - 2
2016-08-12 00:00:00 | 105 | Admin - 3-Admin - 3
2019-08-12 00:00:00 | 105 | Admin - 3-Admin - 3
2010-05-03 00:00:00 | 175 | Admin - 3-Admin - 3
2019-08-26 00:00:00 | 105 | Admin - 3-Admin - 3
2004-02-05 00:00:00 | 175 | Admin - 3-Admin - 3
2004-09-27 00:00:00 | 175 | Prof/Tech - 5-Prof/Tech - 5
2003-09-08 00:00:00 | 175 | Prof/Tech - 4-Prof/Tech - 4
2004-03-01 00:00:00 | 175 | Mgt - 3-Mgt - 3
2004-05-03 00:00:00 | 175 | Prof/Tech - 4-Prof/Tech - 4
2004-06-29 00:00:00 | 175 | Admin - 4-Admin - 4
1998-01-19 00:00:00 | 210 | Admin - 3-Admin - 3
2000-11-14 00:00:00 | 210 | Admin - 2-Admin - 2
2008-01-23 00:00:00 | 175 | Admin - 4-Admin - 4
2007-10-16 00:00:00 | 210 | Admin - 4-Admin - 4
2007-11-26 00:00:00 | 175 | Prof/Tech - 3-Prof/Tech - 3
1998-09-28 00:00:00 | 210 | Prof/Tech - 4-Prof/Tech - 4
2008-02-04 00:00:00 | 175 | Admin - 4-Admin - 4
1993-05-17 00:00:00 | 210 | Admin - 3-Admin - 3
2008-07-07 00:00:00 | 175 | Mgt - 3-Mgt - 3
2006-11-06 00:00:00 | 175 | Prof/Tech - 4-Prof/Tech - 4
2005-06-20 00:00:00 | 175 | Mgt - 4-Mgt - 4
2017-09-05 00:00:00 | 105 | Admin - 4-Admin - 4
2007-07-01 00:00:00 | 210 | Exec - 2-Exec - 2
2007-02-14 00:00:00 | 175 | Prof/Tech - 2-Prof/Tech - 2
2004-03-08 00:00:00 | 175 | Prof/Tech - 4-Prof/Tech - 4
2006-11-06 00:00:00 | 175 | Prof/Tech - 4-Prof/Tech - 4
2014-09-12 00:00:00 | 140 | Admin - 3-Admin - 3
2014-09-12 00:00:00 | 140 | Prof/Tech - 2-Prof/Tech - 2
2014-11-17 00:00:00 | 140 | Admin - 3-Admin - 3
2014-11-17 00:00:00 | 140 | Admin - 3-Admin - 3
2005-02-14 00:00:00 | 175 | Admin - 2-Admin - 2
2005-02-28 00:00:00 | 175 | Prof/Tech - 4-Prof/Tech - 4
2006-10-16 00:00:00 | 175 | Admin - 2-Admin - 2
2009-06-01 00:00:00 | 175 | Prof/Tech - 5-Prof/Tech - 5
2008-01-02 00:00:00 | 175 | Admin - 4-Admin - 4
2014-05-12 00:00:00 | 140 | Admin - 4-Admin - 4
2009-11-09 00:00:00 | 175 | Admin - 4-Admin - 4
2010-01-18 00:00:00 | 175 | Mgt - 3-Mgt - 3
2010-02-24 00:00:00 | 175 | Prof/Tech - 2-Prof/Tech - 2
2010-06-21 00:00:00 | 175 | Admin - 3-Admin - 3
2010-10-12 00:00:00 | 175 | Prof/Tech - 4-Prof/Tech - 4
2011-01-04 00:00:00 | 175 | Admin - 4-Admin - 4
2011-02-07 00:00:00 | 175 | Admin - 4-Admin - 4
2011-04-04 00:00:00 | 210 | Exec - 2-Exec - 2
2011-04-26 00:00:00 | 140 | Prof/Tech - 2-Prof/Tech - 2
2011-06-17 00:00:00 | 140 | Prof/Tech - 2-Prof/Tech - 2
2018-10-15 00:00:00 | 105 | Admin - 2-Admin - 2
2017-07-10 00:00:00 | 105 | Prof/Tech - 4-Prof/Tech - 4
2017-07-10 00:00:00 | 105 | Prof/Tech - 4-Prof/Tech - 4
1991-09-20 00:00:00 | 210 | Admin - 4-Admin - 4
2017-01-23 00:00:00 | 105 | Prof/Tech - 2-Prof/Tech - 2
2019-01-14 00:00:00 | 105 | Admin - 4-Admin - 4
2017-02-13 00:00:00 | 140 | Mgt - 4-Mgt - 4
2017-03-03 00:00:00 | 105 | Admin - 3-Admin - 3 |  |  |  |  |  |  |  | 1 month of accrual at 140; 11 months at 105
2021-03-01 00:00:00 | 105 | Prof/Tech - 4-Prof/Tech - 4
2016-06-20 00:00:00 | 105 | Prof/Tech - 3-Prof/Tech - 3
2013-04-01 00:00:00 | 140 | Prof/Tech - 4-Prof/Tech - 4
2013-03-22 00:00:00 | 140 | Admin - 4-Admin - 4
2013-04-08 00:00:00 | 140 | Admin - 4-Admin - 4
2013-04-08 00:00:00 | 140 | Prof/Tech - 4-Prof/Tech - 4
2013-05-27 00:00:00 | 175 | Mgt - 3-Mgt - 3
2012-04-30 00:00:00 | 140 | Admin - 1-Admin - 1
2012-01-09 00:00:00 | 210 | Exec - 2-Exec - 2
2011-12-13 00:00:00 | 140 | Prof/Tech - 4-Prof/Tech - 4
2012-10-12 00:00:00 | 140 | Admin - 3-Admin - 3
2012-02-28 00:00:00 | 140 | Prof/Tech - 4-Prof/Tech - 4
2012-04-23 00:00:00 | 140 | Prof/Tech - 4-Prof/Tech - 4
2016-08-15 00:00:00 | 105 | Mgt - 1-Mgt - 1
2013-04-15 00:00:00 | 210 | Prof/Tech - 6-Prof/Tech - 6
2012-04-10 00:00:00 | 140 | Prof/Tech - 4-Prof/Tech - 4
2012-07-16 00:00:00 | 140 | Mgt - 2-Mgt - 2
2013-08-19 00:00:00 | 140 | Admin - 4-Admin - 4
2012-04-10 00:00:00 | 140 | Admin - 4-Admin - 4
2012-05-11 00:00:00 | 140 | Admin - 3-Admin - 3
2011-12-05 00:00:00 | 140 | Mgt - 1-Mgt - 1
2012-07-16 00:00:00 | 140 | Prof/Tech - 4-Prof/Tech - 4
2012-05-22 00:00:00 | 140 | Mgt - 1-Mgt - 1
2012-12-10 00:00:00 | 140 | Mgt - 1-Mgt - 1
2012-07-24 00:00:00 | 140 | Prof/Tech - 4-Prof/Tech - 4
2014-03-17 00:00:00 | 175 | Mgt - 3-Mgt - 3
2018-09-04 00:00:00 | 140 | Prof/Tech - 6-Prof/Tech - 6
2014-03-28 00:00:00 | 140 | Mgt - 3-Mgt - 3
2014-04-07 00:00:00 | 140 | Prof/Tech - 3-Prof/Tech - 3
2014-04-01 00:00:00 | 140 | Prof/Tech - 4-Prof/Tech - 4
2017-03-13 00:00:00 | 105 | Admin - 3-Admin - 3 |  |  |  |  |  |  |  | 1 month of accrual at 140; 11 months at 105
2019-04-12 00:00:00 | 105 | Admin - 3-Admin - 3
2014-04-14 00:00:00 | 175 | Prof/Tech - 4-Prof/Tech - 4
2014-05-15 00:00:00 | 140 | Prof/Tech - 4-Prof/Tech - 4
2014-06-02 00:00:00 | 140 | Prof/Tech - 4-Prof/Tech - 4
2018-07-03 00:00:00 | 105 | Prof/Tech - 3-Prof/Tech - 3
2018-02-12 00:00:00 | 105 | Admin - 3-Admin - 3
2018-03-26 00:00:00 | 105 | Admin - 3-Admin - 3
2018-03-29 00:00:00 | 105 | Admin - 3-Admin - 3
2018-03-29 00:00:00 | 105 | Admin - 3-Admin - 3
2015-05-19 00:00:00 | 175 | Mgt - 4-Mgt - 4
2017-04-18 00:00:00 | 105 | Prof/Tech - 2-Prof/Tech - 2
2017-04-10 00:00:00 | 105 | Prof/Tech - 3-Prof/Tech - 3
2017-04-25 00:00:00 | 105 | Prof/Tech - 2-Prof/Tech - 2
2016-11-21 00:00:00 | 210 | Exec - 2-Exec - 2
2019-04-12 00:00:00 | 105 | Admin - 3-Admin - 3
2016-07-11 00:00:00 | 105 | Prof/Tech - 3-Prof/Tech - 3
2016-08-12 00:00:00 | 105 | Admin - 4-Admin - 4
2016-01-25 00:00:00 | 175 | Exec - 1-Exec - 1
2019-07-02 00:00:00 | 105 | Prof/Tech - 3-Prof/Tech - 3
2016-04-18 00:00:00 | 105 | Prof/Tech - 4-Prof/Tech - 4 |  |  |  |  |  |  |  | 11.5 months at 140; .5 at 105
2016-04-11 00:00:00 | 105 | Prof/Tech - 3-Prof/Tech - 3 |  |  |  |  |  |  |  | full fiscal at 140
2016-03-14 00:00:00 | 140 | Admin - 4-Admin - 4
2018-04-30 00:00:00 | 105 | Prof/Tech - 4-Prof/Tech - 4
2016-08-08 00:00:00 | 140 | Mgt - 3-Mgt - 3
2018-05-22 00:00:00 | 105 | Prof/Tech - 3-Prof/Tech - 3
2015-07-20 00:00:00 | 140 | Admin - 4-Admin - 4
2015-08-10 00:00:00 | 140 | Prof/Tech - 3-Prof/Tech - 3
2015-09-14 00:00:00 | 140 | Prof/Tech - 3-Prof/Tech - 3
2015-10-05 00:00:00 | 140 | Mgt - 1-Mgt - 1
2015-03-02 00:00:00 | 210 | Exec - 2-Exec - 2
2015-03-09 00:00:00 | 140 | Admin - 3-Admin - 3
2015-06-08 00:00:00 | 140 | Prof/Tech - 3-Prof/Tech - 3
2017-08-21 00:00:00 | 140 | Mgt - 2-Mgt - 2
2017-08-28 00:00:00 | 105 | Admin - 3-Admin - 3
2017-10-30 00:00:00 | 105 | Prof/Tech - 4-Prof/Tech - 4
2018-07-03 00:00:00 | 105 | Prof/Tech - 3-Prof/Tech - 3
2018-06-25 00:00:00 | 105 | Prof/Tech - 3-Prof/Tech - 3
2018-10-01 00:00:00 | 210 | Other-Other
2018-09-24 00:00:00 | 105 | Mgt - 1-Mgt - 1
2018-08-03 00:00:00 | 105 | Admin - 3-Admin - 3
2018-01-02 00:00:00 | 105 | Admin - 4-Admin - 4
2018-09-17 00:00:00 | 105 | Mgt - 2-Mgt - 2
2018-10-22 00:00:00 | 140 | Mgt - 3-Mgt - 3
2018-11-05 00:00:00 | 105 | Prof/Tech - 3-Prof/Tech - 3
2018-11-26 00:00:00 | 105 | Prof/Tech - 2-Prof/Tech - 2
2018-11-26 00:00:00 | 105 | Prof/Tech - 2-Prof/Tech - 2
2018-12-10 00:00:00 | 105 | Admin - 4-Admin - 4
2019-01-09 00:00:00 | 105 | Prof/Tech - 3-Prof/Tech - 3
2019-01-21 00:00:00 | 210 | Exec - 2-Exec - 2
2014-04-17 00:00:00 | 140 | Prof/Tech - 3-Prof/Tech - 3
2019-04-15 00:00:00 | 105 | Admin - 3-Admin - 3
2019-01-07 00:00:00 | 105 | Mgt - 2-Mgt - 2
2018-05-07 00:00:00 | 105 | Admin - 3-Admin - 3
2019-06-03 00:00:00 | 105 | Admin - 3-Admin - 3
2019-09-23 00:00:00 | 105 | Admin - 2-Admin - 2
2019-09-23 00:00:00 | 105 | Prof/Tech - 4-Prof/Tech - 4
2019-11-04 00:00:00 | 105 | Admin - 3-Admin - 3
2019-11-25 00:00:00 | 105 | Admin - 3-Admin - 3
2011-08-22 00:00:00 | 140 | Prof/Tech - 6-Prof/Tech - 6
2020-01-06 00:00:00 | 105 | Mgt - 1-Mgt - 1
2020-01-13 00:00:00 | 105 | Prof/Tech - 3-Prof/Tech - 3
2021-03-01 00:00:00 | 105 | Prof/Tech - 2-Prof/Tech - 2
2020-01-13 00:00:00 | 105 | Admin - 4-Admin - 4
2016-03-07 00:00:00 | 140 | Mgt - 2-Mgt - 2
2020-09-14 00:00:00 | 105 | Prof/Tech - 3-Prof/Tech - 3
2020-03-23 00:00:00 | 105 | Prof/Tech - 4-Prof/Tech - 4
2011-07-08 00:00:00 | 140 | Mgt - 1-Mgt - 1
2016-06-27 00:00:00 | 105 | Admin - 4-Admin - 4
2020-11-30 00:00:00 | 105 | Admin - 3-Admin - 3
2020-11-30 00:00:00 | 105 | Admin - 3-Admin - 3
2020-11-30 00:00:00 | 105 | Prof/Tech - 2-Prof/Tech - 2
2021-03-01 00:00:00 | 105 | Prof/Tech - 2-Prof/Tech - 2
2014-04-17 00:00:00 | 140 | Prof/Tech - 3-Prof/Tech - 3
2021-03-15 00:00:00 | 105 | Prof/Tech - 4-Prof/Tech - 4
Re: Employee Service Milestones & Vacation Entitlement - Datedif & Index & Match
Hello
More information/learning of how vacation accruals are calculated for the next fiscal have come about. Things have got a whole lot more complicated!
Adding to my information above:
1. employees reaching service milestones of 5, 10 and 20 years in next year's fiscal (April 1, 2021 - March 31, 2022) get hours per the vacation entitlement chart based on their job category. There is a new category of management levels 3 and 4. This wasn't captured in my prior examples. This is another criteria I have to account for.
2. the next criteria is at what point in the month they joined the org ("Seniority Start Date" column). This only applies to employees reaching a service milestone. If they joined between the 1st and the 15th of the month they get credit (accrue) 1 full month of vacation. If they joined between the 16th and the 31st of the month they get a 1/2 month of accrual. Again this criteria is only for those employees who are reaching a service milestone of 5, 10, or 20. We have no 20s.
3. all other non-milestone reaching employees get hours based on their job category
4. the 4th and final layer complicating all this is, is that existing hours ("current entitlement") may trump all of the above as the employee may already be at the [NAME]/above the usual guidelines for their job category which is why you'll see I have the [NAME] formula.
Given the multiple conditions/criteria is it possible to complete all the above with 1 formula in one cell? Would Index and Match be best? Or if and?
Thank you.