Week Name Based on Month

[kumara_faith]

Hi,

I have the following table:

Date	Week
3/1/2022​	Jan Week 1
12/1/2022​	Jan Week 2
5/2/2022​	Feb Week 1
4/3/2022​	Mar Week 1

I have the dates in the first column. In the second column, I am trying to name the week based on the date. For example 3/1/2022 is Jan Week 1.

Is it possible to do this with a formula ? Appreciate all the help.

 

[bebo021999]

Mon 28-Feb is "Feb week 5", Tue 1-Mar is "Mar week 1"
Is it correct? An Feb Week 5 have only 1 day?

 

[kumara_faith]

Hi,

Yes, correct.

 

[bebo021999]

Can I say the logic is:
Week starts from Mon-Sun
Any week with Monday fall into which month, then that week belongs to that month.
Is it correct?
Could you give more sample for week with days of 2 months?

 

[fhqwgads]

try

Book4.xlsm
	A	B
1	date	week num
2	31/12/2021	Dec Week 5
3	1/01/2022	Jan Week 1
4	2/01/2022	Jan Week 1
5	3/01/2022	Jan Week 2
6	4/01/2022	Jan Week 2
7	31/01/2022	Jan Week 6
8	1/02/2022	Feb Week 1
9	25/02/2022	Feb Week 4
10	26/02/2022	Feb Week 4
11	27/02/2022	Feb Week 4
12	28/02/2022	Feb Week 5
13	1/03/2022	Mar Week 1
14	2/03/2022	Mar Week 1
15	3/03/2022	Mar Week 1
16	4/03/2022	Mar Week 1
17	5/03/2022	Mar Week 1
18	6/03/2022	Mar Week 1
19	7/03/2022	Mar Week 2
20	31/03/2022	Mar Week 5
21	1/04/2022	Apr Week 1
22	2/04/2022	Apr Week 1
23	3/04/2022	Apr Week 1
24	4/04/2022	Apr Week 2
25	5/04/2022	Apr Week 2
Sheet5

Cell Formulas
Range	Formula
B2:B25	B2	=TEXT(A2,"mmm")&" Week "&QUOTIENT(TEXT(DAY(A2),"#")+(7-WEEKDAY(A2,2)),7)+1

 

[BSALV]

almost the same formula

Map1
	A	B	C	D
1	date	BSALV	fhqwgads	delta
2	za 01/01/2022	jan week 1	jan Week 1	0
3	zo 02/01/2022	jan week 1	jan Week 1	0
4	ma 03/01/2022	jan week 2	jan Week 2	0
5	di 04/01/2022	jan week 2	jan Week 2	0
6	wo 05/01/2022	jan week 2	jan Week 2	0
7	do 06/01/2022	jan week 2	jan Week 2	0
8	vr 07/01/2022	jan week 2	jan Week 2	0
9	za 08/01/2022	jan week 2	jan Week 2	0
10	zo 09/01/2022	jan week 2	jan Week 2	0
11	ma 10/01/2022	jan week 3	jan Week 3	0
12	di 11/01/2022	jan week 3	jan Week 3	0
13	wo 12/01/2022	jan week 3	jan Week 3	0
14	do 13/01/2022	jan week 3	jan Week 3	0
15	vr 14/01/2022	jan week 3	jan Week 3	0
16	za 15/01/2022	jan week 3	jan Week 3	0
17	zo 16/01/2022	jan week 3	jan Week 3	0
18	ma 17/01/2022	jan week 4	jan Week 4	0
19	di 18/01/2022	jan week 4	jan Week 4	0
Blad1

Cell Formulas
Range	Formula
A2:A1001	A2	=SEQUENCE(1000,,DATE(2022,1,1))
B2:B19	B2	=TEXT(A2,"mmm")& " week " & CEILING((DAY(A2)-MOD(A2-2,7))/7,1)+1
C2:C19	C2	=TEXT(A2,"mmm")&" Week "&QUOTIENT(TEXT(DAY(A2),"#")+(7-WEEKDAY(A2,2)),7)+1
D2:D19	D2	=--(B2<>C2)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=WEEKDAG(A1;2)=1	text	NO

 

[kumara_faith]

Hi fhqwgads/BSALV/bebo021999,

Thank you for your responses.

fhqwgads/BSALV

Thank you for your solution and it worked. Appreciate your valuable time and patience. Have a great day.