Previous Sunday Date using VBA Functions

Previous Sunday Date

VBA get Previous Sunday Date with vbSunday, vbMonday, vbSunday, vbSunday, vbSunday, vbSunday and vbSunday 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 Sunday. This tutorial I am going to explain how to find last or previous Sunday 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 Sunday Date

Method 1:Previous Sunday Date using VBA Functions

Let us see below example to find Last sunday date using VBA functions. In the below procedure we used multiple VBA functions i.e. DateAdd, WeekDay ,Now and Format.

Sub VBA_Find_previous_Sunday_Method1()
    
    Dim dPrevious_Sunday As Date

    dPrevious_Sunday = DateAdd("d", -Weekday(Now) + 1, Now)

    MsgBox "If today's date is '" & Format(Now, "DD MMM YYYY") & "' then" & vbCrLf & _
    " Previous Sunday Date is : " & Format(dPrevious_Sunday, "DD MMM YYYY"), vbInformation, "Previous Sunday Date"
    
End Sub

Output Screenshot:
Previous Sunday Date

Method 2:Last Sunday Date using VBA Functions

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

'Previous Sunday Date using Excel VBA Functions
Sub VBA_Find_previous_Sunday_Method2()

     Dim dPrevious_Sunday As Date
     
    dPrevious_Sunday = DateAdd("ww", -1, Now - (Weekday(Now, vbSunday) - 8))
    Debug.Print dPrevious_Sunday
    'Or
    dPrevious_Sunday = DateAdd("ww", -1, Now - (Weekday(Now, vbMonday) - 7))
    Debug.Print dPrevious_Sunday
    'Or
    dPrevious_Sunday = DateAdd("ww", -1, Now - (Weekday(Now, vbTuesday) - 6))
    Debug.Print dPrevious_Sunday
    'Or
    dPrevious_Sunday = DateAdd("ww", -1, Now - (Weekday(Now, vbWednesday) - 12))
    Debug.Print dPrevious_Sunday
    'Or
    dPrevious_Sunday = DateAdd("ww", -1, Now - (Weekday(Now, vbThursday) - 11))
    Debug.Print dPrevious_Sunday
    'Or
    dPrevious_Sunday = DateAdd("ww", -1, Now - (Weekday(Now, vbFriday) - 10))
    Debug.Print dPrevious_Sunday
    'Or
    dPrevious_Sunday = DateAdd("ww", -1, Now - (Weekday(Now, vbSaturday) - 9))
    Debug.Print dPrevious_Sunday
    
    MsgBox "If today's date is 12/03/2018 " & vbCrLf & " then previous Sunday's date is " _
    & Format(dPrevious_Sunday, "DD MMMM YYYY"), vbInformation, "Previous_Sunday_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.

Output Screenshot: Please find the output screenshot of above specified macros.

Previous Sunday Date

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

Leave a Comment