Adding months to a Date in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to add months to a date. For example, 4/2/04 + 6 months to give
10/2/04.

I have seen examples of how to add months such as:

=MIN(DATE(YEAR(A1),MONTH(A1)+48,DAY(A1));DATE(YEAR(A1),MONTH(A1)+1+48,0))

But Date() means something different in VBA than in Excel. Is there another
solution?

Thanks.
 
Here's one way

Function DateTest(NumMonths)
Dim Date1, Date2, Date3

Date1 = Range("A1").Value
Date2 = DateSerial(Year(Date1), Month(Date1) + NumMonths, Day(Date1))
Date3 = DateSerial(Year(Date1), Month(Date1) + NumMonths + 1, 0)


'=MIN(DATE(YEAR(A1),MONTH(A1)+48,DAY(A1)),DATE(YEAR(A1),MONTH(A1)+1+48,0))
If Date2 < Date3 Then
DateTest = Date2
Else
DateTest = Date3
End If
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
The corresponding VBA function is DateSerial. But there are also specialized
functions like DateAdd, DatePart, etc. Check VBA help for date functions.
 
Back
Top