Formula to calculate waiting hours between three date& time on my data

[Lukma]

HI Guys

Please i need a help with the solution of my data vessel arrive time at FWB Column C2 and loading manifest paper work passed Time in Column D2 and permission is granted to vessel to enter port in Column F2

my aim is to get the waiting hours it took ESNAAD to grant permission to vessel after receiving manifest and total hours Vessel takes waiting for permission at FWB after or before manifest passed to ESNAAD.

Appreciate anyone with the formula to get the waiting hours for vessel to get permission take in FWB

Book1
	A	B	C	D	E	F	G	H	I
1	Mus-No	Supply Vessel	FWB Date & Time	Manifest Passed ESNAAD Date & Time	Total hrs takes Vessel Waiting permision After or before manifest time	ESNAAD Permission	Total hrs takes take to grant Permision After manifest passed	Arrive Date & Time	Commence Cargo OP
2	Mus-3708	SMIT LUZON	31/12/20 22:42	31/12/20 13:35		01/01/21 04:14		01/01/21 08:30	01/01/21 08:42
3	Mus-3709	SMIT LUMUT	31/12/20 22:30	31/12/20 18:00		31/12/20 23:00		01/01/21 05:20	01/01/21 07:58
4	Mus-3711	MAC PHOENIX	31/12/20 21:42	31/12/20 20:35		01/01/21 05:05		01/01/21 09:45	01/01/21 13:30
5	Mus-3712	ADNOC-812	01/01/21 01:30	31/12/20 21:00		01/01/21 04:45		01/01/21 08:05	01/01/21 08:25
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:E5	Cell Value	contains "Maintenance"	text	NO

 

[alansidman]

With Power Query/Get and Transform. See last column.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Mus-No", Int64.Type}, {"Supply Vessel", type text}, {"FWB#(lf)Date & Time", type datetime}, {"Manifest Passed#(lf)ESNAAD#(lf)Date & Time", type datetime}, {"Total hrs takes Vessel Waiting permision #(lf)After or before manifest time", type any}, {"ESNAAD#(lf)Permission", type datetime}, {"Total hrs takes take to grant Permision#(lf)After manifest passed", type any}, {"Arrive #(lf)Date & Time", type datetime}, {"Commence #(lf)Cargo OP", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [#"ESNAAD#(lf)Permission"]-[#"FWB#(lf)Date & Time"]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Wait Time"}})
in
    #"Renamed Columns"

Book1
	A	B	C	D	E	F	G	H	I	J
1	Mus-No	Supply Vessel	FWB Date & Time	Manifest Passed ESNAAD Date & Time	Total hrs takes Vessel Waiting permision After or before manifest time	ESNAAD Permission	Total hrs takes take to grant Permision After manifest passed	Arrive Date & Time	Commence Cargo OP	Wait Time
2	3708	SMIT LUZON	12/31/2020 22:42	12/31/2020 13:35		1/1/2021 4:14		1/1/2021 8:30	1/1/2021 8:42	5:32
3	3709	SMIT LUMUT	12/31/2020 22:30	12/31/2020 18:00		12/31/2020 23:00		1/1/2021 5:20	1/1/2021 7:58	0:30
4	3711	MAC PHOENIX	12/31/2020 21:42	12/31/2020 20:35		1/1/2021 5:05		1/1/2021 9:45	1/1/2021 13:30	7:23
5	3712	ADNOC-812	1/1/2021 1:30	12/31/2020 21:00		1/1/2021 4:45		1/1/2021 8:05	1/1/2021 8:25	3:15
Table1

 

[Lukma]

thanks and i appreciate but i feel like have formula to get it more easy for me

if there is other mean of formula i will be glad give it a try

Regards

 

[Lukma]

Hi Alansidman

Thanks so much but am not too familiar with Power Query still in learning stage that am must be frank with
i only extract few or my data to what i need because my data in much which i enter daily based on the operation

If there is other formula i will more pleased

Regards

​

 

[alansidman]

This worked for me

=F2-D2 formatted special as hh:mm and copied down.

 

[Lukma]

Thanks yes i know but how did you get the waiting time in last column 5:32 is there any formula to do this without using power Query

am not to good in power Query that's why i prefer a formula that do this.

Appreciate if anyone can help out

Regards

 

[alansidman]

Post #5 is not about Power Query. It is a simple excel formula.