Repeating a date/time subtraction that meets certain conditions involving multiple rows?

I have a spreadsheet that allows users to track startup and shutdown dates and times of two machines, CG-3001 and CG-3002. Certain conditions of these machines are also tracked during startup/shutdown, such as whether it was scheduled or unscheduled, or whether the machine was pressurized or depressurized. I have written formulas to calculate various amounts, such as the number of startups/shutdowns, number of scheduled/unscheduled startups/shutdowns, etc., as well as "warm-up time" (which is the time from when the machines are first started until the time when they are fully up and running).

My problem now is I need to calculate values involving cells on different rows and I need it to repeat as the users input data. For example, if CG-3001 is shut down at a time in cell F6 and then started up in cell D9, shut down again in cell F10 and started up again in cell D15, and keeps going down the spreadsheet, I need to track the amount of time CG-3001 was in the shutdown state--even though there is activity with CG-3002 in between these rows. I have multiple calculations I need to track like this, but they are all very similar: total shutdown time, total run time, total pressurized shutdown time, total depressurized shutdown time, total unscheduled shutdown time, and total scheduled shutdown time. These calculations need to be performed for both machines, CG-3001 and CG-3002. My input data ranges from row 5 to row 998 on sheet 1 (Input Log) and my calculations are on sheet 2 (Run Times).

I've attached the spreadsheet with sample data plugged in, since it's kind of difficult to explain what I mean in writing. Please let me know if you have any questions and thanks in advance for the help!

Hi,

Is there any reason why the Shutdown times in column F can't be shown on the same line as the Unit start up time. You would simplify things immensely were that the case.

Originally Posted by rgoodman

Unfortunately, they cannot be in the same row. The spreadsheet the users are currently using has them on the same row and it has caused a lot of problems.

Why? On the face of it there seems no reason why they should not be aligned on the same row, and by splitting the start/finish times on two rows as you have is certainly causing difficulties for a simple solution.

Tell us what problems you encounter when on the same row since it's almost certainly going to be easier to address those.

The reason they can't be is because the users have a tendency to input the data incorrectly if they are on same row. They won't completely fill out the row, will skip rows, and will mix up dates/times. I know it seems simple to have it the other way, but that is how our current spreadsheet is and it's full of user errors. We're trying to make it impossible to mess up.