Spreadsheet: 727030d1617669764-generate-range-of-dates-from-a-given-period-anniversary-and-intermediate-dates-between-hire-and-termination-archvanarl-
Sheet: Sheet1

Date Range |  |  |  |  | From | To |  |  | From | To |  |  | From | To |  | *In every 3 years from the starting date "FROM", there will be 1 Step is added. *There is no step before Year 2000. *The effectivity of step will be updated and reflected starting year 2000 (shown in Test 1 and Test 2 table) *The Start date and end dates of "FROM" and "TO" must follow the date range table. However, the start date and the end end can be change starting year 2000 if there's an additional step for every 3 years from the starting date "FROM"
1981-03-01 00:00:00 | 1981-06-30 00:00:00 |  |  |  | 1985-09-16 00:00:00 | 2007-11-06 00:00:00 |  |  | 1999-08-09 00:00:00 | 2012-04-07 00:00:00 |  |  | 2005-06-03 00:00:00 | 2018-03-27 00:00:00
1981-07-01 00:00:00 | 1981-12-31 00:00:00 |  |  | STEP | test 1 |  |  | STEP | test 2 |  |  | STEP | test 3
1982-01-01 00:00:00 | 1984-04-30 00:00:00 |  |  |  | From | to |  |  | From | to |  |  | From | to
1984-05-01 00:00:00 | 1984-06-30 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1985-09-16 00:00:00 | 1985-12-31 00:00:00 |  |  |  | 1999-08-09 00:00:00 | 1999-12-31 00:00:00 |  |  | 1 | 2005-06-03 00:00:00 | 2007-06-30 00:00:00
1984-07-01 00:00:00 | 1984-12-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1986-01-01 00:00:00 | 1986-06-30 00:00:00 |  |  | 1 | 2000-01-01 00:00:00 | 2001-06-30 00:00:00 |  |  | 1 | 2007-07-01 00:00:00 | 2008-06-02 00:00:00
1985-01-01 00:00:00 | 1985-12-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1986-07-01 00:00:00 | 1987-02-28 00:00:00 |  |  | 1 | 2001-07-01 00:00:00 | 2002-08-08 00:00:00 |  |  | 2 | 2008-06-03 00:00:00 | 2008-06-30 00:00:00
1986-01-01 00:00:00 | 1986-06-30 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1987-03-01 00:00:00 | 1987-12-31 00:00:00 |  |  | 2 | 2002-08-09 00:00:00 | 2005-08-08 00:00:00 |  |  | 2 | 2008-07-01 00:00:00 | 2009-06-30 00:00:00
1986-07-01 00:00:00 | 1987-02-28 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1988-01-01 00:00:00 | 1989-06-30 00:00:00 |  |  | 3 | 2005-08-09 00:00:00 | 2007-06-30 00:00:00 |  |  | 2 | 2009-07-01 00:00:00 | 2010-06-23 00:00:00
1987-03-01 00:00:00 | 1987-12-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | *maximum step is 8, if it reaches 8 then it will remain 8 even if there's another 3 years. |  |  |  |  |  |  | 1989-07-01 00:00:00 | 1993-12-31 00:00:00 |  |  | 3 | 2008-07-01 00:00:00 | 2008-08-08 00:00:00 |  |  | 2 | 2010-06-24 00:00:00 | 2011-05-31 00:00:00
1988-01-01 00:00:00 | 1989-06-30 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1994-01-01 00:00:00 | 1994-12-31 00:00:00 |  |  | 4 | 2008-08-09 00:00:00 | 2009-06-30 00:00:00 |  |  | 2 | 2011-06-01 00:00:00 | 2011-06-02 00:00:00
1989-07-01 00:00:00 | 1993-12-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1995-01-01 00:00:00 | 1995-12-31 00:00:00 |  |  | 4 | 2009-07-01 00:00:00 | 2010-06-23 00:00:00 |  |  | 3 | 2011-06-03 00:00:00 | 2012-05-31 00:00:00
1994-01-01 00:00:00 | 1994-12-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1996-01-01 00:00:00 | 1996-12-31 00:00:00 |  |  | 4 | 2010-06-24 00:00:00 | 2011-05-31 00:00:00 |  |  | 3 | 2012-06-01 00:00:00 | 2014-06-02 00:00:00
1995-01-01 00:00:00 | 1995-12-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1997-01-01 00:00:00 | 1997-10-31 00:00:00 |  |  | 4 | 2011-06-01 00:00:00 | 2011-08-08 00:00:00 |  |  | 4 | 2014-06-03 00:00:00 | 2015-12-31 00:00:00
1996-01-01 00:00:00 | 1996-12-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1997-11-01 00:00:00 | 1999-12-31 00:00:00 |  |  | 5 | 2011-08-09 00:00:00 | 2012-04-07 00:00:00 |  |  | 4 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00
1997-01-01 00:00:00 | 1997-10-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2000-01-01 00:00:00 | 2000-09-15 00:00:00 |  |  |  |  |  |  |  | 4 | 2017-01-01 00:00:00 | 2017-06-02 00:00:00
1997-11-01 00:00:00 | 1999-12-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2000-09-16 00:00:00 | 2001-06-30 00:00:00 |  |  |  |  |  |  |  | 5 | 2017-06-03 00:00:00 | 2017-12-31 00:00:00
2000-01-01 00:00:00 | 2001-06-30 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | What I need to realize is to have a formula to automatically generate thsese dates and step in each test table illustrated based on the "From" and "To". Please do not get confused, these three tables are the same and must have the same formula. |  |  |  |  |  |  | 2001-07-01 00:00:00 | 2003-09-15 00:00:00 |  |  |  |  |  |  |  | 5 | 2018-01-01 00:00:00 | 2018-03-27 00:00:00
2001-07-01 00:00:00 | 2007-06-30 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2003-09-16 00:00:00 | 2006-09-15 00:00:00
2007-07-01 00:00:00 | 2008-06-30 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2006-09-16 00:00:00 | 2007-06-30 00:00:00
2008-07-01 00:00:00 | 2009-06-30 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2007-07-01 00:00:00 | 2007-11-06 00:00:00
2009-07-01 00:00:00 | 2010-06-23 00:00:00
2010-06-24 00:00:00 | 2011-05-31 00:00:00
2011-06-01 00:00:00 | 2012-05-31 00:00:00
2012-06-01 00:00:00 | 2015-12-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | I have some formulas, including one for a helper column, for the Test1 range.
2016-01-01 00:00:00 | 2016-12-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | The helper columns (H) is populated using: =IF(AND(MONTH(F6)=MONTH(F$2),DAY(F6)=DAY(F$2)),SUM(MAX(H$5:H5),1),0)
2017-01-01 00:00:00 | 2017-12-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Note that the helper column is populated starting with row6
2018-01-01 00:00:00 | 2018-12-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | The From column is populated using: =IF(ROW()=5,F2,MIN(AGGREGATE(15,6,A$2:A$33/(A$2:A$33>F4),1),DATE(2000+MAX(H4:H$5)*3,MONTH(F$2),DAY(F$2))))
2019-01-01 00:00:00 | 2019-12-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | The To column is populated using: =MIN(IF(F6<>"",F6-1,10^10),AGGREGATE(15,6,B$2:B$33/(B$2:B$33>F5),1))
2020-01-01 00:00:00 | 2020-12-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | The other Tests seem to have a different logic. For example Test1 moves from step5 to step6 at the beginning of the 15th year of employment, whereas Test2 waits until the 3rd year of employment to move from step1 to [NAME].
2021-01-01 00:00:00 | 2021-12-31 00:00:00
2022-01-01 00:00:00 | 2022-12-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Generate range of dates from a given period (excelforum.com)
2023-01-01 00:00:00 | 2023-12-31 00:00:00