Excel time issue

svenopi

New Member
Joined
Jul 20, 2017
Messages
2
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.
 

Attachments

  • Capture.PNG
    Capture.PNG
    40.6 KB · Views: 3

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,988
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,440
Members
410,684
Latest member
LakTik
Top