Forum Discussion

Excel_problem_4u's avatar
Excel_problem_4u
Copper Contributor
Apr 10, 2023

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 instan...
excel
Formulas and Functions
mtarler's avatar
mtarler
Silver Contributor
Apr 10, 2023

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:

 

 

Excel_problem_4u's avatar
Excel_problem_4u
Copper Contributor
Apr 11, 2023

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 

Friday08/25/234
Saturday08/26/235
Sunday08/27/235
Monday08/28/235
Tuesday08/29/235
Wednesday08/30/235
Thursday08/31/235
Friday09/01/235
Saturday09/02/232
  • mtarler's avatar
    mtarler
    Silver Contributor
    Apr 11, 2023

    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:

     

     

Resources