ThisCalculator

Everyday

Excel Find Date of Previous Sunday

Updated · Standard formula, shown below

Excel Find Date of Previous Sunday

To find the date of the previous Sunday in Excel, use the formula =A1-WEEKDAY(A1,2)+7 if your date is in cell A1 and you want the Sunday of the same week, or =A1-WEEKDAY(A1,2) to get the most recent Sunday that is on or before the date. This article explains the logic behind these formulas, how to adapt them for different weekdays, and common pitfalls to avoid.

How the WEEKDAY Function Works

Excel’s WEEKDAY function returns a number representing the day of the week for a given date. The second argument, called return_type, controls which day is numbered 1. For this task, you need return_type set to 2, which makes Monday=1, Tuesday=2, and so on through Sunday=7. This is the key to making the formula work reliably regardless of your regional settings.

With return_type=2, the number 7 always means Sunday. So if you subtract the weekday number from the date itself, you move back to the previous Monday. Adding 7 days then brings you forward to the following Sunday. That is exactly what the formula =A1-WEEKDAY(A1,2)+7 does.

The Two Formulas Explained

There are two common interpretations of “previous Sunday”:

  • Sunday of the current week (if the date is a Sunday, return that same date): =A1-WEEKDAY(A1,2)+7
  • Most recent Sunday on or before the date (if the date is a Sunday, return that date, otherwise the Sunday before): =A1-WEEKDAY(A1,2)

The first formula is useful for grouping data into weeks that start on Monday. The second is useful when you want the last Sunday that has already occurred. For example, if A1 contains 2025-04-10 (a Thursday), the first formula returns 2025-04-13 (the upcoming Sunday), while the second returns 2025-04-06 (the previous Sunday).

Adapting for Any Weekday

The same logic works for any day of the week. To find the date of a specific weekday in the current week, use =A1-WEEKDAY(A1,2)+N where N is the target day number (Monday=1, Tuesday=2, …, Sunday=7). To find the most recent occurrence of that weekday, use =A1-WEEKDAY(A1,2)+N-7.

For instance, to find the previous Friday (assuming weeks start on Monday), set N=5. The formula becomes =A1-WEEKDAY(A1,2)+5 for the Friday of the current week, or =A1-WEEKDAY(A1,2)-2 for the most recent Friday.

Common Mistakes and How to Avoid Them

  • Using the wrong return_type: If you omit the second argument, Excel defaults to return_type=1 where Sunday=1. That breaks the formula. Always use 2 for Monday-based weeks.
  • Confusing “previous” with “upcoming”: Decide which Sunday you need before writing the formula. The +7 version gives the Sunday after the date’s Monday, which is often the upcoming Sunday for dates after Monday.
  • Not formatting the result as a date: The formula returns a serial number. If the cell shows a number like 45754, apply a date format to the cell.
  • Edge case: date is already Sunday: With =A1-WEEKDAY(A1,2), if A1 is a Sunday, the result is that same Sunday. If you want strictly the Sunday before, use =A1-WEEKDAY(A1,2)-7 for that case.

Using the Formula with Other Functions

You can combine the Sunday-finding formula with other Excel functions. For example, to count how many entries fall in the week starting on a given Sunday, use SUMPRODUCT with a range of dates:

=SUMPRODUCT((dates>=A1-WEEKDAY(A1,2)+1)*(dates<A1-WEEKDAY(A1,2)+8))

This counts dates from Monday to Sunday. You can also use it with IF to create a flag column, or with AVERAGEIF to average values per week.

Why This Formula Works with Any Date Format

Excel stores dates as serial numbers, where each whole number represents a day. The WEEKDAY function converts that serial number to a day-of-week number. By subtracting the weekday number, you effectively round down to the previous Monday. Adding 7 moves to the next Sunday. Because this relies only on the serial number, it works regardless of whether your dates are entered as text, as dates, or as numbers, as long as they are recognized as dates by Excel.

Frequently Asked Questions

What if my week starts on Sunday instead of Monday?

If you want weeks to start on Sunday, use return_type=1 (Sunday=1). Then the formula for the Sunday of the current week is =A1-WEEKDAY(A1,1)+1, and for the most recent Sunday is =A1-WEEKDAY(A1,1).

How do I get the date of the previous Sunday from today?

Replace A1 with TODAY(). So =TODAY()-WEEKDAY(TODAY(),2) gives the most recent Sunday on or before today.

Can I use this to find the previous Saturday?

Yes. For Saturday, the day number is 6. Use =A1-WEEKDAY(A1,2)+6 for the Saturday of the current week, or =A1-WEEKDAY(A1,2)-1 for the most recent Saturday.

Why does my formula return a number like 45754?

That is the serial number for the date. Select the cell, press Ctrl+1, and choose a date format under the Number tab.

Does this work in Google Sheets?

Yes, the same formulas work in Google Sheets because it uses the same date system and function names.

What if the date is in a different time zone?

Excel dates are timezone-agnostic; they simply store a date and time. The formula uses only the date part, so timezone does not affect the result.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.