Forum Discussion
Solved
Automatic Rolling 12 Month List
Hi all
I've looked first but nothing seems to give me what I am looking for. Hope you can help!
I'm simply looking for a rolling 12 months list that automatically updates based on the current date.
The financial year starts in March. An example below based on today's date being in February 2025:
Column A
January 26
February 25
March 25
April 25
May 25
June 25
July 25
August 25
September 25
October 25
November 25
December 25
The idea being that once the month has passed, February 25 would become February 26 in the list and so on.
Can this be done with formula based on the today() date?
Thank you for any help
In A2:
=LET(s, SEQUENCE(12), DATE(YEAR(TODAY())+(s<MONTH(TODAY())), s, 1))
Apply the custom number format mmmm yy to A2:A13.
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()))))
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)
