Forum Discussion
Special Support in time calculations for similar criteria
Hi All,
I'm searching for a solution for a complicated problem I'm facing a month ago. As shown in attached picture, this file is to monitor and record repetitive failures in our plant. In this file the basic idea is to insert equipment tag number in column "D", then insert start/finish date & time of failure and repair in columns "H, I, J, K" (Time is in day and hours). Based on that data I will make my data analytics and get clear overview to equipment performance.
The problem that I cannot solve is how to make automatic calculations for TBF "Time Between Failure" in column "O".
For example, I want to make following operation automatically for any further equipment.
- In rows 8 & 9 the data are for equipment BM-8490 B .
- If I insert equipment tag "BM-8490 B" in row 15, I want excel to subtract [H15 "current failure date" -K9 "Last time the equipment was in service"]
- How can I do this automatically for any further similarity in equipment tag number?
Subtract from the current failure date the maximum back to service time of the set of rows with the same part number and for which the back to service time is before the current failure date.
=IFERROR($G2- MAX( FILTER($J$2:$J$9,($D$2:$D$9=$D2)*($J$2:$J$9<$G2)) ) ,0)
