SOLVED

# Change month of one cell, and cell 2, 3 will auto +1 mth +2mth

Copper Contributor

# Change month of one cell, and cell 2, 3 will auto +1 mth +2mth

Hi All,

I have 3 consecutive Months, E.g. Jan/Feb/Mar

If I change Cell A1 to Jun, C1 will auto +1 mth of A1, and E1 will auto +1 Mth of C1

So it will become Jun/Jul/Aug

Basically I just want to change Cell A1 and the other two months will auto update

 A B C D E 1 Jan / Feb / Mar

May I know how I can do this?

Kind Regards

2 Replies
best response confirmed by wktay (Copper Contributor)
Solution

# Re: Change month of one cell, and cell 2, 3 will auto +1 mth +2mth

One way to do this:

• Apply the custom number format mmm to A1, C1 and E1.
• Enter any date in the start month in A1, for example 1/1/2023
• Enter the following formula in C1: =EDATE(A1, 1)
• Enter the following formula in E1: =EDATE(C1, 1)

If you prefer to enter the month as text in A1:

• In C1, enter the formula =TEXT(EDATE("1-"&A1&"-2023",1),"mmm")
• In E1, enter the formula =TEXT(EDATE("1-"&C1&"-2023",1),"mmm")

# Re: Change month of one cell, and cell 2, 3 will auto +1 mth +2mth

Tyvm

So as you mentioned;
1) Right Click > Format Cell > Custom > put mmm
2) Then in C1 and E1, I use the Text Formula
=TEXT(EDATE("1-"&A1&"-2023",1),"mmm")

The first formula doesn't seem to work, but it's fine
1 best response

Accepted Solutions
best response confirmed by wktay (Copper Contributor)
Solution

# Re: Change month of one cell, and cell 2, 3 will auto +1 mth +2mth

One way to do this:

• Apply the custom number format mmm to A1, C1 and E1.
• Enter any date in the start month in A1, for example 1/1/2023
• Enter the following formula in C1: =EDATE(A1, 1)
• Enter the following formula in E1: =EDATE(C1, 1)

If you prefer to enter the month as text in A1:

• In C1, enter the formula =TEXT(EDATE("1-"&A1&"-2023",1),"mmm")
• In E1, enter the formula =TEXT(EDATE("1-"&C1&"-2023",1),"mmm")