Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Kingwood, Texas, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating hours between dates and times (2003)

    I have dates and times in a ss in a single column using the format of mo/da/yr. hh.mm. I would like to be able to find the difference between these entries in hours (or hours and minutes). Is this possible or do I need to have separate columns for time and date?

    A
    1 3/11/04 19:00
    2 3/12/04 7:30
    3 3/15/04 12:30

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Calculating hours between dates and times (2003)

    You don't need separate columns, just format the =end_date&time - start_date&time calculation using

    [h]:mm:ss

    (':ss' is optional)
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Kingwood, Texas, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating hours between dates and times (2003)

    John,

    Doesn't work. For example the difference between the last two entries in my example returns 5:00

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Calculating hours between dates and times (2003)

    Since you had three data items, I'm not sure what your calculation is, but this works for me:
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><tr><td align=center>1</td><td align=right>3/11/2004 19:00</td><td>[h]:mm</td><td align=right>
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Kingwood, Texas, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating hours between dates and times (2003)

    This is what I have and what I get. Column B is formatted with h:mm


    A B

    3/13/04 16:00
    3/14/04 19:00 3:00
    3/15/04 6:00 11:00

    I have B2 formula =A2-A1
    I have B3 formula =A3-A2

    B3 gives the correct answer but B2 does not.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Calculating hours between dates and times (2003)

    Select the column B cells, then select Format, Cells, Number tab, Custom and edit the format to [h]:mm.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculating hours between dates and times (2003)

    As an explanation of the difference between [h]:mm and h:mm

    h:mm is "time in a day" and and can only be in the range 0-23, since the 24th "hr" is the next day.[h] is elapsed time and can go over 24 hours.

    Similarly if you want elapsed minutes you can use [m]:ss or even get elapsed seconds [s]. without the brackets the minutes and secs will not go above 59.

    without the elapsed time, you are getting that i is 3 days and 5 hours later, but you are telling excel to only display the "hours" (=5) and ignore the "day portion"

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Kingwood, Texas, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating hours between dates and times (2003)

    John,

    Thanks for hanging in there. You kept telling me the answer and I was just to DENSE to see the [h]: vs. h:. Got it now. Thanks.

Posting Permissions

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