Time calculation help needed

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

Hi,

Firstly, your formula can be simplified greatly to

Formula:

Please Login or Register  to view this content.

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