Excel VBA get x days ago

A quick code snippet to get yesteday's date or a week before from the date the macro is executed.
 

Sub xDate()

Dim yesterdaysdate, aweekbeforefromtoday

aweekbeforefromtoday = Format(DateAdd("D", -Weekday(Date) - 1, Date), "dd-mmm-yy")

yesterdaysdate = Format(DateAdd("D", -1, Date), "dd-mmm-yy")

MsgBox yesterdaysdate
MsgBox aweekbeforefromtoday

End Sub

A variable can be set to easily change the desired number of days before the current date. 
Example:
xMinusDays = -2
yesterdaysdate = Format(DateAdd("D", xMinusDays, Date), "dd-mmm-yy")

xMinusDays is set to -2 will return 2 days before the current date

If  xMinusDays is set to -5 then it will return 5 days before from the current date

//===========================

VBA code below will return x days ago depends on the value set on the parameter.

To implement in VBA code:

=============================

Sub xDate()
Dim xMinusDays
xMinusDays = -365
Dim xdays

xdays = Format(DateAdd("D", xMinusDays, Date), "dd-mmm-yy")

MsgBox xdays

End Sub

=============================

xdays will return a year before the current date, since 1 year is 365 days except on leap year.


MSDN link to dig more about date:
http://msdn.microsoft.com/en-us/library/3eaydw6e.aspx


Click labels below for more VBA tips..


Cheers!!!