Formula to return month number for the next 12 months

[Marklarbear]

Hi Brains Trust,

I'm trying to work out a formula that completes column B (based of current date) and then have the formula in column C return the month name as a txt value... hopefully that makes sense...

I can do the basic formulas to start off in cells A2 and B2 - but a bit stumped after that.....

What i'm trying to achieve is that whatever day of the year it is, columns B & C adjust accordingly....

cheers

 

[Rick Rothstein]

Since you are using XL365, put this single formula in cell B2 and it will generate the full result you are looking for...

=LET(n,MOD(SEQUENCE(12,,MONTH(A2))-1,12)+1,HSTACK(n,TEXT(28*n,"mmm")))

 

[kevin9999]

Or you could just format columns B & C as Custom: m (for column B) and mmm (for column C)

Book1
	A	B	C
1	Today's date	Month Number	Month Name
2	14/02/2024	2	Feb
Sheet1

Cell Formulas
Range	Formula
A2	A2	=TODAY()
B2	B2	=A2
C2	C2	=A2

 

[kevin9999]

Or this?

Book1
	A	B	C
1	Today's date	Month Number	Month Name
2	14/02/2024	2	Feb
3		3	Mar
4		4	Apr
5		5	May
6		6	Jun
7		7	Jul
8		8	Aug
9		9	Sep
10		10	Oct
11		11	Nov
12		12	Dec
13		1	Jan
Sheet1

Cell Formulas
Range	Formula
A2	A2	=TODAY()
B2	B2	=A2
C2	C2	=A2
B3:C13	B3	=EDATE(B2,1)

 

[JEC]

=LET(d,EDATE(A1,SEQUENCE(12,,0)),HSTACK(MONTH(d),TEXT(d,"mmm")))

 

[Marklarbear]

Since you are using XL365, put this single formula in cell B2 and it will generate the full result you are looking for...

=LET(n,MOD(SEQUENCE(12,,MONTH(A2))-1,12)+1,HSTACK(n,TEXT(28*n,"mmm")))

Cheers Rick - works a treat!