# Problem with week number

Copper Contributor

# Problem with week number

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
3 Replies

# Re: Problem with week number

@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:

# Re: Problem with week number

@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

# Re: Problem with week number

@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: