How to Get Previous Sunday in Excel (Formula)

If you want to get the date of the previous Sunday for a particular date in Excel, you need to write a formula using Excel’s WEEKDAY function.

Formula to Get the Last Sunday

Below are the steps you need to follow:

1. Refer to cell A1 where you have the actual date.
2. Type the minus operator (-).
3. Insert the WEEKDAY function; in the first argument (), refer to cell A1.
4. In the second argument, enter (11) and hit enter to get the result.

Once you hit enter, it will return the date of the last Sunday from your original date in cell A1.

How does this Formula Work?

To understand this formula, you need to understand the WEEKDAY function first. When you refer to a date in the WEEKDAY function, it returns a number ranging from 1 to 7 according to the date’s day and the week structure you choose.

In the second argument (return_type), you need to specify a number that tells the function to consider the first day of the week. And if you select (2 or 11), it will start the week from Monday.

For Example: If the day is Wednesday and the week starts on Monday, the day number for the date will be 3. So below you can see we have got 3 in the result.

And in the end, when you have 3 from the WEEKDAY and deduct it from the original date, you get the date for the previous Sunday in the result.

Getting Other Previous Days

You can also get other previous days if you want.

=date-WEEKDAY(date,11)
=date-WEEKDAY(date,12)
=date-WEEKDAY(date,13)
=date -WEEKDAY(date,14)
=date -WEEKDAY(date,15)
=date -WEEKDAY(date,16)
=date-WEEKDAY(date,17)

You need to adjust the [return_type] argument in the function to change the starting day of the week.

Applying Day Formatting with the Date

You won’t get the Day name in a cell when you get a date in a cell. But you can change the formatting to get it quickly.

1. Select the Cell and open the format cells option (Ctrl + 1).
2. Click on the Custom category and click with the (Type) input bar.
3. Enter (dd-mmm-yy, dddd) in it and click OK to apply.

Second Method

You can also use CHOOSE with the WEEKDAY to get the previous Sunday.

=A1-CHOOSE(WEEKDAY(A1),0,1,2,3,4,5,6)

In this formula, we have used WEEKDAY without specifying the [return_type] argument, and it takes the default value for it and uses Sunday as the first day of the week.

If you have Wednesday, WEEKNUM returns 4 in the result, and CHOOSE returns 3 from the list of numbers. And then, in the end, you’ll get the Sunday’s date by deducting 3 days from the original date.

Note: Better to use the first method to get the previous Sunday or any day.