Display Certain Periods Based on Input

[ashaman88]

Hello! New to the forum and couldn't find this exact same issue, so thought I would throw this out there! So I'm working on this presentation and want to make it more automated.

Specifically I want the periods that we show to be dynamic based on the current period we are presenting.


The logic should go like this

First column = December of 3 years prior to the year we are looking at
After that, we should show each quarter of the next 2 years (2020 – 2021) and the equivalent month of our current period.
For the current year, we should show all months up to present.
So for example February 2022 should look like the attached:

example.png
The tricky part is if the current period is not in Q1, then the second column would be March 2020 and if it’s a Q end, then the columns we would show would be even less.

The input to change the current month end date is on tab “Legend” - and is just the last day of the current month - so in the current example it would be 2/28/2022.

I have tried using IF formulas that ended up getting longer and longer but eventually got stuck and it was driving me nuts:
example formula.png

Please let me know if I should include any other information!

[ashaman88]

Attached an example of the formulas I was coming up with, but I'm sure there is a more simple way.

[JeteMc]

Hello ashaman88 and Welcome to Excel Forum.
This is a low-tech proposal upon which I hope someone will improve.
1. On the Legend sheet populate cell U8 using: =YEAR(U9)
2. Populate U7 using: =U9 and formatted mmmm
3. On the 10 sheet populate B63:B64 using: =DATE(Legend!U$8-3,12,31)
4. Populate C63:J63 using: =DATE(Legend!$U8-2,COLUMNS($C63:C63)*3,1)
5. Populate C64:G64 using: =IF(B64<>B63,B63,IF(AND(EDATE(Legend!$U9,-24)>B63,EDATE(Legend!$U9,-24)<C63),EDATE(Legend!$U9,-24),C63))
6. Populate H64:L64 using: =EDATE(C64,12)
7. Populate K63:V63 and M64:X64 using: =IF(DATE(Legend!$U8,COLUMNS($K63:K63),1)>Legend!$U9,"",DATE(Legend!$U8,COLUMNS($K63:K63),1))
8. Populate B62:X62 using: =IF(MOD(Legend!$U7,3)=0,B63,B64)
Let us know if you have any questions.

[ashaman88]

Thanks JeteMc! Sorry for the delay, but circling back to this -- when I input 6/30/2022 into the input, it seems to repeat June twice in certain spots, any idea why?

[JeteMc]

I input 6/30/2022 in cell U9 on the legend sheet and saw that Jan 00 was repeated in cells W62:X62 on the 10 sheet.
I believe this is because the formula in row 63 had not been copied across to cell AB63.
I have now copied formulas in rows 62:64 over to column AB, see if that resolves the issue.
If not, please upload a sample that illustrates the problem.
Let us know if you have any questions.