VBA get Previous Friday Date with vbSunday, vbMonday, vbFriday, vbFriday, vbFriday, 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 Friday. This tutorial I am going to explain how to find last or previous Friday 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.
Get Previous Friday Date using VBA Functions
Let us see different statements to find Date of Last or previous Friday using Excel VBA. Lets assume today is 3rd December 2018(03/12/2018). The below macro example produces same result. i.e 30th November 2018(30/11/2018).
'Previous Friday Date using Excel VBA Functions Sub VBA_Find_previous_Friday() Dim dPrevious_Friday As Date dPrevious_Friday = DateAdd("ww", -1, Now - (Weekday(Now, vbSunday) - 6)) Debug.Print dPrevious_Friday 'Or dPrevious_Friday = DateAdd("ww", -1, Now - (Weekday(Now, vbMonday) - 5)) Debug.Print dPrevious_Friday 'Or dPrevious_Friday = DateAdd("ww", -1, Now - (Weekday(Now, vbTuesday) - 4)) Debug.Print dPrevious_Friday 'Or dPrevious_Friday = DateAdd("ww", -1, Now - (Weekday(Now, vbWednesday) - 10)) Debug.Print dPrevious_Friday 'Or dPrevious_Friday = DateAdd("ww", -1, Now - (Weekday(Now, vbThursday) - 9)) Debug.Print dPrevious_Friday 'Or dPrevious_Friday = DateAdd("ww", -1, Now - (Weekday(Now, vbFriday) - 8)) Debug.Print dPrevious_Friday 'Or dPrevious_Friday = DateAdd("ww", -1, Now - (Weekday(Now, vbSaturday) - 7)) Debug.Print dPrevious_Friday MsgBox "If today's date is 12/03/2018 " & vbCrLf & " then previous Friday's date is " _ & Format(dPrevious_Friday, "DD MMMM YYYY"), vbInformation, "Previous Friday 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.
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