Forum Discussion
Re: 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.
7 Replies
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
Incredible thanks HansVogelaar - and quick too!
Second question:
Is there a way in cell C1 for example, to display the current FY based on the date?
The current financial year starts on 1st March. Therefore in cell C1, based on todays date, it would be 01/03/2024 formatted to mmmm yy (March 2024)
If todays date was 6th April 2025, cell C1 would register as 01/03/2025
Is this possible too?
Or:
=DATE(YEAR(TODAY())-(MONTH(TODAY())<3), 3, 1)
As variant
=LET( d, TODAY(), m, MONTH(d), EOMONTH(d, 2-m-12*(m<3))+1 )Thank you - much appreciated
