CODEWITHSUNDEEP
datetimeexcelweekdayvba
Ashonna
Ashonna

Reputation: 47

Getting Date of Monday of each week

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

Upvotes: 0

Views: 474

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

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)

Upvotes: 3

Gary's Student
Gary's Student

Reputation: 96753

In A1 enter:

1/6/2014

In A2 enter:

=A1 + 7

and copy down

Upvotes: 0

Related Questions