Spreadsheet Practice

How to calculate the week of the month from a date in Excel

Formula (Excel and Google Sheets)

=INT((DAY(A2)+WEEKDAY(EOMONTH(A2,-1)+1)-2)/7)+1

Offset the day number by the weekday of the 1st, then divide by 7: =INT((DAY(A2)+WEEKDAY(EOMONTH(A2,-1)+1)-2)/7)+1.

Result on the example
one result per row in B2, filled down to B7
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.

How it works

Weeks run Sunday to Saturday and week 1 is the one containing the 1st. WEEKDAY of the 1st says how many days of that first week belong to the previous month; adding it to the day number before dividing by 7 puts every date in its calendar row. August 31, 2026 is in week 6 because August 1 is a Saturday.

=INT((DAY(A2)+WEEKDAY(EOMONTH(A2,-1)+1)-2)/7)+1

Result1

  1. A23/1/2026

    The value in A2.

  2. DAY(A2)1

    DAY: Day of the month, 1–31.

    • serial_numberA23/1/2026
  3. -1-1

    Makes the value negative.

  4. EOMONTH(A2,-1)2/28/2026 (46081)

    EOMONTH: Last day of the month, months away.

    • start_dateA23/1/2026
    • months-1-1
  5. EOMONTH(A2,-1)+146082

    The + operator adds.

  6. WEEKDAY(EOMONTH(A2,-1)+1)1

    WEEKDAY: Day of the week as a number; Sunday = 1 by default.

    • serial_numberEOMONTH(A2,-1)+146082
  7. DAY(A2)+WEEKDAY(EOMONTH(A2,-1)+1)2

    The + operator adds.

  8. DAY(A2)+WEEKDAY(EOMONTH(A2,-1)+1)-20

    The - operator subtracts.

  9. DAY(A2)+WEEKDAY(EOMONTH(A2,-1)+1)-2)/70

    The / operator divides.

  10. INT((DAY(A2)+WEEKDAY(EOMONTH(A2,-1)+1)-2)/7)0

    INT: Rounds down to the nearest integer.

    • numberDAY(A2)+WEEKDAY(EOMONTH(A2,-1)+1)-2)/70
  11. INT((DAY(A2)+WEEKDAY(EOMONTH(A2,-1)+1)-2)/7)+11

    The + operator adds.

In Google Sheets

The same formula works in Google Sheets: =INT((DAY(A2)+WEEKDAY(EOMONTH(A2,-1)+1)-2)/7)+1. We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=ROUNDUP((DAY(A2)+WEEKDAY(A2-DAY(A2)+1)-1)/7,0)1, 2, 5…The same count with ROUNDUP.
=ROUNDUP(DAY(A2)/7,0)1, 2, 5…Different resultCounts 7-day blocks from the 1st (days 1 to 7 are week 1), ignoring weekdays. Week 1 is always days 1 to 7.

Common mistakes

  • A month can span 6 calendar weeks, so leave room for week 6.
  • For Monday-start weeks use type 2: =INT((DAY(A2)+WEEKDAY(EOMONTH(A2,-1)+1,2)-2)/7)+1.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate the week of the month from a date

In B2, return the week of the month (Sunday-to-Saturday weeks, week 1 contains the 1st) for the date in A2. Fill down to B7.

Questions

Is there a function for this?

Not directly. With WEEKNUM it is =WEEKNUM(A2)-WEEKNUM(EOMONTH(A2,-1)+1)+1.

How do I label it "Week 2 of March"?

="Week "&B2&" of "&TEXT(A2,"mmmm").

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.