VBA-How to calc the day (number) for the Monday of Current Week
 

Asking the group for assistance on calculating in VBA the Date for Monday of
the current week (Using Monday as the first day of week). This has to work
across years accurately. Ex., 12-29-08, 01-05-09 etc.

I've tried looking at the posts and can't seem to figure it out. I've used
the WeekStart and YearStart functions from the C Pearson website and am
unable to get them to work for what I need.

Tried using the calc below to get the right day. Getting the month and year
is easy but the day is a bit tricky. Guess I'm just doing it wrong.
sDay = DatePart("d", WeekStart(DatePart("ww", Now, vbMonday,
vbFirstFullWeek), CInt(Year(Now))))
--
Edwin Kelly
Houston, TX

VBA-How to calc the day (number) for the Monday of Current Week
 

How about this

sDay = Date - Weekday(Date, vbMonday) + 1


--
__________________________________
HTH

Bob

"Edwin Kelly" wrote in message
...
Asking the group for assistance on calculating in VBA the Date for Monday
of
the current week (Using Monday as the first day of week). This has to
work
across years accurately. Ex., 12-29-08, 01-05-09 etc.

I've tried looking at the posts and can't seem to figure it out. I've
used
the WeekStart and YearStart functions from the C Pearson website and am
unable to get them to work for what I need.

Tried using the calc below to get the right day. Getting the month and
year
is easy but the day is a bit tricky. Guess I'm just doing it wrong.
sDay = DatePart("d", WeekStart(DatePart("ww", Now, vbMonday,
vbFirstFullWeek), CInt(Year(Now))))
--
Edwin Kelly
Houston, TX

VBA-How to calc the day (number) for the Monday of Current Week
 

Please ignore this response if you get less dumb answer:

Sub daymonday()
d = Format(Date, "dddd")
daz = "Monday,Tuesday,Wednesday,Thursday,Friday,Saturday ,Sunday"
s = Split(daz, ",")
For i = 0 To 6
If d = s(i) Then
MsgBox (Date - i)
Exit Sub
End If
Next
End Sub
--
Gary''s Student - gsnu200825


"Edwin Kelly" wrote:

Asking the group for assistance on calculating in VBA the Date for Monday of
the current week (Using Monday as the first day of week). This has to work
across years accurately. Ex., 12-29-08, 01-05-09 etc.

I've tried looking at the posts and can't seem to figure it out. I've used
the WeekStart and YearStart functions from the C Pearson website and am
unable to get them to work for what I need.

Tried using the calc below to get the right day. Getting the month and year
is easy but the day is a bit tricky. Guess I'm just doing it wrong.
sDay = DatePart("d", WeekStart(DatePart("ww", Now, vbMonday,
vbFirstFullWeek), CInt(Year(Now))))
--
Edwin Kelly
Houston, TX

VBA-How to calc the day (number) for the Monday of Current Week
 

Hi,

Here's one way

If (Weekday(Range("A1"), 2)) = 1 Then
mydate = Range("A1")
Else
mydate = Range("A1") - (Weekday(Range("A1"), 2) - 1)
End If


Mike

"Edwin Kelly" wrote:

Asking the group for assistance on calculating in VBA the Date for Monday of
the current week (Using Monday as the first day of week). This has to work
across years accurately. Ex., 12-29-08, 01-05-09 etc.

I've tried looking at the posts and can't seem to figure it out. I've used
the WeekStart and YearStart functions from the C Pearson website and am
unable to get them to work for what I need.

Tried using the calc below to get the right day. Getting the month and year
is easy but the day is a bit tricky. Guess I'm just doing it wrong.
sDay = DatePart("d", WeekStart(DatePart("ww", Now, vbMonday,
vbFirstFullWeek), CInt(Year(Now))))
--
Edwin Kelly
Houston, TX

VBA-How to calc the day (number) for the Monday of Current Wee
 

That was such a simple fix I am embarassed. Thank you so much!
And thanks to Mike H and Gary's Student for your suggestions as well. I did
not try them "just yet" because I was looking for something to easily update
my code with and Bob's was the least instrusive.
Will keep all suggestions ! Thanks again !
--
Edwin Kelly
Houston, TX


"Bob Phillips" wrote:

How about this

sDay = Date - Weekday(Date, vbMonday) + 1


--
__________________________________
HTH

Bob

"Edwin Kelly" wrote in message
...
Asking the group for assistance on calculating in VBA the Date for Monday
of
the current week (Using Monday as the first day of week). This has to
work
across years accurately. Ex., 12-29-08, 01-05-09 etc.

I've tried looking at the posts and can't seem to figure it out. I've
used
the WeekStart and YearStart functions from the C Pearson website and am
unable to get them to work for what I need.

Tried using the calc below to get the right day. Getting the month and
year
is easy but the day is a bit tricky. Guess I'm just doing it wrong.
sDay = DatePart("d", WeekStart(DatePart("ww", Now, vbMonday,
vbFirstFullWeek), CInt(Year(Now))))
--
Edwin Kelly
Houston, TX