Forum Discussion
Automatic Rolling 12 Month List
In A2:
=LET(s, SEQUENCE(12), DATE(YEAR(TODAY())+(s<MONTH(TODAY())), s, 1))
Apply the custom number format mmmm yy to A2:A13.
Hi HansVogelaar - once again thanks for this!
Question - can the formula be adapted to include a set number of previous months also?
For example. Current month is February 25. The rolling 12 months would therefore be if I wanted 2 extra months history:
December 24
January 25
February 25
March 25
April 25
May 25
June 25
July 25
August 25
September 25
October 25
November 25
Would be great if this number in the formula can be adjusted at any time should I wish to include more or less months prior to todays month
To get the result shown in your post:
Number of months is in D2.
Formula in A1 is
=LET(o, D1, s, SEQUENCE(12), t, EOMONTH(TODAY(), -o-2)+1, EDATE(t, s))
If you want months prior to the current month to display the next year:
=LET(o, D1, s, SEQUENCE(12), t, EOMONTH(TODAY(), -o-2)+1, p, EDATE(t, s), EDATE(p, 12*(p<TODAY()-DAY(TODAY()))))
Works perfectly, massive thank you for your time and support