Spreadsheet: 726504d1617279039-generate-range-of-dates-from-a-given-period-sample_date
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 |  |  |  | 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 |  | *maximum step is 8, if it reaches 8 then it will remain 8 even if there's another 3 years.
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 |  |  | 5 | 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 |  |  | 6 | 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 |  |  | 6 | 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 |  | 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 | 2007-06-30 00:00:00 |  |  | 7 | 2003-09-16 00:00:00 | 2006-09-15 00:00:00
2007-07-01 00:00:00 | 2008-06-30 00:00:00 |  |  | 8 | 2006-09-16 00:00:00 | 2007-06-30 00:00:00
2008-07-01 00:00:00 | 2009-06-30 00:00:00 |  |  | 8 | 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
2016-01-01 00:00:00 | 2016-12-31 00:00:00
2017-01-01 00:00:00 | 2017-12-31 00:00:00
2018-01-01 00:00:00 | 2018-12-31 00:00:00
2019-01-01 00:00:00 | 2019-12-31 00:00:00
2020-01-01 00:00:00 | 2020-12-31 00:00:00
2021-01-01 00:00:00 | 2021-12-31 00:00:00
2022-01-01 00:00:00 | 2022-12-31 00:00:00
2023-01-01 00:00:00 | 2023-12-31 00:00:00