May 16 2022 10:52 AM
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.
May 16 2022 11:19 AM
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)