Display value of the date of LAST Sunday?

[CTOROCK]

How can I have the date of the last Sunday shown in a field.
If today's date is 3/26, I would like it to caluculate the date of the most recently past Sunday which would be 3/23.
Can anyone help? Thanks! "The greatest risk, is not taking one."

 

[xlbo]

this should do the trick:
=TODAY()-(WEEKDAY(TODAY())-1)
OR, if you have a specific date you want to find the previous sunday for, use
=A1-(WEEKDAY(A1)-1)
where A1 holds the date Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Zathras]

As a worksheet formula you can use

  =F3-WEEKDAY(F3)+1

In a macro you can use

Option Explicit

Sub test()
  MsgBox GetSunday("3/28/03")
End Sub

Function GetSunday(ADate As Date) As Date
  GetSunday = ADate - WeekDay(ADate) + 1
End Function

 

[xlbo]

You got different regional settings to me Zathras ???
It's definately -1 for me..... Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[RobBroekhuis]

A matter of parentheses...
Rob

 

[Zathras]

Fascinating, -1 gives me Friday!

 

[mikevh]

YourDate - WeekDay(YourDate) + 1

where "YourDate" is your date.

?#3/26/2003# - weekday(#3/26/2003#) + 1
3/23/03

 

[xlbo]

Rob - nail...head....hit
then
my head.....wall....hit

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[CTOROCK]

Thanks a lot guys,
=TODAY()-(WEEKDAY(TODAY())-1)

did it!!!
"The greatest risk, is not taking one."