Auto-Update Date Based on Cell Input

[acool]

Hi Everyone,

I am currently trying to create a formula in cell C2:N2 which will auto update months/year based on the starting value in cell X2. For example, if X2 is change to 2/1/2024, I would like Cell C2 to update to Feb-2024 and as well as the have remaining 11 months to update. Any help would be greatly appreciated. Thank you for your help in advanced.

 

[Cubist]

Try

=MAKEARRAY(1,12,LAMBDA(r,c,EOMONTH(X2,0)+(c-1)))

 

[PeteWright]

A non-lambda formula version:

Book1.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	12 Months Forecast
2	Jan-2024	Feb-2024	Mar-2024	Apr-2024	May-2024	Jun-2024	Jul-2024	Aug-2024	Sep-2024	Oct-2024	Nov-2024	Dec-2024									Start Date	1/1/2024
Sheet2

Cell Formulas
Range	Formula
C2	C2	= DATE(YEAR($X$2), MONTH($X$2)+0, 1)
D2	D2	= DATE(YEAR($X$2), MONTH($X$2)+1, 1)
E2	E2	= DATE(YEAR($X$2), MONTH($X$2)+2, 1)
F2	F2	= DATE(YEAR($X$2), MONTH($X$2)+3, 1)
G2	G2	= DATE(YEAR($X$2), MONTH($X$2)+4, 1)
H2	H2	= DATE(YEAR($X$2), MONTH($X$2)+5, 1)
I2	I2	= DATE(YEAR($X$2), MONTH($X$2)+6, 1)
J2	J2	= DATE(YEAR($X$2), MONTH($X$2)+7, 1)
K2	K2	= DATE(YEAR($X$2), MONTH($X$2)+8, 1)
L2	L2	= DATE(YEAR($X$2), MONTH($X$2)+9, 1)
M2	M2	= DATE(YEAR($X$2), MONTH($X$2)+10, 1)
N2	N2	= DATE(YEAR($X$2), MONTH($X$2)+11, 1)

Then, format the cells as "MMM-YYYY"

 

[Asbestos_Jen]

Yet another option without Lambda:

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	12 month forecast																				LTM Revenue	11000
2	Jan-2024	Feb-2024	Mar-2024	Apr-2024	May-2024	Jun-2024	Jul-2024	Aug-2024	Sep-2024	Oct-2024	Nov-2024	Dec-2024									Start Date:	01-01-24
Sheet1

Cell Formulas
Range	Formula
C2	C2	=EOMONTH($X$2,0)
D2:N2	D2	=EOMONTH(C$2,1)

 

[Cubist]

There's a mistake in this formula above in post #2. It should've been this

=MAKEARRAY(1,12,LAMBDA(r,c,EOMONTH(X2,c-1)))

 

[acool]

Try

=MAKEARRAY(1,12,LAMBDA(r,c,EOMONTH(X2,0)+(c-1)))

Hi @Cubist. When trying the formula above, I receive these inputs back.

 

[Cubist]

Hi @Cubist. When trying the formula above, I receive these inputs back.

View attachment 108805

See post#5 for correction.

 

[Fluff]

Another option

=EDATE(X2,SEQUENCE(,12,0))

 

[acool]

There's a mistake in this formula above in post #2. It should've been this

=MAKEARRAY(1,12,LAMBDA(r,c,EOMONTH(X2,c-1)))

This worked! Thanks everyone for your help!