Problem with week number

[Excel_problem_4u]

Hi, I am in little bit of trouble state. 

What I need is - To get week number for each individual month in excel, week starting from Saturday and partial week to be counted from 1 again?

For instance, I need 1 instead of 5 and likewise for all the switch over period, if the week is going to next month I need 1 for that whole week. 

1/28/2023	Saturday	5
1/29/2023	Sunday	5
1/30/2023	Monday	5
1/31/2023	Tuesday	5
2/1/2023	Wednesday	1
2/2/2023	Thursday	1
2/3/2023	Friday	1

[#2]

@Excel_problem_4u  it can probably be simplified but try this:

=LET(d,A1,INT((DAY(d+6-WEEKDAY(d,16))/7+1)))

where A1 is the date to check 

Better yet you should put it into the Name Functions:

Name something like MonthWk and formula:

=LAMBDA(d,INT((DAY(d+6-WEEKDAY(d,16))/7+1)))

then in the spreadsheet you can just type =MonthWk(A1) and get the answer:

[#3]

@mtarler  it worked for me, thank you, Cheers !

the only problem is it will not work for below mention scenario, if the month change and Friday is the first day on the next month 

Friday	08/25/23	4
Saturday	08/26/23	5
Sunday	08/27/23	5
Monday	08/28/23	5
Tuesday	08/29/23	5
Wednesday	08/30/23	5
Thursday	08/31/23	5
Friday	09/01/23	5
Saturday	09/02/23	2

[#4]

@Excel_problem_4u  dang thought I tested that case but must have missed it.  basically had to pull the 1 outside the DAY(....) so it should be:

=LET(d,A1,INT((DAY(d+7-WEEKDAY(d,16))-1)/7+1))

so a 7 inside and then a -1 outside but that can get reduce slightly to:

MonthWk = LAMBDA(d,INT((DAY(d+7-WEEKDAY(d,16))+6)/7))

BTW in case you didn't know, you can pass arrays to the function.  So here I pass a whole column of dates J7:J209 to test it: