Hi Guys,
It's been a while since I've been on the forum fortunately, but I've finally run into a roadblock and was hoping someone could share some insight about how to approach this problem. I essentially have a series of cells were I specific a feed into my model a series of 6 years that have each been mapped with a series of four alternatives: Yearly, Semi-Annually, Quarterly or Monthly. All of these are interchangeable between years. The data looks as follows currently.
2017 - Yearly
2018 - Yearly
2019 - Semi-Annually
2020 - Quarterly
2021 - Monthly
2022 - Yearly
2023 - Yearly
What I want to do is create a formula in excel that displays the data for each of the six years in the format I want, so the output would look something like this:
DESIRED OUTPUT:
2017 FY-17
2018 FY-18
2019 H1-19
2019 H2-19
2020 Q1-20
2020 Q2-20
2020 Q3-20
2020 Q4-20
2021 Jan-21
2021 Feb-21
2021 Mar-21
2021 Apr-21
2021 May-21
2021 Jun-21
2021 Jul-21
2021 Aug-21
2021 Sep-21
2021 Oct-21
2021 Nov-21
2021 Dec-21
2022 FY-22
2023 FY-23
I have mapped a series of "helper columns" below which classify each data to its most granular and which in theory could be linked by some sort of an index match function that just cross-references the year by the desired format. My question is, how do I link all of this together by cross-referencing the year to the desired format and create a function that looks up the format and returns the unique instances of that format for each year? For example, Quarterly 2020 would look against this table and look up Q1-20, Q2-20, Q3-20, Q4-20 and then move on to the next year. I have attached a spreadsheet with everything in there hopefully easily laid out but any guidance on how to approach this would be hugely helpful.
Link to spreadsheet Attached.
Helper Columns:
Monthly Periods Back Months Monthly Quarterly Semi-Annually Yearly
1/31/2017 1 Jan-17 Q1-17 H1-17 FY-17
2/28/2017 2 Feb-17 Q1-17 H1-17 FY-17
3/31/2017 3 Mar-17 Q1-17 H1-17 FY-17
4/30/2017 4 Apr-17 Q2-17 H1-17 FY-17
5/31/2017 5 May-17 Q2-17 H1-17 FY-17
6/30/2017 6 Jun-17 Q2-17 H1-17 FY-17
7/31/2017 7 Jul-17 Q3-17 H2-17 FY-17
8/31/2017 8 Aug-17 Q3-17 H2-17 FY-17
9/30/2017 9 Sep-17 Q3-17 H2-17 FY-17
10/31/2017 10 Oct-17 Q4-17 H2-17 FY-17
11/30/2017 11 Nov-17 Q4-17 H2-17 FY-17
12/31/2017 12 Dec-17 Q4-17 H2-17 FY-17
.....
.....
Bookmarks