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

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`.

**Excel:** `=INT((DAY(A2)+WEEKDAY(EOMONTH(A2,-1)+1)-2)/7)+1` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Date | Week of month |
| 2026-03-01 |  |
| 2026-03-08 |  |
| 2026-03-31 |  |
| 2026-04-04 |  |
| 2026-04-05 |  |
| 2026-08-31 |  |

Formula in B2, filled to B7: `=INT((DAY(A2)+WEEKDAY(EOMONTH(A2,-1)+1)-2)/7)+1`

Result: 1; 2; 5; 1; 2; 6

## 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.

## Other ways

- `=ROUNDUP((DAY(A2)+WEEKDAY(A2-DAY(A2)+1)-1)/7,0)`: The same count with ROUNDUP.
- `=ROUNDUP(DAY(A2)/7,0)`: Counts 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`.

## 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")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/week-of-the-month