Help with IF formula with Negative Time YES OR NO

[Lukma]

Hi Friends

Please can someone help with a formula with IF for YES or NO in Column J in my Data Sheet

I have a Time in Column I so i need formula is Column J display the time with negative sign as YES and without negative and NO
but some how i cant seem to get it with my formula in Column J

Appreciate a help with This

Regards

OSMS-Onshore Jetty Operations vessels Tracking.xlsx
	A	B	C	D	E	F	G	H	I	J
1	NO
2	YES
3	Mus Num	Supply Vessel	Vessel Arrive Fairway Buoy Date&Time	MUS Channel Status C/O	Manifest Date & Time	Manifest passed On Time	Control Tower Permission Date & Time	Perm Granted On Time	Permission Hours	YES OR NO
4	1154	CECILIE-K			01/05/23 12:40	NO	01/05/23 12:44	Met	0:04	Yes
5	1155	Z-OCEAN	01/05/23 14:30		01/05/23 13:40	NO	01/05/23 13:51	Met	0:11	Yes
6	1156	SWISSCO RUBY	02/05/23 06:00		01/05/23 21:57	YES	01/05/23 22:40	Met	-07:20	Yes
7	1157	LCT-SHEWELEH	01/05/23 16:00		01/05/23 15:00	NO	01/05/23 18:27	Not Met	3:27	Yes
8	1158	A-RANGER	01/05/23 20:00		01/05/23 16:57	YES	01/05/23 17:00	Met	-03:00	Yes
9	1159	ADNOC-S02	01/05/23 21:10		01/05/23 20:14	NO	02/05/23 03:00	Not Met	6:46	Yes
Sheet1

Cell Formulas
Range	Formula
H4:H9	H4	=IFERROR(IF(G4>E4+TIME(3,0,0),"Not Met","Met"),"")
I4:I9	I4	=IF(IF(AND(F4=$A$1,G4>C4-TIME(3,0,0)),G4-E4,IF(AND(F4=$A$2,G4>C4-TIME(3,0,0)),G4-C4,G4-C4))<0,"-"&TEXT(ABS(IF(AND(F4=$A$1,G4>C4-TIME(3,0,0)),G4-E4,IF(AND(F4=$A$2,G4>C4-TIME(3,0,0)),G4-C4,G4-C4))),"hh:mm"),IF(AND(F4=$A$1,G4>C4-TIME(3,0,0)),G4-E4,IF(AND(F4=$A$2,G4>C4-TIME(3,0,0)),G4-C4,G4-C4)))
J4:J5,J7:J9	J4	=IF(I4>0,"Yes","No")
J6	J6	=IF(I6>"","Yes","No")
F4:F9	F4	=IFERROR(IF(E4>C4-TIME(3,0,0),"NO","YES"),"")

 

[etaf]

as that is text try
=IF(LEFT(I4,1)="-","Yes","No")

Book21
	A	B	C	D	E	F	G	H	I	J
1	NO
2	YES
3	Mus Num	Supply Vessel	Vessel Arrive Fairway Buoy Date&Time	MUS Channel Status C/O	Manifest Date & Time	Manifest passed On Time	Control Tower Permission Date & Time	Perm Granted On Time	Permission Hours	YES OR NO
4	1154	CECILIE-K			45047.5278	NO	45047.5306	Met	0.002777778	No
5	1155	Z-OCEAN	45047.6042		45047.5694	NO	45047.5771	Met	0.007638889	No
6	1156	SWISSCO RUBY	45048.25		45047.9146	YES	45047.9444	Met	-07:20	Yes
7	1157	LCT-SHEWELEH	45047.6667		45047.625	NO	45047.7688	Not Met	0.14375	No
8	1158	A-RANGER	45047.8333		45047.7063	YES	45047.7083	Met	-03:00	Yes
9	1159	ADNOC-S02	45047.8819		45047.8431	NO	45048.125	Not Met	0.281944444	No
Sheet1

Cell Formulas
Range	Formula
H4:H9	H4	=IFERROR(IF(G4>E4+TIME(3,0,0),"Not Met","Met"),"")
I4:I9	I4	=IF(IF(AND(F4=$A$1,G4>C4-TIME(3,0,0)),G4-E4,IF(AND(F4=$A$2,G4>C4-TIME(3,0,0)),G4-C4,G4-C4))<0,"-"&TEXT(ABS(IF(AND(F4=$A$1,G4>C4-TIME(3,0,0)),G4-E4,IF(AND(F4=$A$2,G4>C4-TIME(3,0,0)),G4-C4,G4-C4))),"hh:mm"),IF(AND(F4=$A$1,G4>C4-TIME(3,0,0)),G4-E4,IF(AND(F4=$A$2,G4>C4-TIME(3,0,0)),G4-C4,G4-C4)))
J4:J9	J4	=IF(LEFT(I4,1)="-","Yes","No")
F4:F9	F4	=IFERROR(IF(E4>C4-TIME(3,0,0),"NO","YES"),"")