Obtaining the Previous Sunday

Obtaining the Previous Sunday. To retrieve the date of the previous Sunday in Excel for a specific date, you can use a formula that employs the WEEKDAY function.

Formula for Retrieving the Last Sunday

Follow these instructions:
1. Refer to the cell A1, which contains the original date.
2. Type the minus operator (-).
3. Insert the WEEKDAY function, with the reference to cell A1 in the first argument ().
4. Enter (11) in the second argument, then press enter to obtain the result. 5. Once you hit enter, the formula returns the date of the last Sunday prior to the date in cell A1.

How does this Formula Work?

To comprehend this formula, it’s necessary to understand the WEEKDAY function first. When you use the WEEKDAY function with a date, it returns a number ranging from 1 to 7, depending on the day of the week and the week structure you select. In the second argument (return_type), you must specify a number indicating the first day of the week. If you choose 2 or 11, for example, it begins with Monday.

For example, if it’s Wednesday and the week begins on Monday, the date’s day number will be 3. Therefore, the result displays 3.
When you subtract the result from the WEEKDAY function from the original date, you obtain the previous Sunday’s date.

Retrieving Other Previous Days

You may also retrieve 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)

To modify the first day of the week, adjust the [return_type] argument in the function.

Applying Day Formatting with the Date

When you obtain a date in a cell, the day name is not included. However, you may easily obtain it by changing the format.
1. Select the cell and open the format cells option (Ctrl + 1).
2. Select the custom category, then click on the input bar for the Type.
3. Enter (dd-mmm-yy, dddd) and click OK to apply.

Second Method

You may also utilize CHOOSE with WEEKDAY to obtain the previous Sunday.
=A1-CHOOSE(WEEKDAY(A1),0,1,2,3,4,5,6)
This formula uses WEEKDAY without specifying the [return_type] argument, so it employs Sunday as the default starting day of the week.
If the original date is Wednesday, WEEKNUM returns 4, and CHOOSE returns 3 from the list of numbers. Then, you obtain Sunday’s date by subtracting three days from the original date.
Note: It’s preferable to use the first method to obtain the previous Sunday or any other day.