Week number for a particular month not working for the month of January

[ExcelNewbie2020]

Sirs,

I have a formula that counts the week number of a particular month (from week 1 to week 5). It is working properly from february 2023 onwards but its not working for January 2023. Please check how to make it work.. many thanks

testing.xlsx
	A	B	C	D
1			Current Fomula Result	Expected Result
2	27-01-23		-47	4
3	28-01-23		-47	4
4	29-01-23		-47	4
5	30-01-23		-46	5
6	31-01-23		-46	5
7	01-02-23		1	1
8	02-02-23		1	1
9	03-02-23		1	1
10	04-02-23		1	1
11	05-02-23		1	1
12	06-02-23		2	2
13	07-02-23		2	2
14	08-02-23		2	2
15	09-02-23		2	2
16	10-02-23		2	2
17	11-02-23		2	2
18	12-02-23		2	2
Sheet17

Cell Formulas
Range	Formula
C2:C18	C2	=ISOWEEKNUM(A2)-ISOWEEKNUM(DATE(YEAR(A2),MONTH(A2),1))+1

 

[alansidman]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Inserted Week of Month" = Table.AddColumn(#"Changed Type", "Week of Month", each Date.WeekOfMonth([Column1]), Int64.Type)
in
    #"Inserted Week of Month"

Column1	Week of Month
1/27/2023	4
1/28/2023	4
1/29/2023	5
1/30/2023	5
1/31/2023	5
2/1/2023	1
2/2/2023	1
2/3/2023	1
2/4/2023	1
2/5/2023	2
2/6/2023	2
2/7/2023	2
2/8/2023	2
2/9/2023	2
2/10/2023	2
2/11/2023	2
2/12/2023	3

 

[ExcelNewbie2020]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Inserted Week of Month" = Table.AddColumn(#"Changed Type", "Week of Month", each Date.WeekOfMonth([Column1]), Int64.Type)
in
    #"Inserted Week of Month"

Column1	Week of Month
1/27/2023	4
1/28/2023	4
1/29/2023	5
1/30/2023	5
1/31/2023	5
2/1/2023	1
2/2/2023	1
2/3/2023	1
2/4/2023	1
2/5/2023	2
2/6/2023	2
2/7/2023	2
2/8/2023	2
2/9/2023	2
2/10/2023	2
2/11/2023	2
2/12/2023	3

thanks man, as much as possible, i am hoping on a excel formula..

 

[Phuoc]

Or try this:

=IF(MONTH(A2)=1;ISOWEEKNUM(A2); ISOWEEKNUM(A2)-ISOWEEKNUM(EOMONTH (A2;-1)+1)+1)

 

[ExcelNewbie2020]

Or try this:

=IF(MONTH(A2)=1;ISOWEEKNUM(A2); ISOWEEKNUM(A2)-ISOWEEKNUM(EOMONTH (A2;-1)+1)+1)

thanks man. but it's error..

 

[Phuoc]

Change ; to ,

.

 

[ExcelNewbie2020]

Change ; to ,

.

thanks sir, it worked..