Results 1 to 5 of 5
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Adding Time (2002)

    I am trying to add exactly 1 hour to a cell formated as custom, h:mm AM/PM into another cell with the same format. I use this code:

    Target.Offset(0, 1) = Target.Value + 0.04167

    I had to experiment to find that .04167 seems to give me the exact hour. I
    You know it's time to diet when you push away from the table and the table moves.

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

    Re: Adding Time (2002)

    There are several possibilities, each of which can easily be modified for other time values:

    Target.Offset(0,1) = DateAdd("h", 1, Target.Value)

    Target.Offset(0,1) = Target.Value + TimeValue("1:00")

    Target.Offset(0,1) = Target.Value + TimeSerial(1, 0, 0)

    You can get help for the DateAdd, TimeValue and TimeSerial functions by typing them in a VBE window and pressing F1.

  3. #3
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Adding Time (2002)

    Thank you Hans and for the quick response! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> I thought there was a better way than trying to experiment like I did. I'll read up on those three functions and get familiar with them.
    You know it's time to diet when you push away from the table and the table moves.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Time (2002)

    Just to add to what Hans said. .041667 is 1 hour divided by 24 hours (the number of hours in a day). Excel keeps time as the fractional part of a day. The equivalent for 30 minutes would be 30/1440 where 1440 is the number of minutes in a day.
    Legare Coleman

  5. #5
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Adding Time (2002)

    Thanks for the explanation Legare. Now I know why my number worked. It's good to know there are three other ways that are better than experiementing.
    You know it's time to diet when you push away from the table and the table moves.

Posting Permissions

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