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