Excel time issue

[svenopi]

Hey guys,

I have a issue I have been trying to figure out all day.

We have a KPI for a late early arrivals and our KPI if the departure is after 8 minutes of planned time or 12 minutes earlier, it qualifies as "on time". If its more than 12 minutes early it qualifies as "early" and if it is more than 8 later, it qualifies as "late".

I am trying to write a formula to capture that but due to negative times showing as larger, for example -00:06:00 shows as a bigger number than -00:05:00, I can figure out how to write the formula.

You can see from the picture below my table, I am trying to put the formula in column N for data in Column L.

Thank you in advance.

 

[jasonb75]

Screen captures are not easy to work from, there is too much detail missing. XL2BB captures are preferable.

See if this works for you, note that the results will not appear as negative.

J2: =MAX(I2-H2,0)
K2: =ABS(MIN(I2-H2,0))
L2: =MAX(J2,K2)
N2: =IF(J2>TIME(0,8,0),"Late",IF(K2>TIME(0,12,0),"Early","On Time"))

This is the best I can do from a screen captue.