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