Previous Monday Date using VBA Functions

Previous Monday Date

Previous Monday Date:

VBA get Previous Monday Date with vbSunday, vbMonday, vbTuesday, vbWednesday, vbThursday, vbFriday and vbSaturday in Excel using VBA Functions. We run so many reports manually or by scheduling reports as part of our daily activities. Sometimes we want to run or schedule reports on specific day in a week.

Lets assume you want to run reports on Monday. This tutorial I am going to explain how to find last or previous Monday Date using VBA Formulas. We run report based on weekday date, If the output word matches with specified weekday then continue run the report, otherwise it ignores.

Previous Monday

Get Previous Monday Date using VBA Functions

Let us see different statements to find Date of Last or previous Monday using Excel VBA. Lets assume today is 3rd December 2018(03/12/2018). The below macro example produces same result. i.e 26th November 2018(26/11/2018).

'Previous Monday Date using Excel VBA Functions
Sub VBA_Find_previous_Monday()

     Dim dPrevious_Monday As Date
     
    dPrevious_Monday = DateAdd("ww", -1, Now - (Weekday(Now, vbSunday) - 2))
    Debug.Print dPrevious_Monday
    'Or
    dPrevious_Monday = DateAdd("ww", -1, Now - (Weekday(Now, vbMonday) - 1))
    Debug.Print dPrevious_Monday
    'Or
    dPrevious_Monday = DateAdd("ww", -1, Now - (Weekday(Now, vbTuesday) - 7))
    Debug.Print dPrevious_Monday
    'Or
    dPrevious_Monday = DateAdd("ww", -1, Now - (Weekday(Now, vbWednesday) - 6))
    Debug.Print dPrevious_Monday
    'Or
    dPrevious_Monday = DateAdd("ww", -1, Now - (Weekday(Now, vbThursday) - 5))
    Debug.Print dPrevious_Monday
    'Or
    dPrevious_Monday = DateAdd("ww", -1, Now - (Weekday(Now, vbFriday) - 4))
    Debug.Print dPrevious_Monday
    'Or
    dPrevious_Monday = DateAdd("ww", -1, Now - (Weekday(Now, vbSaturday) - 3))
    Debug.Print dPrevious_Monday
    
    MsgBox "If today's date is 12/03/2018 " & vbCrLf & " then previous Monday's date is " _
    & Format(dPrevious_Monday, "DD MMMM YYYY"), vbInformation, "Previous Monday Date"

End Sub

In the above macro we have used different vba functions like DateAdd, Now, Weekday and Format in multiple statements. You can view immediate window, it displays same output.

Note: Use Shortcut ‘Ctrl + G’ for an Immediate window.

Output Screenshot:

Please find the output screenshot of above specified macros.

Previous Monday Date

Related VBA Functions used in this article:

We have used DateAdd, Now, Weekday and Format VBA functions. Click on below specified links to learn more about VBA functions.

VBA DateAdd Function VBA Now Function VBA WeekDay Function VBA Format Function

Leave a Comment

Your email address will not be published. Required fields are marked *