Assign Month number 1 to n based on sequential week range

[amolvijay]

Hi Team,
Plz help me to automate the formula to compute month sequence...using formula.

For a intersection week, It should advance a month count if Preceding months days are less than succeeding month and vice versa it should remain same if preceding months days are greater than succeeding month days.

Month

M1

M1

M1

M1

M2

M2

M2

M2

M3

M3

M3

M3

M3

M4

M4

M4

M4

M5

M5

M5

M5

M6

M6

M6

M6

M6

Week Start

04-Mar

11-Mar

18-Mar

25-Mar

01-Apr

08-Apr

15-Apr

22-Apr

29-Apr

06-May

13-May

20-May

27-May

03-Jun

10-Jun

17-Jun

24-Jun

01-Jul

08-Jul

15-Jul

22-Jul

29-Jul

05-Aug

12-Aug

19-Aug

26-Aug

Week End

08-Mar

15-Mar

22-Mar

29-Mar

05-Apr

12-Apr

19-Apr

26-Apr

03-May

10-May

17-May

24-May

31-May

07-Jun

14-Jun

21-Jun

28-Jun

05-Jul

12-Jul

19-Jul

26-Jul

02-Aug

09-Aug

16-Aug

23-Aug

30-Aug

 

[awoohaw]

What is wrong with what you have posted above? Or are you asking to create this with a formula?

Should the first Column "M3" be M3 or M4?

What determines the new month, the start of the week or any day in the week?

Here is something just based on the month number of either the start or end day of the week:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Month
2	WeekStart	2024-03-04	2024-03-11	2024-03-18	2024-03-25	2024-04-01	2024-04-08	2024-04-15	2024-04-22	2024-04-29	2024-05-06	2024-05-13	2024-05-20	2024-05-27	2024-06-03	2024-06-10	2024-06-17	2024-06-24	2024-07-01	2024-07-08	2024-07-15	2024-07-22	2024-07-29	2024-08-05	2024-08-12	2024-08-19	2024-08-26
3	WeekEnd	2024-03-08	2024-03-15	2024-03-22	2024-03-29	2024-04-05	2024-04-12	2024-04-19	2024-04-26	2024-05-03	2024-05-10	2024-05-17	2024-05-24	2024-05-31	2024-06-07	2024-06-14	2024-06-21	2024-06-28	2024-07-05	2024-07-12	2024-07-19	2024-07-26	2024-08-02	2024-08-09	2024-08-16	2024-08-23	2024-08-30
4
5	Month based Start	M1	M1	M1	M1	M2	M2	M2	M2	M2	M3	M3	M3	M3	M4	M4	M4	M4	M5	M5	M5	M5	M5	M6	M6	M6	M6
6	Month Based End	M1	M1	M1	M1	M2	M2	M2	M2	M3	M3	M3	M3	M3	M4	M4	M4	M4	M5	M5	M5	M5	M6	M6	M6	M6	M6
Sheet4

Cell Formulas
Range	Formula
B2:AA2	B2	=SEQUENCE(,26,DATE(2024,3,4),7)
B3:AA3	B3	=SEQUENCE(,26,DATE(2024,3,8),7)
B5:AA6	B5	="M"&MONTH(B2)-2
Dynamic array formulas.

 

[Kaper]

Assuming your table starts in A1 i'd write 1 in B1 and in C1 (and copy to right) formula

=IF(MONTH(B3)<>MONTH(C3),B1+1,B1)

Then I'd select all cells in row 1 with 1 and with the formula, so may be something like B1:R1, or B1:Z1, or ... and format the cells with custom formatting:
"M"0
Note double quotation marks before and after M. They are necessary, otherwise excel will treat this formatting as a month number for 1900 Jan 01; 1900 Jan 02 and so on.

 

[Kaper]

@awoohaw - my understanding was that these are some dates on some shedule and dates in column B are first month of that task - doesn't matter if it's January, March or ... December.

But let's wait for @amolvijay reply

 

[awoohaw]

@awoohaw - my understanding was that these are some dates on some shedule and dates in column B are first month of that task - doesn't matter if it's January, March or ... December.

But let's wait for @amolvijay reply

Thanks for the statement. My question about the M3 or M4 was for the column where the week transitions from April to May.

 

[Kaper]

Hmmm, @amolvijay was seen on the forum on Saturday, but not commented ...

 

[amolvijay]

Hmmm, @amolvijay was seen on the forum on Saturday, but not commented ...

@Kaper @awoohaw .. Apologize for delay in response.
I was desparately looking for the solution...and after two days of efforts finally I got the result..
Tomorrow I will paste the complicated formula currently working as per my requirement...