Finding difference in time

[Rubygas14]

Need Help with the following Dates.

i have a a file but cannot attach i have copied the table below hope.

[TABLE="width: 987"]
<colgroup><col><col span="2"><col><col><col span="3"><col span="2"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Allocation time[/TD]
[TD]last picked time[/TD]
[TD]time and date needs to be ready
[/TD]
[TD]picked late ? [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 2"]Allocated Late[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]16/11/2018 14:27[/TD]
[TD="align: right"]16/11/2018 15:00[/TD]
[TD="align: right"]16/11/2018 14:00[/TD]
[TD]"yes" by how long by, so in this case 1 hour show 01:00:00 in cell G3 and yes in F3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Not Late[/TD]
[TD="align: right"]00:27:00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]If F3= "yes" was Cell B3 and C3 within 1 hour of each other this case "yes" show time difference in cell K3 and "Not late" in cell J3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]if B3 is >1 and <5 hours against C3 show "late" in J3 and time difference in K3

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Please Help

Thanks

 

[etaf]

Re: Need help finding difference in time

tried some of this and its reasonable straight forward BUT
need to know which cells to use
on the TABLE the date and times are shown as B,C & D
is that correct or have they moved due to pasting here, and so should be A,B & C

"yes" by how long by, so in this case 1 hour show 01:00:00 in cell G3 and yes in F3

C2-D2 formatted as Time , use [H]|:MM to get greater than 1 day

=IF ( C2 > D2 , "Yes", "No")

If F3= "yes" was Cell B3 and C3 within 1 hour of each other this case "yes" show time difference in cell K3 and "Not late" in cell J3

=IF(F2="Yes",IF((C2-B2)<time(1,0,0),"not late","late"))


time difference = (C2-B2), but if it can be a negative time - ie allocated is after picked
then it will show a negative time

if B3 is >1 and <5 hours against C3 show "late" in J3 and time difference in K3

<time(5,0,0))
=AND(C2-B2 > = TIME(1,0,0),C2-B2 < = TIME(5,0,0))

=IF( AND(C2-B2 > = TIME(1,0,0),C2-B2 < = TIME(5,0,0)) , "Late", "" )<time(5,0,0)) ,="" "late"="" ""="" )


time difference

C2 - B2</time(5,0,0))></time(5,0,0))
</time(1,0,0),"not>

 

[Rubygas14]

Re: Need help finding difference in time

if you can go by the cell that were pasted on here B1 for Allocation time
ive had a loogwith the formula is this to create 1 long formula or do they need to be placed in seperate cells.

Thanks in Advance.

 

[etaf]

Re: Need help finding difference in time

separate cells, based on your questions - so i have used Column B in the above example

=IF(F2="Yes",IF((C2-B2) < TIME(1,0,0),"Not Late","Late"))

but your questions asks for different results in J3 and K3
in your question here
if B3 is >1 and <5 hours against C3 show "late" in J3 and time difference in K3
you ask for the answers to be put in J3 and K3 again - and what time difference between what cells

i stated a sample sheet here - with most of the answers
https://www.dropbox.com/s/ziu1xmqmap9siw5/timecalc_etaf.xlsx?dl=0