Getting Date of Monday of each week

Question

I want to get the date of Monday for each week. I am using that date to open a folder that is created every Monday and is named based on the date. I tried using weekday() function. But I am not sure how to proceed. Thanks for the help! This is what i have tried, it works. But I juts wanted to know if there is more efficient way to do it?

Sub test()

Dim myday As Integer
Dim mydate As Date
mydate = Date
myday = Weekday(Date, vbMonday)

If myday = 1 Then

mydate = Date

ElseIf myday = 2 Then
mydate = DateAdd("d", -1, Date)

ElseIf myday = 3 Then
mydate = DateAdd("d", -2, Date)

ElseIf myday = 4 Then
mydate = DateAdd("d", -3, Date)

ElseIf myday = 5 Then
mydate = DateAdd("d", -4, Date)

End If

End Sub

What seems to be the problem? Your code looks good. Can you describe how you are trying to use it, and what your specific problem/challenge is? — David Zemens, Jun 03 '14 at 17:15
There is nothing wrong. I just wanted to know if there is better way to do this. — Ashonna, Jun 03 '14 at 17:28

score 3 · Accepted Answer · answered Jun 03 '14 at 17:15

If DT is any date, and if you want to return the same date if DT is a Monday, then, using VBA:

Function FirstMonday(DT As Date) As Date
    FirstMonday = DT - Weekday(DT, vbMonday) + 1
End Function

Or, on your worksheet, with the Date in A1:

=A1+1-WEEKDAY(A1-1)

score 0 · Answer 2 · answered Jun 03 '14 at 16:55

0

In A1 enter:

1/6/2014

In A2 enter:

=A1 + 7

and copy down

answered Jun 03 '14 at 16:55

Gary's Student

95,722
10
59
99

Getting Date of Monday of each week

2 Answers2