How do I return the date of the previous month using VBA?
Article contributed by Beth Melton, Ibby and Dave Rado
VBA contains many Date functions that you can utilize for Date calculations.
For getting information such as the equivalent date in the previous
month, (e.g. to return November 30 if the current date is December 31),
the simplest method is to use the DateAdd function, as follows:
MsgBox DateAdd("m", -1, Date)
The above returns the date using your system's short date format
- i.e. 30/11/2000 (UK, AUS), or 11/30/2000 (US).
To specify the format you want the date to be returned in, use the Format
function. For instance, if the current date is 31 December 2000, the
following will return 30 November 2000:
MsgBox Format(DateAdd("m", -1, Date), "dd mmmm
If you wanted a macro to insert the date in your document, it would
look something like:
Dim mBefore As Date
mBefore = Format(DateAdd("m", -1, Date), "dd mmmm
Here are some more examples of using DateAdd combined with Format:
MsgBox Format(DateAdd("m", -1, "31 July 2000"), "dd mmmm
Returns 30 June 2000
MsgBox Format(DateAdd("m", -1,
"31/1/2000"), "dd mmmm yyyy")
Returns 31 December 1999.
Some useful date functions worth exploring in other contexts include DateDiff,
DatePart, DateSerial, Day, Month, Year,
and IsDate (all covered in help). IsDate (which checks
whether a string is a valid date) is particularly useful for things like
UserForms where you may want to force the user to type a valid date into a