Formula for week number with month

[aryanaveen]

Hi All

is there a way to get week number for the month along with month name in excel using the formula

For Ex if Column A has date 30/Jan/2022 then in column B I need this date to appear in format "Jan week 5"
and if the date is 2/Feb/2022 then it should update as "Feb week 1"

 

[Sufiyan97]

Try

=TEXT(A1,"mmm")&" Week "&INT((DAY(A1-1)/7)+1)

 

[James006]

Hi,

You can test

=TEXT(A10,"mmm")&" week "&WEEKNUM(A10)

 

[etaf]

weeknum() or ISOweeknum()

2/feb/2022

=TEXT(A2,"mmm")&" week "&WEEKNUM(A2)
or if you have a later version of excel
=TEXTJOIN(" ",,TEXT(MONTH(A2),"mmm"),"week",B2)

Book2
	A	B	C	D	E	F	G
1	date	weeknum	isoweeknum
2	1/29/22	5	4		Jan week 5		Jan week 5
3	1/30/22	6	4		Jan week 6		Jan week 6
4	1/31/22	6	5		Jan week 6		Jan week 6
5	2/1/22	6	5		Jan week 6		Feb week 6
6	2/2/22	6	5		Jan week 6		Feb week 6
7	2/3/22	6	5		Jan week 6		Feb week 6
8	2/4/22	6	5		Jan week 6		Feb week 6
9	2/5/22	6	5		Jan week 6		Feb week 6
10	2/6/22	7	5		Jan week 7		Feb week 7
11	2/7/22	7	6		Jan week 7		Feb week 7
12	2/8/22	7	6		Jan week 7		Feb week 7
Sheet1

Cell Formulas
Range	Formula
B2:B12	B2	=WEEKNUM(A2)
C2:C12	C2	=ISOWEEKNUM(A2)
E2:E12	E2	=TEXTJOIN(" ",,TEXT(MONTH(A2),"mmm"),"week",B2)
G2:G12	G2	=TEXT(A2,"mmm")&" week "&WEEKNUM(A2)

 

[Kerryx]

Book2
	A	B
1	29-Jan-22	Jan week 5
2	05-Feb-22	Feb week 1
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=TEXT(A1,"mmm") & " week " & WEEKNUM(A1,1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),1)+1

 

[Fluff]

If the 1st of the month is always week 1 how about

=TEXT(A1,"mmm ""week """)&INT((DAY(A1)-1)/7)+1