Formula Help To Calculate Hours exceeding the Target hours KPI

[Lukma]

Hi Guys

I need a help around few formula to to calculate hours exceeding a target KPI 12Hrs. to complete the operation Work for Offloading & Loading

Column A1 with start time 01/01/21 03:00
Column B1 With End Time 01/01/21 19:00

what i need formula to do is to give me the exceeded Hours that do not meet the KPI 12 hrs. completed
And If the target is meet it should give me a negative hours

Appreciate with any Help with formula to get this done

Regards

 

[Toadstool]

Hi Lukma,

Does this do what you ask?

Lukma.xlsx
	A	B	C
1	Start	End	KPI Met?
2	1-Jan-2021 03:00	1-Jan-2021 19:00	Exceeds KPI
3	1-Jan-2021 03:00	3-Jan-2021 18:00	Exceeds KPI
4	1-Jan-2021 03:00	1-Jan-2021 14:00	OK
5	1-Jan-2021 23:00	2-Jan-2021 10:00	OK
6	1-Jan-2021 23:00	2-Jan-2021 11:00	OK
7	1-Jan-2021 23:00	2-Jan-2021 11:01	Exceeds KPI
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=IF(B2>A2+TIME(12,0,0),"Exceeds KPI","OK")

 

[Lukma]

Hi Lukma,

Does this do what you ask? Yes thanks so much Toadstool

Lukma.xlsx
	A	B	C
1	Start	End	KPI Met?
2	1-Jan-2021 03:00	1-Jan-2021 19:00	Exceeds KPI
3	1-Jan-2021 03:00	3-Jan-2021 18:00	Exceeds KPI
4	1-Jan-2021 03:00	1-Jan-2021 14:00	OK
5	1-Jan-2021 23:00	2-Jan-2021 10:00	OK
6	1-Jan-2021 23:00	2-Jan-2021 11:00	OK
7	1-Jan-2021 23:00	2-Jan-2021 11:01	Exceeds KPI
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=IF(B2>A2+TIME(12,0,0),"Exceeds KPI","OK")

 

[Toadstool]

You're welcome!