Determine 1st week date of the week

[Jasper Recto]

I need to figure out the Sunday date of any given day.

For example, today is 7/21/08. This is the 'WEEK OF: 7/20/08'

Or if the date given is 3/20/08 then the it is the 'WEEK OF: 3/16/08'

Any ideas?

Thanks,
Jasper

 

[Klatuu]

=DateAdd("d", vbSunday - DatePart("w", BaseDate), BaseDate)

Where BaseDate is the day you want to test with. For example:
=DateAdd("d", vbSunday - DatePart("w", #7/21/08#), #7/21/08#)
Will return 7/20/2008

 

[fredg]

I need to figure out the Sunday date of any given day.

For example, today is 7/21/08. This is the 'WEEK OF: 7/20/08'

Or if the date given is 3/20/08 then the it is the 'WEEK OF: 3/16/08'

Any ideas?

Thanks,
Jasper

=DateAdd("d",-Weekday(#3/20/2008#)+1,#3/20/2008#)

 

[Keith]

Hi Jasper:

This should do it for you:

Public Function GetSundayDate(ADate As Date) As Date
Dim DayNumber As Byte

'Get the number of the day in question (Sunday = 1)
DayNumber = Weekday(ADate)
If DayNumber > 1 Then
GetSundayDate = DateAdd("d", -(DayNumber - 1), ADate)
Else
GetSundayDate = ADate
End If
End Function

You'll need to add in your own error handling, etc.

Keith

 

[Krzysztof Pozorek [MVP]]

(...)

For example, today is 7/21/08. This is the 'WEEK OF: 7/20/08'

Or if the date given is 3/20/08 then the it is the 'WEEK OF: 3/16/08'

Any ideas?

= [date1] - WeekDay([date1], 2)

K.P.