Time calculation help needed

[frustrated]

Hi All

I have a spreadsheet that is being used to identify faults with equipment and log how long that equipment is broken down because of the fault.


I am logging the time and date the machine broke down and also the time and date it is back up and running. Using this information I would like to calulate the duration of the breakdown.

Simple so far........if only it were really so!

The duration I wish to calculate is not for the whole breakdown but for parts of it that fall within a specific period based upon some reference dates and times that are entered.


So far, I have managed to get my formula working if my reference points are 24 hours or less. Anything over this period e.g a week is not yielding the correct results.

I apologise if I have not explained my requirements clearly which is why I have attached the spreadsheet for you to look at.

I would really appreciate some help with this as I have now reached the limit to my capability.

Thank you

Duration.xls

[ajryan88]

Hi,

Firstly, your formula can be simplified greatly to

=IFERROR(24*((K5+L5)-(I5+J5)),"")

Secondly, I'm not sure where this formula isn't working...??? The reason it is producing negative values from rows 45 onwards is because the end time is occurring before the start time.

If I have read/misinterpreted something incorrectly regarding what it is that you are trying to do, then can you please try and explain it again in greater detail.

Hope this helps