Weeknum vs Month

[chandrashekar]

Hello Team,

Could you please help me in formula(attached). Where if week number continuous to next month I need to get previous month in Column D(Formula generate)

Regards,
Chandrashekar B

 

[PeteWright]

In cell D2 enter following:

= DATE(YEAR(A2), MONTH(A2)-1, 1)

 

[chandrashekar]

Hello,

Can you please check formula again as requirement is different. Month should change as per week number.

Sometime week number will same for this month and next month.

Regards,
Chandrashekar B

 

[Cubist]

Try adding a check for the week number from @PeteWright's solution.

=IF(B2=B1,C1,DATE(YEAR(A2), MONTH(A2)-1, 1))

 

[chandrashekar]

Hello,

Sorry it is not working expected result in D column.

Regards,
Chandrashekar B

 

[Fluff]

How about

=IF(C2=C1,D1,EOMONTH(A2,-1)+1)

 

[Cubist]

Book1
	A	B	C	D
2	4/15/24		16	Apr 24
3	4/16/24		16	Apr 24
4	4/17/24		16	Apr 24
5	4/18/24		16	Apr 24
6	4/19/24		16	Apr 24
7	4/20/24		16	Apr 24
8	4/21/24		16	Apr 24
9	4/22/24		17	Apr 24
10	4/23/24		17	Apr 24
11	4/24/24		17	Apr 24
12	4/25/24		17	Apr 24
13	4/26/24		17	Apr 24
14	4/27/24		17	Apr 24
15	4/28/24		17	Apr 24
16	4/29/24		18	Apr 24
17	4/30/24		18	Apr 24
18	5/1/24		18	Apr 24
19	5/2/24		18	Apr 24
20	5/3/24		18	Apr 24
21	5/4/24		18	Apr 24
22	5/5/24		18	Apr 24
23	5/6/24		19	May 24
24	5/7/24		19	May 24
25	5/8/24		19	May 24
26	5/9/24		19	May 24
27	5/10/24		19	May 24
28	5/11/24		19	May 24
29	5/12/24		19	May 24
Sheet3

Cell Formulas
Range	Formula
C2:C29	C2	=ISOWEEKNUM(A2)
D2:D29	D2	=IF(C2=C1,D1,A2)
A3:A29	A3	=A2+1

 

[chandrashekar]

How about

=IF(C2=C1,D1,EOMONTH(A2,-1)+1)

Hello,

D Column is expected output.

Regards,
Chandrashekar B

 

[chandrashekar]

Hello,

D Column is expected output.

Regards,
Chandrashekar B

Book1
	A	B	C	D
2	4/15/24		16	Apr 24
3	4/16/24		16	Apr 24
4	4/17/24		16	Apr 24
5	4/18/24		16	Apr 24
6	4/19/24		16	Apr 24
7	4/20/24		16	Apr 24
8	4/21/24		16	Apr 24
9	4/22/24		17	Apr 24
10	4/23/24		17	Apr 24
11	4/24/24		17	Apr 24
12	4/25/24		17	Apr 24
13	4/26/24		17	Apr 24
14	4/27/24		17	Apr 24
15	4/28/24		17	Apr 24
16	4/29/24		18	Apr 24
17	4/30/24		18	Apr 24
18	5/1/24		18	Apr 24
19	5/2/24		18	Apr 24
20	5/3/24		18	Apr 24
21	5/4/24		18	Apr 24
22	5/5/24		18	Apr 24
23	5/6/24		19	May 24
24	5/7/24		19	May 24
25	5/8/24		19	May 24
26	5/9/24		19	May 24
27	5/10/24		19	May 24
28	5/11/24		19	May 24
29	5/12/24		19	May 24
Sheet3

Cell Formulas
Range	Formula
C2:C29	C2	=ISOWEEKNUM(A2)
D2:D29	D2	=IF(C2=C1,D1,A2)
A3:A29	A3	=A2+1

 

[Fluff]

D Column is expected output.

Just format the column to your preferred format.