How to Find the Start of the Week for a Date in Excel

Group dates by week: find the Monday or Sunday that starts each date’s week, and the Friday that ends it.

You will learn to

• Return the Monday of a date’s week
• Return the Sunday of a date’s week
• Return the week-ending Friday

How it works

To group dates into weeks, convert each date to the first day of its week. For Monday-start weeks use =B2-WEEKDAY(B2,2)+1: WEEKDAY with return type 2 is 1 on Monday, so Mondays stay put and every other day steps back to its Monday. For Sunday-start weeks use =B2-WEEKDAY(B2)+1.

Timesheets often use a week-ending date instead. =B2+MOD(6-WEEKDAY(B2),7) moves any date forward to the next Friday (or keeps it if it is already a Friday). Format the results as dates and they make perfect PivotTable groups.

=B2-WEEKDAY(B2,2)+1