# Calculate next quarter end date in VBA

This is a discussion on Calculate next quarter end date in VBA within the Excel Questions forums, part of the Question Forums category; In cell B5 of my spreadsheet, I have a month end date. It will always be the last day of ...

1. ## Calculate next quarter end date in VBA

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.

2. ## Re: Calculate next quarter end date in VBA

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

so with VBA use Dateserial

3. ## Re: Calculate next quarter end date in VBA

=EDATE(B5,3)

and copy down.
(Analysys Toolpak needed).

Albert 1

4. ## Re: Calculate next quarter end date in VBA

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!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•