Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hours Calculation Function (Excel 2002)

    Hey people, I was just wondering if anyone had an already made copy of an Excel function that would calculate the difference between two times. For instance:
    A1: 8:30 AM B1: 6:00 PM C1: =9.5
    I know approximately how to do this, but I know it would take me a long time (nested ifs and right functions). Just wanted to check if anyone else has made this already. Thanks,

    -naut

  2. #2
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hours Calculation Function (Excel 2002)

    Format col A & B to the appropriate time format and format col C to "number" format with two decimal places. In C1 enter =B1-A1*24
    Excel stores times based on a percentage of the day so 6:00 PM equals 0.75 to Excel. So by figuring out the difference and mulitplying by 24 it gives you the elapsed time in a numeric format instead of a time format.

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

    Re: Hours Calculation Function (Excel 2002)

    The formula in C1 needs to be:

    <pre>=(B1-A1)*24
    </pre>

    Legare Coleman

  4. #4
    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: Hours Calculation Function (Excel 2002)

    C1 should be:
    (B1-A1)*24

    since you want to SUBTRACT before you multiply. Your calc will subtract the A1*24 from B1.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hours Calculation Function (Excel 2002)

    D' Oh! Thanks for correcting my brain cramp!!

  6. #6
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hours Calculation Function (Excel 2002)

    I managed to make one on my own, it is very different compare to what you have:
    =(HOUR(B1)-12) + (12-HOUR(A1)) - (MINUTE(A1) /60) + (MINUTE(B1) / 60)
    * where A1 is the start time and B1 is the end time. (eg. A1 = 8:00 AM, B1 = 6:30 PM)
    Thanks for the input.

  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: Hours Calculation Function (Excel 2002)

    The one(s) listed above also will take into account days, and seconds and is alot easier.

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hours Calculation Function (Excel 2002)

    This is a pretty old thread, but I just figured I post this little update incase anyone else refferes to it. The (B1-A1)*24 formula doesn't work if you were to work from 4pm to midnight. Here is a fix incase anyone ever needs it:
    =IF((B1-A1)*24>=0, (B1-A1)*24, (B1-A1)*24+24)

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

    Re: Hours Calculation Function (Excel 2002)

    If you add the date to the time, then the (B1-A1)*24 will work just file.
    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hours Calculation Function (Excel 2002)

    <hr>The (B1-A1)*24 formula doesn't work if you were to work from 4pm to midnight<hr>
    Well - yes, it does. See attached.

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

Posting Permissions

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