Date Calculation

[Kathy]

I need to display the previous Sunday's date on a report
regardless of what today's date is. For example, if I
open the report today, 4/1, the date on the report needs
to read Sunday, 3/28. If the report was opened on
Tuesday, 4/6, the report needs to read Sunday, 4/4. It
always needs to display the previous Sunday. Can someone
tell me how to set this up? Any help you can provide
would be appreciated.

Kathy

 

[Jason Morin]

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

HTH
Jason
Atlanta, GA

 

[Guest]

Jason, THANK YOU! However, if you have a moment, I would
appreciate some insight into "why" the formula works. I
understand the TODAY function. I also know that the
arguments for the WEEKDAY function include the serial
number for the day you want to find, plus a return type.
But I don't get how this formula results in giving me
Sunday's date. If you have time to clear up my confusion,
I would greatly appreciate it. Thanks!

Kathy

 

[Jason Morin]

With =WEEKDAY(A1), you get 1=Sun,2=Mon,3=Tues,etc. So if I
take any date (let's say A1 is Wed, 4/7/04), then WEEKDAY
(A1) = 4. If I subtract 4 from the 4/7/04 (actually
38084), then I get 4/3/04 (38080). This is Saturday. If I
add 1, I'm back to Sunday. This applies for any day of
the week. If I take any date and subtract the return value
of WEEKDAY(...) from that same date, I return to the
previous Sat. Add 1 to get to Sun.

Jason