Results 1 to 4 of 4

Thread: Calculating total run time of a device...

  1. #1

    Calculating total run time of a device...



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I have been given pages and pages of handwritten start and stop times and I have the fun task of finding total run times for said device, over a period of time.

    I have no idea how to format this data in a spreadsheet, other than that I should have 3 columns of data:

    Date | Start Time | Stop Time

    1/1/14 | 1.25am | 3.14am
    1/1/14 | 5.23am | 10.48am
    3/1/14 | 6.03pm | 1.24am <-- Stopped Next Day!

    So I think that having a 4th column called Total On Time which gives Hours run total would be handy to graph.

    Can anyone help me get this started? Because I dont know how to format it so that when I do things like 'B3-B2' it gives me a usable answer. And somehow I need to incorporate run times that go past midnight and into the next morning.

    Any guidance/advice would be greatly appreciated.

    Thanks
    -Ben

  2. #2
    Format columns B and C as time, and use this formula in D2

    =MOD(C2-B2,1)

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    Format columns B and C as time
    Hi Bob,

    I cannot get my head around how excel is thinking... I have formatted the column as "Time", i selected the option '13:30:55' as the type.

    But when I type in my time in 24hr time, for example I type in 1.25, excel changed this cell to display 6:00:00. When I select this cell, it shows me that it is storing '1/01/1900 6:00:00 AM', and I dont know what to do to change this.

    Any further hints?

    Thanks Bob.

  4. #4

    Colon is needed

    Try to use the colon to separate the time so if you mean 1:25 AM use 1:25 and if you mean 1:25 PM use 13:25, not 1.25. by typing in 1.25 excel is interpreting this as the 1 and a 1/4 day of the earliest day excel can record which is Jan 1 (the 1) and 6:00AM which is the (0.25).

Tags for this Thread

Posting Permissions

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