Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: I'M STUCK!!! Trying to add dates in VBA.

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Arizona
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can anyone tell me why this won't work???

    --
    Dim endcycle As Date
    Dim newstartcycle As Date

    newstartcycle = CDate(InputBox("Enter beginning date of billing cycle."))

    endcycle = DateAdd(m, 1, newstartcycle)
    --

    thanks in advance to whomever solves this!

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Arizona
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    additionally, how can I use VBA to return a date that is the 1st of the next month?

    e.g. 3/15 --> 4/1
    8/15 --> 9/1

    etc.

    Thanks again if you can answer this

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Add quotes around the 'm' in the DateAdd

    Change:
    endcycle = DateAdd(m, 1, newstartcycle)
    to
    endcycle = DateAdd("m", 1, newstartcycle)

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-06 16:17, chouston wrote:
    additionally, how can I use VBA to return a date that is the 1st of the next month?

    e.g. 3/15 --> 4/1
    8/15 --> 9/1

    etc.

    Thanks again if you can answer this
    One way:

    DateSerial(Year(endcycle), Month(endcycle) + 1, 1)

    Another is to use the EOMONTH function, but you have to set references to the Analysis ToolPak to call the function.

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Arizona
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    THANKS!

    I ended up cheating on the second part though:
    --
    newstartcycle = InputBox("Enter beginning date of billing cycle.")

    endcycle = DateAdd("m", 1, newstartcycle)
    nextdate = DateAdd("m", 1, endcycle)
    duedate = DateAdd("d", -14, nextdate)
    --
    it's not fancy, but it works, Thanks again.

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You may want to validate the date, i.e., application.inputbox....type:=1, at the very least, you'll get a number.

Some videos you may like

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
  •