Help with a Formula of IF greater than or Less than or with a Negative time sign

[Lukma]

Hello Friend

Please I need a little help with my formula which i am having doubts about
In Column E, I have my Arrive Date & Time for Vessel at Fairbouy, In Column G, I have my Manifest passed date & Time , and I have got the total Hours in Column H,
i have your formula in Column H, for negative sign time, meaning if the Manifest Passed Date and Time is early thank arrival date of Vessel at Fairbouy

Now i need Column H with a Formula, IF Column H is Greater than 3 hours it should give me Not Met
And IF column H is Less than 3 hours or With a Negative Sign it should give me Met

I also Have Column J Permission Date and Time which I also Need a formula in Column L with the same formula IF Column K is Greater than >3 hours Not Met
and if Less than < 3 hours or with a Negative sign as Met

Appreciate a help to resolve this

2023-Master Data Onshore Jetty Operation Tracking.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	WK Rpt	Month	Mus Num	Supply Vessel	Vessel Arrived FWB Date&Time	Chanel Status	Manifest Date & Time	Mnfst On Time	Mnfst Delay Hours	Control Tower Permission Date & Time	Tower Perm On Time	Tower Perms Delay Hours
2		0	1153-M	ADNOC-230	27/04/23 14:25		27/04/23 14:30	0:05	Met	27/04/23 19:30	5:00	Not Met
3		0	1154	CECILIE-K	01/05/23 10:00		01/05/23 12:40	2:40	Not Met	01/05/23 12:44	0:04	Met
4		0	1155	Z-OCEAN	01/05/23 14:30		01/05/23 13:40	-0:50	Met	01/05/23 13:51	0:11	Met
5		0	1156	SWISSCO RUBY	02/05/23 06:00		01/05/23 21:56	-8:04	Met	01/05/23 22:40	0:44	Met
6		0	1157	LCT-SHEWELEH	01/05/23 16:00		01/05/23 17:37	1:37	Not Met	01/05/23 18:27	0:50	Met
7		0	1158	A-RANGER	01/05/23 20:00		01/05/23 16:57	-3:03	Met	01/05/23 17:00	0:03	Met
8		0	1159	ADNOC-S02	01/05/23 21:10		01/05/23 20:14	-0:56	Met	02/05/23 03:00	6:46	Not Met
9		0	1160	ADNOC-810	01/05/23 01:50		01/05/23 19:55	18:05	Not Met	01/05/23 20:08	0:13	Met
10		0	1161	ADNOC-511	01/05/23 22:10		01/05/23 21:21	-0:49	Met	01/05/23 21:38	0:17	Met
11		0	1162	SK MAINSTAY	02/05/23 06:05		02/05/23 01:59	-4:06	Met	02/05/23 05:46	3:47	Not Met
12		0	1163	MUTAWA-402	01/05/23 10:00		02/05/23 01:31	15:31	Not Met	02/05/23 01:50	0:19	Met
13		0	1164	ADNOC-512	02/05/23 08:10		02/05/23 05:19	-2:51	Met	02/05/23 05:50	0:31	Met
14		0	1165	ADNOC-812	02/05/23 09:40		02/05/23 09:13	-0:27	Met	02/05/23 09:22	0:09	Met
15		0	1166	Z-POWER	02/05/23 04:20		02/05/23 11:14	6:54	Not Met	02/05/23 11:49	0:35	Met
Sheet4

Cell Formulas
Range	Formula
A2:A15	A2	=IFERROR(EOMONTH(K40,-1)+1,"")
B2:B15	B2	=IF(WEEKDAY(K40,2)>7,"",WEEKNUM(K40)-WEEKNUM(DATE(YEAR(K40),MONTH(K40),1))+1)
H2:H15	H2	=IFERROR(IF((G2-E2)>0,(G2-E2),TEXT(ABS(G2-E2),"-h:mm")),"")
I2:I15,L2:L15	I2	=IF(OR(LEFT(H2,1)="-",H2<3/48),"Met","Not Met")
K2:K15	K2	=IFERROR(IF((J2-G2)>0,(J2-G2),TEXT(ABS(J2-G2),"-h:mm")),"")

 

[Peter_SSs]

IF Column H is Greater than 3 hours it should give me Not Met
And IF column H is Less than 3 hours or With a Negative Sign it should give me Met

With your existing column I formula, just change 3/48 to 3/24 since there are only 24 hours in a day.

=IF(OR(LEFT(H2,1)="-",H2<3/24),"Met","Not Met")

Similar with column L

=IF(OR(LEFT(K2,1)="-",K2<3/24),"Met","Not Met")

 

[Lukma]

With your existing column I formula, just change 3/48 to 3/24 since there are only 24 hours in a day.

=IF(OR(LEFT(H2,1)="-",H2<3/24),"Met","Not Met")

Similar with column L

=IF(OR(LEFT(K2,1)="-",K2<3/24),"Met","Not Met")

Dear Peter

Thanks so much, I really appreciate your support which I really needed now.

The forum has been so helpful with my current state of data and formula.

many grace to you all

Thanks

 

[Lukma]

With your existing column I formula, just change 3/48 to 3/24 since there are only 24 hours in a day.

=IF(OR(LEFT(H2,1)="-",H2<3/24),"Met","Not Met")

Similar with column L

=IF(OR(LEFT(K2,1)="-",K2<3/24),"Met","Not Met")

Hi peter

One last issue I need a few updates with my formula if there is no time I need it to remain Blank I tried IFERROR but still shows met in a cell with no time as I need to drag the formula down


Regards

 

[Peter_SSs]

if there is no time

I assume that means that the vessel has not arrived so column E would be blank?

=IF(E2="","",IF(OR(LEFT(H2,1)="-",H2<3/24),"Met","Not Met"))

If that is not the case then please give us some sample data that reflects what you have/want.

 

[Lukma]

Dear Peter

Thanks a million,
perfect just the way I needed things to run my work runs easier now.

Regards

 

[Peter_SSs]

You are very welcome. Thanks for the follow-up.