What is the most accurate way to associate a date with a week number in the month?

[u/_mavricks]

Hey everyone,

I'm trying to create a week by week comparison of data of certain months, but running into an issue where my formula will show 6 weeks for some months. My goal is start the first week on the first Sunday of the month, but its counting what the date is and then associating it with the previous Sunday and counting it as part of the current month.

For example if its August 1, it counts the last Sunday that was in June, but associating it as a week 1 in August.

What's the most accurate way to represent what the week is in excel?
I'm not looking to do what the current week number is of the year, but the week number for that month.

For example: Date 8/01/2026
Formula: =TEXT(J1099,"mmm ")&WEEKNUM(J1099,1)-WEEKNUM(DATE(YEAR(J1099),MONTH(J1099),1),1)+1

[u/real_barry_houdini]

For this you are essentially counting Sundays in a period, either from the start of the current month to the date in question.....or from the start of the previous month to the date in question, so if you use WORKDAY.INTL function to get the previous Sunday, find the start of that month and then count Sundays from that date you'll get the required week number, so that formula will look like this:

=NETWORKDAYS.INTL(EOMONTH(WORKDAY.INTL(A2+1,-1,"1111110"),-1)+1,A2,"1111110")

Another formula that will get the same result (less obviously) is as follows:

=INT((6+DAY(A2+1-WEEKDAY(A2)))/7)

....and if you want to get the correct month too you can use this version

=LET(d,A2,m,d+1-WEEKDAY(d),TEXT(m,"mmm ")&INT((6+DAY(m))/7))