VBA Code for ‘Last Week’s Saturday’ – & other dates!

These codes come in handy for me all of the time – Whenever I need to automatically save a report with the Week Ending Date, or if I need to calculate something from a SQL query that needs a specific date, I chuck one of these codes. I thought it might come in handy for others, so here you go!

Last week’s: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.

Open a new Excel workbook and save it as a Macro-Enabled workbook (make sure there aren’t any spaces in the name!

Press ALT + F11 to open the VBA Editor and paste in the below code in full:

(The comments in the code will let you know what to do next! – If you’re after a way to quickly comment/uncomment code in VBA check out my post HERE!)

Sub LastWeekDate()

'GIRLVSDATA.COM
' ____________________________________________
' | Brooke Clarke / girlvsdata.com / Nov 2017 |
' |                                           |
' |           ** ¯\_(^-^)_/¯ **               |
' |___________________________________________|
'
'First check the arrangement of your week.
'This will mean there are no suprises when you run the code that includes the "vb"Weekday formula

'**
'**UNCOMMENT THIS CODE & PRESS F5 TO RUN:**
'MsgBox _
'"Monday = " & vbMonday & Chr(10) & _
'"Tuesday = " & vbTuesday & Chr(10) & _
'"Wednesday = " & vbWednesday & Chr(10) & _
'"Thursday = " & vbThursday & Chr(10) & _
'"Friday = " & vbFriday & Chr(10) & _
'"Saturday = " & vbSaturday & Chr(10) & _
'"Sunday = " & vbSunday
'**

 

'My code returns:
'Monday = 2
'Tuesday = 3
'Wednesday = 4
'Thursday = 5
'Friday = 6
'Saturday = 7
'Sunday = 1
'Which means my week runs Sunday-Saturday

'These formulas calculate the dates of each weekday last week.
LastSunday = Format(DateAdd("ww", -1, Now - (Weekday(Now, 1) - vbSunday)), "dd/mm/yyyy")
LastMonday = Format(DateAdd("ww", -1, Now - (Weekday(Now, 1) - vbMonday)), "dd/mm/yyyy")
LastTuesday = Format(DateAdd("ww", -1, Now - (Weekday(Now, 1) - vbTuesday)), "dd/mm/yyyy")
LastWednesday = Format(DateAdd("ww", -1, Now - (Weekday(Now, 1) - vbWednesday)), "dd/mm/yyyy")
LastThursday = Format(DateAdd("ww", -1, Now - (Weekday(Now, 1) - vbThursday)), "dd/mm/yyyy")
LastFriday = Format(DateAdd("ww", -1, Now - (Weekday(Now, 1) - vbFriday)), "dd/mm/yyyy")
LastSaturday = Format(DateAdd("ww", -1, Now - (Weekday(Now, 1) - vbSaturday)), "dd/mm/yyyy")

 

'Now we check what they return:
'--**
'--**UNCOMMENT THIS CODE & PRESS F5 TO RUN:**
'LastWeek = _
'"Sunday = " & LastSunday & Chr(10) & _
'"Monday = " & LastMonday & Chr(10) & _
'"Tuesday = " & LastTuesday & Chr(10) & _
'"Wednesday = " & LastWednesday & Chr(10) & _
'"Thursday = " & LastThursday & Chr(10) & _
'"Friday = " & LastFriday & Chr(10) & _
'"Saturday = " & LastSaturday & Chr(10)
'MsgBox LastWeek
'--**

 

'So now you can use these in all sorts of fun formulas
'Like maybe you want to automatically save a report with the Week Ending date
'--**
'--**UNCOMMENT THIS CODE & PRESS F5 TO RUN:**
ReportName = "Sales_Report_" & LastSaturday & ".xlsx"
MsgBox ReportName
'--**

'Or maybe you just want it in a Pop up box with full date formatting
'--**
'--**UNCOMMENT THIS CODE & PRESS F5 TO RUN:**
Alert = "Last Friday was " & Format(LastFriday, "dd mmmm 2017")
MsgBox Alert
'--**

'The only limit is your imagination!

End Sub

Thanks for reading, and let me know if you have any questions or comments!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s