Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Now() - 1 month? (XP)

    Hi, quick Q

    how do I calculate a date - 1 month? I am using VBA in Excel. I can do

    Dim d As Date
    d = Now() - 31

    to give me the date a month ago for 31 day months, which is usable, but would rather have the exact date just one month in arrears... I am sure I have done this befor but cant remember right now.
    Thanks,

    pmatz

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

    Re: Now() - 1 month? (XP)

    d = DateAdd("m", -1, Date)

    Click in DateAdd after you've typed it and press F1 to get help on this useful function.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Now() - 1 month? (XP)

    Thanks Hans, thats neat.

    I was messing with DateSerial() which can probably work too.

    An oddity while i was populating a range with random dates using the following formula

    For Each c In Range("checkrange")
    c = DateSerial(2005, Int(Rnd(1) * 12), 1)
    Next c

    is that I get 01/12/2004 for December - but I have specified the year to be 2005? All other random months are 2005...
    Thanks,

    pmatz

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

    Re: Now() - 1 month? (XP)

    DateSerial is rather flexible - if you enter 0 for the day argument, you get the last day of the previous month, and if you enter 0 for the month argument, you get the last month of the previous year.
    Rnd(1) is a number between 0 and 1; Rnd(1)*12 is a number between 0 and 12; taking the Int may result in 0. DateSerial(2005, 0, 1) evaluates to the first of December, 2004.

  5. #5
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Now() - 1 month? (XP)

    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    Thanks Hans
    Thanks,

    pmatz

Posting Permissions

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