Spreadsheet Practice

How to calculate the week ending date in Excel

Formula (Excel and Google Sheets)

=A2+7-WEEKDAY(A2)

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

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

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.

=A2+7-WEEKDAY(A2)

Result46088

  1. A23/4/2026

    The value in A2.

  2. A2+746092

    The + operator adds.

  3. WEEKDAY(A2)4

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

    • serial_numberA23/4/2026
  4. A2+7-WEEKDAY(A2)46088

    The - operator subtracts.

In Google Sheets

The same formula works in Google Sheets: =A2+7-WEEKDAY(A2). 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
=A2+7-WEEKDAY(A2,2)3/8/2026, 3/8/2026, 3/8/2026…Different resultFor Monday-to-Sunday weeks that end on Sunday. The week ends on Sunday.
=A2+MOD(6-WEEKDAY(A2),7)3/6/2026, 3/13/2026, 3/13/2026…Different resultFor 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.

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

In B2, return the Saturday that ends the week of each expense date, and fill down to B7.

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()).

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.