Calculating total run time of a device...

[some_evil]

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

 

[Bob Phillips]

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

=MOD(C2-B2,1)

 

[some_evil]

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.

 

[alderman]

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).