How to Get Previous Sunday in Excel (Formula)

- Written by Puneet

excelchamps-free-courses-puneet-gogia

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.

get-previous-sunday

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.
formula-to-get-last-sunday

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

returns-the-last-sunday-date

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.

understand-weekday-function

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.

result-from-weekday

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.

date-of-previous-sunday

Getting Other Previous Days

You can also get other previous days if you want.

get-other-previous-days
=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.
day-formatting-with-date

Second Method

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

second-method-to-get-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.

Get the Excel File

Last Updated: April 30, 2024

Leave a Comment