Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    two days time period (Office 2003)

    On Monday at (b12)19:54:57 we started a job that ran until the next day at (d12)12:20 pm. How can I figure out the total number of hours? I tried D12 - B12.

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

    Re: two days time period (Office 2003)

    The best solution is to put the date+time in the cells. You can then use =D12-B12. This will be a date/time value. If you really need the number of hours, multiply by 24: =24*(D12-B12)

    Alternatively, you can put the dates and times in separate cells, say date1 in A12, time1 in B12, date2 in C12, time2 in D12. You can then use =(C12+D12)-(A12+B12). Again, this will be a date/time value. To get the number of hours, use =24*((C12+D12)-(A12+B12)).

  3. #3
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: two days time period (Office 2003)

    ... and the key to displaying hours in time format is to format the cell properly.

    Either do the maths as Hans suggests and get decimal hours by dividing by 24 into a number field

    Or use custom format and set it to [h]:mm:ss where th square brackets are required - then dividing by 24 isn't needed.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: two days time period (Office 2003)

    We tried doing the format cells but that wouldn't work. We did the formula and got it going.

    Thanks

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: two days time period (Office 2003)

    Hans,
    Can the square brackets not be used here too?
    If you minus the two time values and format the answer to [hh], it will give the total in hours as a value, not a time. Specifying [m] in square brackets will give the total number of minutes.
    Regards,
    Rudi

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

    Re: two days time period (Office 2003)

    Depends on what Daniel wants. If he just wants to see the time difference in hours, the [h] or [hh] format will do fine. If he needs to do further calculations with the number of hours, he will need to multiply by 24 somewhere along the line.

Posting Permissions

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