Calculating Operational Downtime

I cannot figure out how to calculate the operational downtime via formula. Attached I have a sample of what I am trying to figure out. We occasionally have a machine go down on lets say 01APR at 03:15 then we fix it on 03APR 19:37. I need a formula to figure out both How many minutes the machine is down in total and a separate formula to figure out how many minutes of operational time we missed. Please help I cannot wrap my head around this.

Do you ever have down time start outside of your operational hours? If you don't, these formulas might do the trick.

For total downtime, in H2 and drag down:

Please Login or Register  to view this content.

For operational downtime, in J2 and drag down:

Please Login or Register  to view this content.

Use those results divided by 60 to get the hours. Hopefully that gives you what you're looking for.

Hi and welcome to the forum,

Perhaps in G2 copied down

Formula:

Please Login or Register  to view this content.

This will give you the hours and decimals of an hour for downtime.

Firstly:

=((E2+F2)-(C2+D2))*1440

Subtract the sum of C2 and D2 from the sum of E2 and F2, then multiply the result by 1440. This turns the answer into minutes.

Secondly:

=(IF(F2<=A2,(E2-1)+B2,IF(F2>=B2,E2+B2,E2+F2))-(C2+D2))*1440

If F2 is less than or equal to A2, subtract 1 from E2 and then add this to B2 then subtract the sum of C2 and D2 and multiply the result by 1440.

If F2 is bigger than or equal to B2, add E2 and B2 then subtract the sum of C2 and D2 and multiply the result by 1440.

If neither of the first to criteria are met, add E2 and F2 then subtract the sum of C2 and D2 and multiply the result by 1440.

No, not crazy - just very logical. That's all it is - logic: if this, then this; if that, then that; if neither of the above, the other.

Try this:

=IF(Z2<>"Dog","",(IF(F2<=A2,(E2-1)+B2,IF(F2>=B2,E2+B2,E2+F2))-(C2+D2))*1440)

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

To add to Ali's explanation, the logic in the second formula is there to account for fixes outside of working hours. If the fix time is between midnight and open, only count the hours to the end of the previous day. If the fix time is between close and midnight, only count the hours to the end of the current day. If neither is true, count the total time.

Hi,

It's extremely important that we are able to check any solutions we come up with.

That necessitates you first manually calculating and adding the results you expect. Clearly identify which columns are data and which are results, and if it's not blindingly obvious explai your workings in a note that references specific cells.

Please add the results and reupload

I second Richard's request for expected results. I also have a couple more questions. Can any of the Open Times or Change Times be outside of normal operating hours? I would guess no, but I may be wrong (I often am). Also, what happens when the Close Time / Date are before the Change Time / Date? Which time / date should be used to calculate hours?

Try this formula, based on the data provided. It does not account for any start / change / close times outside of normal working hours. If those need to be accounted for, let us know which times might fall outside normal working hours. It also requires a helper formula.

Helper formula in O1, copied and pasted into O4 and O7 (make sure the references adjust):

Please Login or Register  to view this content.

Downtime calculation formula in K2 and dragged down:

Please Login or Register  to view this content.

This matches the results provided (or should). If any adjustments need to be made, let us know.

Here's an updated formula so P2 results in blank:

Please Login or Register  to view this content.

There are others here far more talented than me. I just stumble on through as best as I can

Happy to help, and thanks for the rep!

All 0 sums should be blank as Row 2 is correct.

Picking up your comments in #15 can we go back to basics.
Would you confirm the following

As I understand it the object is to determine the number of minutes between the time a task is said to be started and the time it's closed.

1. The time the task is started is EITHER Columns D&E UNLESS there's a date/time in columns (N&O) in which case N&O are used
The time the task is closed is Columns R&S

2. Once the difference between the times in 1 above is evaluated this is modified by deciding whether the start or end times are outside the hours of operation of a particular airport, and if that is the case reducing the number calculated in 1 above accordingly - taking into account if more than one day is involved the modification needs to reflect this.

Are there any other modifications? I see reference to P1 & P2 but if I understand it that is essentially the calculation in 1 above

1. The time the task is started is ALWAYS Columns D&E.

2. The task closed date / time in Columns R&S are the final close out. The calculation for this difference is reflected in Columns X/Y

3. The Columns U&W are the reflection of the calculation of the difference in Open (D&E ) & Close (R&S) OR Open (D&E) & Change (N&O). Only IF Column B = P1.

4. If Column B = P2 then Columns U&W shall remain Blank.

I hope this helps clarify. I think you understand what I'm thinking.

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Please check the attached out.

It uses the following formula

Formula:

Please Login or Register  to view this content.

I think perhaps the only modification required is to trap an event like row 10 where the finish Date/Time is EARLIER than the Start date/Time

The only other mod that might be necessary is where more than one day is involved and the start or end times are within the hours of operation.
At the moment an adjustment is made for the whole of the hours of operation for each day of the subsequent days if relevant. I've not checked it out yet but I think it needs an adjustment where this is the case

Sorry you've lost me

My formula is not showing False, it's showing 100 minutes, i.e the difference between 18:21 and 20:01
You are showing 39 in V26. How do you calculate that?

As I mentioned I think a Mod is still required for a start/finish time outside the hours of operation. I think this is a case in point since the finish time is 20:01, i.e. i minute past the hours of operation which finished at 20:00. Should the actual result be 99 minutes not 100?

The reason the formula is returning FALSE on Ticket Summary is because there is a scenario there that wasn't represented in the previous workbook, namely P1 being in both the initial priority and the interim priority. I did not account for that in the formula. Does the Ticket Summary sheet have all of the possibilities that need to be accounted for?

Edit: The reason there are #VALUE! errors is due to the Times not all being actual times, but some being text.

Back to Basics

Which sheet are you trying to get results for? Sheet1 or the Tickets SUmmary (2) sheet?

Your post #23 said "The manual work is in Yellow and the formula work is in Orange." I took that to mean Ticket Summary (2)
And I also took from that, that the results you WANTED was the orange column O, but there were FALSE values occurring which you needed correctingso that they produced a numeric result - which was waht my simplified formula is doing.

As with all these things we need a definitive set of results that you've manually created, and an understanding of the rules you apply. I think I understand the rules you want to apply - what I'm not clear on is what are the ACTUAL results you want for ALL the records.

As I said I want to see a definitive set of ALL the results you expect in order that any solution can be tested.
And I'm assuming that the Production worksheet you want to use is the Ticket Summary sheet - but if not say so, there's no point in working on a sheet that you won't be using in anger.

Does column W, specifically W2:W115 contain all the results you expect? I sense not since it contains FALSE values and errors.
Or is it column V?

However If you will manually add ALL the results you expect and indicate which column contains the results maybe we can move forward.

The formula I came up with was simpler than the one you were using and in most cases came up with the same answer, and gave values where you were getting FALSE.

One thing I've noticed on the spreadsheet currently uploaded in post #1 is that on the Ticket Summary sheet, there are a number of cells that have "P1 " (extra space), meaning the formula is returning FALSE when comparing "P1" to "P1 ". I've added TRIM functions, and I'm now treating P1 in column M on Ticket Summary the same as if column M is blank.

Please Login or Register  to view this content.

There are still some anomalies here and there. There are a few manually calculated numbers that do not appear correct. There are a few airport codes with no hours specified. There are a few rows where interim priority is N/A. Try out the formula in column W in Ticket Summary, and then let us know how you'd like the various anomalies handled.

...So are you needing any further assistance???

If so please upload the workbook with the Results you expect fully detailed as per my last post.