Forum Discussion
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_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:
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 |
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: