Calculate next quarter end date in VBA

[Joe4]

In cell B5 of my spreadsheet, I have a month end date. It will always be the last day of a month (i.e. 1/31/03, 2/28/03, etc.).

In VBA, I need to calculate the following quarter end date based on this date. Essentially, whatever the date is in cell B5, I need to calculate the month end date 3 months into the future.

Here are some examples of dates in B5 and what they should convert to:
1/31/03 -> 4/30/03
9/30/03 -> 12/31/03
11/30/03 -> 2/29/04
etc.

I am working on some code, but it is rather messy, and I would be interested in seeing if others can come up with a simpler, cleaner, solution.

TIA.

 

[Dave Patton]

As a regular function try =DATE(YEAR(B5),MONTH(B5)+4,0)

so with VBA use Dateserial

 

[Albert 1]

=EDATE(B5,3)

and copy down.
(Analysys Toolpak needed).

Albert 1

 

[Joe4]

Thanks Dave. They key I needed was DATESERIAL (I couldn't find the VBA equivalent of the DATE function). It is easy enough to do in Excel, it was the VBA part that was giving me problems.

DateSerial(Year(Range("B5")), Month(Range("B5")) + 4, 0)
works great!