Find last Sunday
 

Hi - I'm trying to display data based on the last complete workweek, i.e.
today, the data displayed should be for the week ending Oct. 8. Monday, I
want to display data for the week ending Oct 15. Short of a messy nested 'if'
formula, is there a way to determine the date of the most recent Sunday past?

Thanks

Answer: Find last Sunday
 

Finding the Date of the Most Recent Sunday Past

To find the date of the most recent Sunday past, follow these steps:

1. In a blank cell, enter the following formula:
   Formula:

   ---

   =TODAY()-WEEKDAY(TODAY(),2)+1 


   ---

2. Press Enter. This will give you the date of the most recent Sunday past.
3. You can now use this date to display data for the week ending on that Sunday.

For example, if today is October 12, 2021, the formula will return October 10, 2021, which is the most recent Sunday past. You can then use this date to display data for the week ending on October 10, 2021.

Find last Sunday
 

=TODAY()-WEEKDAY(TODAY())+1

Format as Date

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Dean F" wrote in message ...
| Hi - I'm trying to display data based on the last complete workweek, i.e.
| today, the data displayed should be for the week ending Oct. 8. Monday, I
| want to display data for the week ending Oct 15. Short of a messy nested 'if'
| formula, is there a way to determine the date of the most recent Sunday past?
|
| Thanks

Find last Sunday
 

=TODAY()-WEEKDAY(TODAY())+1
=A2-WEEKDAY(A2)+1

HTH
Kostis Vezerides

Find last Sunday
 

may i know why 1 is used in the formula plzzzzzzzzzz

"vezerid" wrote:

=TODAY()-WEEKDAY(TODAY())+1
=A2-WEEKDAY(A2)+1

HTH
Kostis Vezerides

Find last Sunday
 

For the answer as to why +1 is used, I suggest you look in Excel help for
the WEEKDAY function.

The question, of course, remains as to what the OP would have wanted for
"last Sunday" if today is Sunday. He seemed to be including only the
working week as possible inputs, but if he wants a Sunday date to point back
at the previous Sunday, the formula may need to be expanded a little, such
as
=IF(WEEKDAY(A2)=1,A2-7,A2-WEEKDAY(A2)+1)
or, more obscurely,
=A2-1-MOD(WEEKDAY(A2)+5,7)
--
David Biddulph

"Naveed Pathan" wrote in message
...
may i know why 1 is used in the formula plzzzzzzzzzz

"vezerid" wrote:

=TODAY()-WEEKDAY(TODAY())+1
=A2-WEEKDAY(A2)+1

HTH
Kostis Vezerides