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

[Edwin Kelly]

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))))

 

[Bob Phillips]

How about this

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

 

[Gary''s Student]

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

 

[Mike H]

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]

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

How about this

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