Results 1 to 4 of 4

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. #1
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    26,997

    Default 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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    2,378

    Default 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. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    390

    Default Re: Calculate next quarter end date in VBA

    =EDATE(B5,3)

    and copy down.
    (Analysys Toolpak needed).

    Albert 1

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    26,997

    Default 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!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com