PLEASE HELP!
I am certain that this is not the most ideal way to accomplish the given task but I am using the following formula to "mostly" accomplish the time duration calculation:
=IFERROR(
IF(D14="END_SYSTEM_DEGRADED",B14-LOOKUP(2,1/($D$13:$D13="BEGIN_SYSTEM_DEGRADED")/($A$13:$A13=A14),$B$13:$B13),
IF(D14="END_SERVICE_OUTAGE",B14-LOOKUP(2,1/($D$13:$D13="BEGIN_SERVICE_OUTAGE")/($A$13:$A13=A14),$B$13:$B13),
IF(D14="END_SERVICE_DEGRADED",B14-LOOKUP(2,1/($D$13:$D13="BEGIN_SERVICE_DEGRADED")/($A$13:$A13=A14),$B$13:$B13),
IF(D14="END_RFC_DEGRADED",B14-LOOKUP(2,1/($D$13:$D13="BEGIN_RFC_DEGRADED")/($A$13:$A13=A14),$B$13:$B13),
IF(D14="END_RFC_OUTAGE",B14-LOOKUP(2,1/($D$13:$D13="BEGIN_RFC_OUTAGE")/($A$13:$A13=A14),$B$13:$B13),
IF(D14="END_SYSTEM_DEGRADED",B14-LOOKUP(2,1/($D$13:$D13="BEGIN_EMERGENCY_RFC")/($A$13:$A13=A14),$B$13:$B13),
IF(D14="END_SERVICE_DEGRADED",B14-LOOKUP(2,1/($D$13:$D13="BEGIN_EMERGENCY_RFC")/($A$13:$A13=A14),$B$13:$B13),
IF(D14="END_SERVICE_OUTAGE",B14-LOOKUP(2,1/($D$13:$D13="BEGIN_EMERGENCY_RFC")/($A$13:$A13=A14),$B$13:$B13),
"")))))))),"NO MATCH")
Unfortunately, I am now running into issues with the "BEGIN_EMERGENCY_RFC" using the same "END" criteria as something else i.e. END_SYSTEM_DEGRADED, END_SERVICE_DEGRADED and END_SERVICE_DEGRADED.
I hope this makes sense.
ANY help with this is greatly appreciated!
Bookmarks