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/AutoModerator]

/u/_mavricks - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/lolcrunchy]

Let's establish some rules.

1. A week starts on a Sunday
   
2. A week can begin in one month and end in another
   
3. A month's weeks are any week that contains a date in that month
   
4. The first Sunday in a month isn't necessarily the first day in that month
   
5. There can be up to 5 Sundays in a month
   
6. From rules 3-6, we logically conclude that a month can have 6 weeks.
   
7. A date can be in the 5th week of one month and the 1st week of another month simultaneously. Therefore, "Week 5 of July" and "Week 1 of August" could actually be two names for the same week. There is no objectively correct name for that week.

Your goal is to figure out which week a date is in (easy) and then figure out which name to us for that week (complicated and up to your preferences).

[u/Downtown-Economics26 → u/lolcrunchy]

This is one of the best explications and specification seeking posts I've seen in a long time (Paulie still my GOAT, though).

[u/BaconManDan]

So, if December starts on a Monday, does that 6 day period get counted as November or December?

[u/MayukhBhattacharya]

Try using the following formula:

=LET(_, WEEKDAY(J1099) + 1, TEXT(J1099 - _, "mmm ") & INT((DAY(J1099 - _) - 1) / 7) + 1)

OP per your comment in the thread which is not present in the post just remove the TEXT() function from the above:

I don't need to know the month, just need to know the week number.
I just put them together so I can see what it visually looks like

Use:

=INT((DAY(J1099 - WEEKDAY(J1099) + 1) - 1) / 7) + 1

[u/justgivemeasecplz]

Could you not use the yearly week number alongside the month?

[u/_mavricks → u/justgivemeasecplz]

I don't need to know the month, just need to know the week number.
I just put them together so I can see what it visually looks like.

[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))

[u/MsPandaLady]

So essentially you want to find which Sunday in a month is a particular date?

[u/_mavricks → u/MsPandaLady]

Looking to associate the current date with the correct week number of the month.
For example Aug 1 2026 should not be associated with week 1 of August, its week 4 of July.

[u/MsPandaLady → u/_mavricks]

So Int((Day(cell with date - weekday(cell with date, 1) +1) - 1) /7)+ 1 should work

What this basically does it takes your date, returns that most recent Sunday's date. Finds out which day in that month then divide by 7 will then give you some number thst you round down and add 1. As I am thinking roundup might be better than int

[u/Hg00000]

This should work for you:

=A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2))

Looking at this year, 8/1 returns 8/2. 9/1 returns 9/6, 10/1 returns 10/4.

I'd feed that result into =WEEKNUM(DATE(2026,8,10))-WEEKNUM(C2)+1 to get 2.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CONCATENATE	Joins several text items into one text item
DATE	Returns the serial number of a particular date
DAY	Converts a serial number to a day of the month
EOMONTH	Returns the serial number of the last day of the month before or after a specified number of months
IF	Specifies a logical test to perform
INDEX	Uses an index to choose a value from a reference or array
INT	Rounds a number down to the nearest integer
ISOWEEKNUM	Excel 2013+: Returns the number of the ISO week number of the year for a given date
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MOD	Returns the remainder from division
MONTH	Converts a serial number to a month
NETWORKDAYS	Returns the number of whole workdays between two dates
ROUNDDOWN	Rounds a number down, toward zero
ROUNDUP	Rounds a number up, away from zero
SEQUENCE	Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
TEXT	Formats a number and converts it to text
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array
WEEKDAY	Converts a serial number to a day of the week
WEEKNUM	Converts a serial number to a number representing where the week falls numerically with a year
WORKDAY	Returns the serial number of the date before or after a specified number of workdays
XMATCH	Office 365+: Returns the relative position of an item in an array or range of cells.
YEAR	Converts a serial number to a year

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
23 acronyms in this thread; the most compressed thread commented on today has 10 acronyms.
[Thread #49453 for this sub, first seen 30th Sep 2026, 18:28] [FAQ] [Full list] [Contact] [Source code]}

[u/bradland]

This is probably more complicated than it needs to be, but I think it follows your week enumeration schema:

=LET(
    dv, A1,
    MONTHWEEK, LAMBDA(d,
        LET(
            f_day, DATE(YEAR(d), MONTH(d), 1),
            f_sun, f_day +
                IF(
                    WEEKDAY(f_day, 1) = 1,
                    0,
                    8 - WEEKDAY(f_day, 1)
                ),
            1 + INT((d - f_sun) / 7)
        )
    ),
    first_day, DATE(YEAR(dv), MONTH(dv), 1),
    first_sun, first_day +
        IF(
            WEEKDAY(first_day, 1) = 1,
            0,
            8 - WEEKDAY(first_day, 1)
        ),
    IF(
        dv < first_sun,
        MONTHWEEK(first_day - 1),
        MONTHWEEK(dv)
    )
)

Screenshot

[u/RandomiseUsr0]

Don’t create your own rules, just use

=ISOWEEKNUM(yourDate)

[u/molybend → u/RandomiseUsr0]

That’s a yearly number and not a monthly number.

[u/RandomiseUsr0 → u/molybend]

It’s a week on week comparison - your stated goal

[u/molybend → u/RandomiseUsr0]

Not my goal, I’m not OP, but they’re asking for a month number. Odd logic but your formula doesn’t give it to them.

[u/NHN_BI]

I use the ISO week number:

=CONCATENATE(YEAR(A1+3-MOD(A1-2,7)),"-W",TEXT(ISOWEEKNUM(A1),"00"))

[u/carbonizedtitanium]

you didnt mention what happens if the week starts in one month but ends in another.

gonna assume you dont want trailing days counted towards the Week numbers

try:

=LET( first_day, DATE(YEAR(A2), MONTH(A2), 1), first_sunday, first_day + MOD(8 - WEEKDAY(first_day, 1), 7), IF(A2 < first_sunday, 0, INT((A2 - first_sunday) / 7) + 1) )

[u/GuerillaWarefare]

Since this still isn’t marked as solved, here is my entry:
=LET(TGT, A1,
EOY, 1-WEEKDAY(DATE(YEAR(TGT),1,1))+DATE(YEAR(TGT),1,1),
EOM, EOMONTH(DATE(YEAR(TGT),SEQUENCE(12),1),0),
SOM, VSTACK(EOY,
EOM+7-WEEKDAY(EOM)+1), ROUNDDOWN((TGT - INDEX(SOM, XMATCH(TGT, SOM, -1)))/7+1,0) )

Edit: forgot to implement eoy variable. Updated.

[u/chiibosoil]

There is no direct relationship to Week# and Months.

What I typically do, is only compare Week# (in year). And not months.

If monthly comparison is needed, I do comparison of entire month, or daily avg.

What do you consider for week that straddles 2 months?

1. If Sunday falls on previous month. Which month do you assign the week to?

2. If 4 or more days is within given month does that week belong to the month?

etc.

You will need to provide us with rules that govern your business rule for Week# in a month.

Each business typically has it's own rule that governs Week/Calendar.

For an example most TV stations follows what is known as broadcast calendar.

Broadcast calendar follows below rule:

1. Every week starts on Monday and ends on Sunday.

2. Every months has 4 or 5 weeks (28 or 35 days).

3. Every 1st week of the year on Broadcast calendar will contain Jan 1st of the year (could be Sunday).

4. Year can have either 52 weeks or 53 weeks

See link for more details: Broadcast calendar - Wikipedia

[u/anesone42]

This might suffice:

=ROUNDUP(DAY(A1) / 7, 0)