Question re incrementing Dates by one month

[SA3214]

My apologies in advance if part of this question is off topic.

How do I use vba to increment a date by one month ....

and (probably off topic)

What is the usual practise for dating 'monthly' invoices when the date of
previous invoice falls in the range 29th to 31st and the following month has
less days


Regards & TIA

 

[Bob Phillips]

Here is one way that takes the last day of the month if that event happens.
Problem is, eventually it will always settle on the 28th

Dim myDate As Date
Dim newDate As Date
myDate = "March 31 2005"
If Month(DateSerial(Year(myDate), Month(myDate) + 2, 0)) <> _
Month(DateSerial(Year(myDate), Month(myDate) + 1, Day(myDate))) Then
newDate = DateSerial(Year(myDate), Month(myDate) + 2, 0)
Else
newDate = DateSerial(Year(myDate), Month(myDate) + 1, Day(myDate))
End If
MsgBox newDate

 

[Ron Rosenfeld]

My apologies in advance if part of this question is off topic.

How do I use vba to increment a date by one month ....

and (probably off topic)

What is the usual practise for dating 'monthly' invoices when the date of
previous invoice falls in the range 29th to 31st and the following month has
less days


Regards & TIA

I believe the usual practice is to date the invoice as of the last day of the
month.

In order to do this, you need to always refer back to a "seed date", or else,
when adjusting to the end of the month, you'd have no way of knowing what the
correct day should be for the following month.

In VBA, the DateAdd function will adjust to the proper end of month date.



So something like this should give you some ideas:

Sub IncrMonth()
Const SeedDt As Date = "31 Jan 2004"
Dim NumMnths As Double

For NumMnths = 0 To 12
MsgBox (NumMnths & " months since " & SeedDt & _
" is " & DateAdd("m", NumMnths, SeedDt))
Next NumMnths

End Sub

--ron