Help With my Formula in Column I

[Lukma]

Hi friends

Good day, please I need help with my formula in COLUMN I, I don't know if I got it right.
to explain all I need the formula to do is two criteria
IF Column F4=NO and Column G4 is greater Than 3Hrs before Column C4, then Subtract Column G From Column E
IF Column F4=Yes and Column G4 is greater Than 3Hrs before Column C4, then Subtract Column G From Column C

But what am getting is false I need the FALSE to show negative or time.

Appreciate any help and could solve this or a better formula

Thanks

OSMS-Onshore Jetty Operations vessels Tracking.xlsx
	A	B	C	D	E	F	G	H	I
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
4	1154	CECILIE-K	01/05/23 10:00		01/05/23 12:40	NO	01/05/23 12:44	Met	0:04
5	1155	Z-OCEAN	01/05/23 14:30		01/05/23 13:40	NO	01/05/23 13:51	Met	0:11
6	1156	SWISSCO RUBY	02/05/23 06:00		01/05/23 21:57	YES	01/05/23 22:40	Met	FALSE
7	1157	LCT-SHEWELEH	01/05/23 16:00		01/05/23 15:00	NO	01/05/23 18:27	Not Met	3:27
8	1158	A-RANGER	01/05/23 20:00		01/05/23 16:57	YES	01/05/23 17:00	Met	FALSE
9	1159	ADNOC-S02	01/05/23 21:10		01/05/23 20:14	NO	02/05/23 03:00	Not Met	6:46
Sheet1

Cell Formulas
Range	Formula
H4:H9	H4	=IFERROR(IF(G4>E4+TIME(3,0,0),"Not Met","Met"),"")
I4:I9	I4	=IF(F4=$A$1,IF(G4>C4-TIME(3,0,0),G4-E4,IF(F4=$A$2,IF(G4>C4-TIME(3,0,0),G4-C4))))
F4:F9	F4	=IFERROR(IF(E4>C4-TIME(3,0,0),"NO","YES"),"")

 

[Sufiyan97]

What you want to return if non of above two condition are met?

 

[Lukma]

What you want to return if non of above two conditions are met?

Hi Sufiyan

Thank you for your response if both are met I need to show the negative hours permission was grated in column I
to display the negative time

A	B	C	D	E	F	G	H	I
1156	SWISSCO RUBY	02/05/23 06:00		01/05/23 21:57	YES	01/05/23 22:40	YES	-7:20

Regards

 

[Sufiyan97]

Looks not good but works

Try

Excel Practice 05.22.2023.xlsx
	A	B	C	D	E	F	G	H	I
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
4	1154	CECILIE-K	45047.42		45047.53	NO	45047.53	Met	0:04
5	1155	Z-OCEAN	45047.6		45047.57	NO	45047.58	Met	0:11
6	1156	SWISSCO RUBY	45048.25		45047.91	YES	45047.94	Met	-07:20
7	1157	LCT-SHEWELEH	45047.67		45047.63	NO	45047.77	Not Met	3:27
8	1158	A-RANGER	45047.83		45047.71	YES	45047.71	Met	-03:00
9	1159	ADNOC-S02	45047.88		45047.84	NO	45048.13	Not Met	6:46
10
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)))
F4:F9	F4	=IFERROR(IF(E4>C4-TIME(3,0,0),"NO","YES"),"")

 

[Lukma]

Looks not good but works

Try

Excel Practice 05.22.2023.xlsx
	A	B	C	D	E	F	G	H	I
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
4	1154	CECILIE-K	45047.42		45047.53	NO	45047.53	Met	0:04
5	1155	Z-OCEAN	45047.6		45047.57	NO	45047.58	Met	0:11
6	1156	SWISSCO RUBY	45048.25		45047.91	YES	45047.94	Met	-07:20
7	1157	LCT-SHEWELEH	45047.67		45047.63	NO	45047.77	Not Met	3:27
8	1158	A-RANGER	45047.83		45047.71	YES	45047.71	Met	-03:00
9	1159	ADNOC-S02	45047.88		45047.84	NO	45048.13	Not Met	6:46
10
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)))
F4:F9	F4	=IFERROR(IF(E4>C4-TIME(3,0,0),"NO","YES"),"")

Dear surfiyan

good day, many thanks to you just perfect as I needed it.
I appreciate your support

thanks

 

[Sufiyan97]

You're welcome.
Glad I could help!