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 yyyy")
If you wanted a macro to insert the date in your document, it would look something like:
Sub PrevMonth()
Dim mBefore As
Date
mBefore = Format(DateAdd("m", -1, Date), "dd mmmm yyyy")
Selection.InsertBefore mBefore
End Sub
Here are some more examples of using DateAdd combined with Format:
MsgBox Format(DateAdd("m", -1, "31 July 2000"), "dd mmmm yyyy")
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 certain textbox.
