#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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)+
  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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,440
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 751
Default Find last Sunday

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

HTH
Kostis Vezerides

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default 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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 618
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find the first Sunday for a given year? Eutrapelia Excel Worksheet Functions 2 January 20th 06 07:07 PM
Find and Replace blakrapter Excel Worksheet Functions 3 December 15th 05 01:25 AM
Code needed to find records from bottom up Andy Excel Discussion (Misc queries) 4 December 5th 05 04:27 AM
find a cells from a range of cell kelvintaycc Excel Worksheet Functions 2 April 2nd 05 07:20 PM
Excel has a "Find Next" command but no "Find Previous" command. Michael Fitzpatrick Excel Discussion (Misc queries) 2 January 11th 05 12:45 AM


All times are GMT +1. The time now is 03:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"