Finding the first DAY of the week of a given week.

[Mark]

Hi All,

I’m building a between statement and need to find the first and last day of
a given week.
The user will select a date from an active X calendar. The day doesn’t
mater, since they are picking a week. The week they select I would like to
use as the start and end date. I need the start to be Sunday 00:00:01 and
the end to be 1159:59 for the week they select.

I’m using datepart to get the week

vWeek = DatePart("ww", cal1.Value, vbSunday, vbFirstJan1)
vYear = DatePart("yyyy", cal1.Value, vbSunday, vbFirstJan1)

In this example my values are vWeek = 47 and vYear is 2009. How can I get
it to see the first day of this week which is 11/08/09 and 11/14/09.

I tried to use the dateserial, but it uses month instead of week.

Thanks for any info,
Mark

 

[Stuart McCall]

Hi All,

I'm building a between statement and need to find the first and last day
of
a given week.
The user will select a date from an active X calendar. The day doesn't
mater, since they are picking a week. The week they select I would like
to
use as the start and end date. I need the start to be Sunday 00:00:01 and
the end to be 1159:59 for the week they select.

I'm using datepart to get the week

vWeek = DatePart("ww", cal1.Value, vbSunday, vbFirstJan1)
vYear = DatePart("yyyy", cal1.Value, vbSunday, vbFirstJan1)

In this example my values are vWeek = 47 and vYear is 2009. How can I get
it to see the first day of this week which is 11/08/09 and 11/14/09.

I tried to use the dateserial, but it uses month instead of week.

Thanks for any info,
Mark

This ought to do exactly what you want:

http://www.smccall.demon.co.uk/Dates.htm#FirstMonthDay

 

[Mark]

Stuart,

How would I do it for a given week though? The dateSerial only allows me to
select the month not week (I think).

 

[Mark]

I found it.

Was able to use this:

FirstDayOfWeek = DateAdd("d", 1 - Weekday(cal1.Value), cal1.Value)