# How to calculate the week ending date in Excel

Add the days left until Saturday: `=A2+7-WEEKDAY(A2)`. Every date in a Sunday-to-Saturday week gets the same week-ending date.

**Excel:** `=A2+7-WEEKDAY(A2)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Expense date | Week ending |
| 2026-03-04 |  |
| 2026-03-07 |  |
| 2026-03-08 |  |
| 2026-03-13 |  |
| 2026-03-29 |  |
| 2026-12-30 |  |

Formula in B2, filled to B7: `=A2+7-WEEKDAY(A2)`

Result: 3/7/2026; 3/7/2026; 3/14/2026; 3/14/2026; 4/4/2026; 1/2/2027

## How it works

WEEKDAY returns 1 for Sunday through 7 for Saturday, so `7-WEEKDAY(A2)` is the number of days left until Saturday: 3 from a Wednesday, 0 on a Saturday. The year-end week runs into January: December 30, 2026 is in the week ending January 2, 2027.

## Other ways

- `=A2+7-WEEKDAY(A2,2)`: For Monday-to-Sunday weeks that end on Sunday. (The week ends on Sunday.)
- `=A2+MOD(6-WEEKDAY(A2),7)`: For weeks ending on Friday, common for payroll. (The week ends on Friday.)

## Common mistakes

- Use one week definition everywhere. Here a Sunday belongs to the week ending the next Saturday; with Monday-to-Sunday weeks it ends its own week.
- Format the result as a date, or it shows as a number.

## Questions

**How do I sum expenses by week ending date?**

Use this as a helper column and `=SUMIFS(amounts,helper,week_end)`.

**How do I get the week ending date for today?**

`=TODAY()+7-WEEKDAY(TODAY())`.

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-ending-date