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!!!
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!!!
Comments
Post a Comment