Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting the difference between two times (97)

    I have a formula that is calculating the difference between two times. For example, if you take 7:00 from 9:30, then I'll get 2:30 for the answer if the cell is formatted as a time. However, I don't want it to say 2:30, I want it to show 2.5 for 2 and a half hours. I'm sure there is a way to do this, but I'm just not sure how. Any help would be most appreciated.

    -Kelley

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting the difference between two times (97)

    If you have 7:30 in A1 and 9:00 in A2 (that is formatted as [h]:mm), then try

    =(A2-A1)*1440/60

    in A3, formatted as general.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting the difference between two times (97)

    That worked! Thank you!

    What exactly does the *1440/60 do ?

    -Kelley

  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: Formatting the difference between two times (97)

    It does the same as "*24". Times are maintained in Excel as a decimal portion of a day, so either *1440/60 or *24 will convert to hours. (1440 minutes in a day.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting the difference between two times (97)

    Thank you so much. That explains it well.

    -Kelley

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting the difference between two times (97)

    Simply there are 24 hours in a day, each hours counts 60 minutes: 24*60 = 1440. This means that by just doing (A2-A1)*24, the result is exactly the same. The reason for 1440/60 is that if you want to convert the difference in minutes, then you should only multiply by 1440; if you want the difference in hours, then you just multiply by 1440/60 or 24 and format as general. In that context, 1440/60 is more logical to use than 24.

Posting Permissions

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