Calculate next quarter end date in VBA

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Calculate next quarter end date in VBA

  1. #1
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    42,657
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    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
    3,304
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    42,657
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    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!"

User Tag List

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