Adding months to a Date in VBA

[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.

 

[Mike Fogleman]

Take a look at Chip Pearson's site about dates
http://www.cpearson.com/excel/datearith.htm

Mike F

 

[Bob Phillips]

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)

 

[Guest]

The DateSerial function is exactly what I needed. Thanks.

 

[Myrna Larson]

The corresponding VBA function is DateSerial. But there are also specialized
functions like DateAdd, DatePart, etc. Check VBA help for date functions.