Automatically Change Month formula.

[Dandelion3]

Hi everyone,

Is there a formula to automatically change the months. For example, in the snippet attached JUL is the current reporting month & there's also forecast data for the next 3 months off to the right. I have a data validation setup on another tab for the current reporting to automatcally change when a month is selected from the drop down. I also want the trailing 3 months to change as the current month changes. If I select AUG for the current month, I want the next 3 to automatically update to SEP, OCT, NOV.

Also, I would like to keep the data in the file for each month to be able to flip back & forth between months to review the data in one file instead of having a different file each month.

 

[awoohaw]

Be sure the "JUL" is an actual date, formatted as MMM.

Book1
	A	B	C	D	E
1	2024-07-01
2	Jul	Variance	Aug	Sep	Oct
3
Sheet2

Cell Formulas
Range	Formula
A2	A2	=A1
C2:E2	C2	=EDATE(A2,SEQUENCE(,3))
Dynamic array formulas.

If you must use text with a drop down, assuming you always want year in 2024:

Book1
	A	B	C	D	E
4
5	Aug		Sep	Oct	Nov
6
7	drop down
8	Jan
9	Feb
10	Mar
11	Apr
12	May
13	Jun
14	Jul
15	Aug
16	Sep
17	Oct
18	Nov
19	Dec
Sheet2

Cell Formulas
Range	Formula
C5:E5	C5	=EDATE(DATE(2024,MATCH(A5,$A$8:$A$19,0),1),SEQUENCE(,3))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A5	List	=$A$8:$A$19

 

[Dandelion3]

Be sure the "JUL" is an actual date, formatted as MMM.

Book1
	A	B	C	D	E
1	2024-07-01
2	Jul	Variance	Aug	Sep	Oct
3
Sheet2

Cell Formulas
Range	Formula
A2	A2	=A1
C2:E2	C2	=EDATE(A2,SEQUENCE(,3))
Dynamic array formulas.

Thank you so much for the quick response; however, I got a SPILL error when I used the provided formula

 

[awoohaw]

then you're pasting the formulas in areas that have adjacent cells. Clear out cells s D3 and E3, and D6 and E6.

Paste the mini sheet range in a brand new worksheet first, just so you see how it works.

(If you edited the formula I gave at all, please post it).

 

[Dandelion3]

Be sure the "JUL" is an actual date, formatted as MMM.

Book1
	A	B	C	D	E
1	2024-07-01
2	Jul	Variance	Aug	Sep	Oct
3
Sheet2

Cell Formulas
Range	Formula
A2	A2	=A1
C2:E2	C2	=EDATE(A2,SEQUENCE(,3))
Dynamic array formulas.

If you must use text with a drop down, assuming you always want year in 2024:

Book1
	A	B	C	D	E
4
5	Aug		Sep	Oct	Nov
6
7	drop down
8	Jan
9	Feb
10	Mar
11	Apr
12	May
13	Jun
14	Jul
15	Aug
16	Sep
17	Oct
18	Nov
19	Dec
Sheet2

Cell Formulas
Range	Formula
C5:E5	C5	=EDATE(DATE(2024,MATCH(A5,$A$8:$A$19,0),1),SEQUENCE(,3))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A5	List	=$A$8:$A$19

Got it. Thank you

 

[Scott Huish]

For only 3, you could drop the SEQUENCE function call.

=EOMONTH(A2,{1,2,3})

 

[Scott Huish]

If you're pulling from a dropdown and just have the text like Jul, you could do this:
=EOMONTH(A2&"01",{1,2,3})

Book1
	A	B	C	D	E
2	Dec	Variance	Jan	Feb	Mar
Sheet1

Cell Formulas
Range	Formula
C2:E2	C2	=EOMONTH(A2&"01",{1,2,3})
Dynamic array formulas.

 

[Dandelion3]

then you're pasting the formulas in areas that have adjacent cells. Clear out cells s D3 and E3, and D6 and E6.

Paste the mini sheet range in a brand new worksheet first, just so you see how it works.

(If you edited the formula I gave at all, please post it).

Can I use the same formula if the source date changes? Say I want the report date to be the date of a meeting or a week ending date, is there something different I should do? Right now it only works if the date is the first of the month. If that changes, my data errors out. What I'm building is a weekly sales forecast dashboard.

 

[Dandelion3]

If you're pulling from a dropdown and just have the text like Jul, you could do this:
=EOMONTH(A2&"01",{1,2,3})

Book1
	A	B	C	D	E
2	Dec	Variance	Jan	Feb	Mar
Sheet1

Cell Formulas
Range	Formula
C2:E2	C2	=EOMONTH(A2&"01",{1,2,3})
Dynamic array formulas.

Can I use the same formula if the source date changes? Say I want the report date to be the date of a meeting or a week ending date, is there something different I should do? Right now it only works if the date is the first of the month. If that changes, my data errors out. What I'm building is a weekly sales forecast dashboard.

 

[Scott Huish]

What do you mean if it's the first of the month? That formula literally only referenced the text Jul or Dec or whatever.
Can you post a sample of what you want and expected results?