How to Calculate the Week Ending Date in Excel

To find the week ending date in Excel, add the days that are left until the last day of the week. For a week that ends on Sunday, the formula is =A2+7-WEEKDAY(A2,2). This guide is for anyone filing timesheets, weekly reports or payroll by "week ending" date, and it covers Sunday, Saturday and Friday week ends, the week start date, and weekly totals.

How Excel works out the week ending date#

Excel stores every date as a whole number: one more for each day. So adding 3 to a date moves it 3 days forward. The date basics lesson explains this in more detail.

WEEKDAY tells you where a date sits in its week. With return type 2, it numbers the days from Monday = 1 to Sunday = 7:

=WEEKDAY(date, 2)

If a date is a Wednesday, WEEKDAY gives 3, and there are 7 − 3 = 4 days left until Sunday. Adding those 4 days gives the Sunday that ends the week. That is all the formula does. The guide to the WEEKDAY function lists all the return types.

Week ending Sunday: =A2+7-WEEKDAY(A2,2)#

For a week that runs from Monday to Sunday, type this in C2 and fill down (Windows: Ctrl+D; Mac: ⌘D):

=A2+7-WEEKDAY(A2,2)

A Sunday adds 0 days, so it returns itself. That is what you want for a week ending date: Sunday is the last day of its own week. Every date from Monday 2 to Sunday 8 November gives 8 November.

Week ending Saturday or Friday#

Change the formula to match the day your week ends.

Week ending Saturday (a Sunday to Saturday week). WEEKDAY's default numbering, with no return type, runs from Sunday = 1 to Saturday = 7:

=A2+7-WEEKDAY(A2)

Wednesday 4 November gives Saturday 7 November, and Sunday 8 November gives Saturday 14 November.

Week ending Friday (a Saturday to Friday week, common for payroll). Friday is day 6 in the default numbering, and MOD works out the days left until it:

=A2+MOD(6-WEEKDAY(A2),7)

Wednesday 4 November gives Friday 6 November. Saturday 7 November gives Friday 13 November, because MOD(6 − 7, 7) is 6. MOD returns the remainder after dividing by 7, and it is never negative here, so the formula never goes backwards.

The MOD pattern works for any end day: =A2+MOD(d-WEEKDAY(A2),7), where d is 1 for Sunday, 2 for Monday, up to 7 for Saturday. In Excel 2010 and later, return type 16 (Saturday = 1 to Friday = 7) gives a shorter Friday version: =A2+7-WEEKDAY(A2,16).

How to get the week start date#

The week start date is the other end of the same week. For a week that starts on Monday:

=A2-WEEKDAY(A2,2)+1

Wednesday 4 November gives Monday 2 November. For a week that starts on Sunday, use =A2-WEEKDAY(A2)+1. The week start is always 6 days before the week ending date, so once you have one, the other is just +6 or -6.

To find the next Monday after a date instead of the start of its week, see how to find the next Monday or any weekday. For month boundaries rather than week boundaries, see the first and last day of the month.

Show the week ending date as a date or a label#

If the result shows a number such as 46334, the cell is formatted as General. Press Ctrl+1 (Mac: ⌘1), choose Date and pick a format. Or choose Custom and type ddd d mmm yyyy to see Sun 8 Nov 2026.

For a heading or report label, build the text with TEXT:

="Week ending "&TEXT(A2+7-WEEKDAY(A2,2),"d mmm yyyy")

The result is Week ending 8 Nov 2026. Keep the plain date in its own column for formulas, because a text label can't be compared or added to. The EDATE, EOMONTH and WEEKDAY lesson practises these date functions together.

Total hours by week ending date#

A week ending column turns any daily log into weekly totals. With dates in A2:A6, hours in B2:B6 and the week ending formula in C2:C6, list each week ending date in E2:E3 and use SUMIF:

=SUMIF($C$2:$C$6,E2,$B$2:$B$6)

The week ending 8 November has 7.5 + 8 = 15.5 hours, and the week ending 15 November has 6 + 8 + 4 = 18. Because both columns hold real dates, SUMIF matches them exactly. This is the core of a weekly timesheet in Excel. For other ways to group by week, including SUMIFS with date ranges and pivot tables, see how to sum by week in Excel.

Common mistakes with week ending formulas#

1. Using the wrong return type. =A2+7-WEEKDAY(A2) (no return type) ends on Saturday, not Sunday. Add ,2 for Sunday.
2. Dates stored as text. WEEKDAY returns #VALUE! for text it can't read as a date. Use real dates, typed in your usual format or built with DATE.
3. Times included. A date with a time, such as 4 Nov 2026 14:30, gives a week ending date with 14:30 attached, which won't match in SUMIF. Wrap the date in INT first: =INT(A2)+7-WEEKDAY(A2,2).
4. Using TODAY() in a log. =TODAY()+7-WEEKDAY(TODAY(),2) changes every day. That is right for "this week", but for a record, point at the entry date instead.

Practise it#

Try the exercise on this page: one WEEKDAY formula turns a timesheet date into its week ending Sunday. Then practise EDATE, EOMONTH and WEEKDAY in a live spreadsheet, where you type each formula and it checks it instantly.