Results 1 to 4 of 4

Thread: Adding time

  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding time

    I need to add/subtract start times and end times together to come up with a duration. For instance if I enter 11PM in one cell and 1AM in the next cell, can Excel (with a formula) tell me that I have spent 2 hours on a certain project?

    Thanks in advance for your help.

    Christa

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Re: Adding time

    Excel doesn't handle negative times, so subtracting one time from another won't work. I think the easiest way is to use an IF function. If your times (without a date) are in A1 and B1, then

    =IF(A1>B1,B1+1-A1,B1-A1)

    subtracts and finds the duration.

    The "1" in the "true" section of this formula allows for B1 to be in the next day. Note, without formatting this result, you get a decimal that is the fraction part of a 24 hour day. You could either format the result of multiple it by 24, depending on whether you want "2" or "2:00".

    HTH

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding time

    Thanks, I'll give that a try

  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

    What is really in those cells? If the cells contain date and time (ie. you entered "4/19/2001 11:00 pm"), then you should be able to just subtract the start from the finish and come up with the elapsed time. You can then format the result with the format [hh]:mm with the btrackets in the format to get the time spent on the project. However, if the cells contain just the time (you just entered "11:00 pm"), then you are going to have to use a formula with an If command to adjust the finish time if it is in the next day.
    Legare Coleman

Posting Permissions

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