Adding 6 months to a date

[doctortt]

If I do

DATE(YEAR(2014),MONTH(5)+6, DAY(31))

I will get December 01, 2014, but I actually want November 30th, 2014.

What can I do to fix this problem? I can't really change May 31st to May 30th.

[GarysStudent]

Not so....... DateSerial(2014, 5 + 6, 30) gets 11/30/2014
then...........DateSerial(2014, 5 + 6, 31) should be one day after

[Paul_Hossler]

Depends on what you mean by 6 months

If you want the last day of the month 6 months from now, then use


=DATE(YEAR($A$1),MONTH($A$1)+7,0)



Paul

[macropod]

If I do

DATE(YEAR(2014),MONTH(5)+6, DAY(31))

I will get December 01, 2014, but I actually want November 30th, 2014.

What can I do to fix this problem? I can't really change May 31st to May 30th.
What you've posted isn't vba, but a formula.
In formula terms, if the date is in A1, you'd use:
=MIN(DATE(YEAR(A1),MONTH(A1)+6+{0,1},DAY(A1)*{1,0}))

In vba terms, for the equivalent you might use:
Sub Demo()
Dim i As Long, StrDt As String
i = InputBox("How many months to add?", "Add Months", 6)
If DateSerial(2014, 5 + i, 31) > DateSerial(2014, 5 + 1 + i, 0) Then
StrDt = DateSerial(2014, 5 + 1 + i, 0)
Else
StrDt = DateSerial(2014, 5 + i, 31)
End If
MsgBox StrDt
End Sub

[AMontes]

Look here:
http://office.microsoft.com/en-001/excel-help/eomonth-function-HP010062288.aspx

[doctortt]

What you've posted isn't vba, but a formula.
In formula terms, if the date is in A1, you'd use:
=MIN(DATE(YEAR(A1),MONTH(A1)+6+{0,1},DAY(A1)*{1,0}))



This works great; it's exactly what I want. Thanks everyone

[doctortt]

Deleted

[doctortt]

Ok, it's resolved now. Paul's suggestion fits best.

=DATE(YEAR($A$1),MONTH($A$1)+7,0)

This will now display the last day of the month, which is the 6th month after the date in A1.