Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forward Date (VBA/Excel/97)

    I was trying to use

    dateadd("m", monthsForward, endingMonthDate)

    to determine an ending month date, out a certain number of months (monthsForward) in the future. However, the problem with using this routine is that it will only go out the number of days associated with endingMonthDate. For example, if September 30, 2004 was the endingMonthDate and I wanted to go out three months, the date given by dateadd would be 12/30/2004. I need the actual end of the month or 12/31/2004. I have the dateadd function in an "IF" statement and endingMonthDate is not a fixed date. Any ideas?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Forward Date (VBA/Excel/97)

    Hi,
    You could use something like:
    <pre>Function GetLastDayofMonth(dteFrom As Date, intMonths As Integer) As Date
    GetLastDayofMonth = DateSerial(Year(dteFrom), Month(dteFrom) + intMonths + 1, 0)
    End Function
    </pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forward Date (VBA/Excel/97)

    Have you tried the EOMonth function from the Analysis ToolPak?

    There's the toolpak, then there's the toolpak/VBA.

    As an end-user, place =NOW() in cell A1, place the integer 3 in cell A2, and in cell A3 "=EOMONTH(A1,A2)" without the quotes.

    If this works for you as an end user, consider calling it from VBA as a developer.

    HTH

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forward Date (VBA/Excel/97)

    I modified your code a bit to get

    DateSerial(Year(DateAdd("m", x, BeginningMonthDate)), Month(DateAdd("m", 1, BeginningMonthDate)) + 1, 0)

    While this gives me the end of date value for x months in the future and an ending date value that is no longer a function of the number of days in the reference month BeginningMonthDate, it is not as intuitive as I would like. However, it works and I shall move on. Thanks for your help.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forward Date (VBA/Excel/97)

    Is eomonth a VBA function?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forward Date (VBA/Excel/97)

    To use EoMonth in VBA, you must do the following:

    - In Excel, select Tools | Add-Ins...
    - Tick the check box for Analysis Toolpak VBA.
    - Click OK.
    - Switch to the Visual Basic Editor.
    - Select Tools | References...
    - Tick the check box for atpvbaen.xls (it should be immediately below the references already ticked).
    - Click OK.

    EoMonth will then be available. From the Object Browser:

    Function EoMonth(start_date, months)
    Member of atpvbaen.xls.VBA Functions and Subs
    Returns the serial number of the last day of the month before or after a specified number of months

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forward Date (VBA/Excel/97)

    > Is eomonth a VBA function?

    Yup. I ran it before making my post. I see you've been clued in by other parties.

    Give us a final report at the end of the month (huge grin!)

Posting Permissions

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