Current Monday Date of This Week using VBA Functions

Current Monday Date using Excel VBA Functions Now, DateAdd,Format and WeekDay. We have different methods and ways to find Current Week Monday date. You can find below two specified macros or procedures to find Current week Monday date of this week using VBA functions. i.e Date, DateAdd, WeekDay and Format.

We are trying to find current week Monday date based on current or today’s date. Lets say today’s date is 13th Dec 2018 and weekday is Thursday then current date of Monday should be 10th Dec 2018 and weekday is Monday. In this tutorial we have explained three methods to find Current week Monday date. To avoid using time, you can replace Date with Now. Then there is no difference in output.

Method1: Get Current Week Monday Date using VBA Functions

Let us see the first method to find current week Monday date of this week.

'Method 1: Current Monday Date using Excel VBA Functions
Sub VBA_Find_Current_Monday_Method1()
    
    Dim dCurrent_Monday As Date

    dCurrent_Monday = DateAdd("d", 1 - Weekday(Date, vbMonday), Date)

    MsgBox "If today's date is '" & Format(Now, "DD MMM YYYY") & "' then" & vbCrLf & _
    "Current Monday Date is : " & Format(dCurrent_Monday, "DD MMM YYYY"), vbInformation, "Current Week Monday Date"
    
End Sub

Method2: Get Current Week Monday Date using VBA Functions

Here is the second method to find Current week Monday date of this week.

'Method 2: Current Monday Date using Excel VBA Functions
Sub VBA_Find_Current_Monday_Method2()
    
    'Variable Declaration
    Dim dCurrent_Monday As Date
    
    dCurrent_Monday = Date - Weekday(Date, vbMonday) + 1

    MsgBox "If today's date is '" & Format(Now, "DD MMM YYYY") & "' then" & vbCrLf & _
    " Current Monday Date is : " & Format(dCurrent_Monday, "DD MMM YYYY"), vbInformation, "Current Week Monday Date"
    
End Sub

Method3: Get Current Week Monday Date using VBA Functions

Let us see the third method to find Current week Monday date of this week.

'Method 3: Current Monday Date using Excel VBA Functions
Sub VBA_Find_Current_Monday_Method3()
    
    'Variable Declaration
    Dim dCurrent_Monday As Date
    
    dCurrent_Monday = 2 - Weekday(Date) + Date

    MsgBox "If today's date is '" & Format(Now, "DD MMM YYYY") & "' then" & vbCrLf & _
    " Current Monday Date is : " & Format(dCurrent_Monday, "DD MMM YYYY"), vbInformation, "Current Week Monday Date"
    
End Sub

Output screenshot: You can find following output screenshot of above specified macros.

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog