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.

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

Mike F
"kdw" wrote in message
...
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(YEA R(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)


"kdw" wrote in message
...
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.

The DateSerial function is exactly what I needed. Thanks.

"Bob Phillips" wrote:

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(YEA R(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)


"kdw" wrote in message
...
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.

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

On Wed, 17 Nov 2004 15:29:06 -0800, kdw wrote:

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(YEA R(A1),MONTH(A1)+1+48,0))

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

Thanks.