Forum Discussion
wktay
Nov 24, 2023Copper 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 t...
- Nov 24, 2023
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")
HansVogelaar
Nov 24, 2023MVP
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")
- wktayNov 28, 2023Copper ContributorTyvm
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