If time matches list of time within 5 minutes

[skoorBmaS]

Hi All, I currently have the below formula:

[COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IF[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]AND[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]R2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"c"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]MATCH[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]D2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]'Activity Report'!$A$3:$A$8[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]MATCH[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]B2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#4285F4][FONT=Inconsolata]'Activity Report'!$C$3:$C$8[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Confirmed"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"False"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR]

The Cell B2 and the column of C in the Activity Report are time and dates, formatted as (dd/mm/yyyy hh:mm:ss). Effectively, when I am matching B2 to the column of C in the Activity report, what I would actually like to do is to count as a match if it is within 5 minutes of the time. With both data sets, we can't reliably confirm that the two times would match, however they are likely to be within 5 minutes of each other.

Anyone know how to do this?

 

[DanteAmor]

Try this


=IF(AND(R2="c",OR(AND(INDEX('Activity Report'!$A$3:$A$8,MATCH(D2,'Activity Report'!$A$3:$A$8))-"00:05"<=D2,INDEX('Activity Report'!$A$3:$A$8,MATCH(D2,'Activity Report'!$A$3:$A$8))+"00:05">=D2),AND(INDEX('Activity Report'!$A$3:$A$8,MATCH(D2,'Activity Report'!$A$3:$A$8)+1)-"00:05"<=D2,INDEX('Activity Report'!$A$3:$A$8,MATCH(D2,'Activity Report'!$A$3:$A$8)+1)+"00:05">=D2)),OR(AND(INDEX('Activity Report'!$C$3:$C$8,MATCH(B2,'Activity Report'!$C$3:$C$8))-"00:05"<=B2,INDEX('Activity Report'!$C$3:$C$8,MATCH(B2,'Activity Report'!$C$3:$C$8))+"00:05">=B2),AND(INDEX('Activity Report'!$C$3:$C$8,MATCH(B2,'Activity Report'!$C$3:$C$8)+1)-"00:05"<=B2,INDEX('Activity Report'!$C$3:$C$8,MATCH(B2,'Activity Report'!$C$3:$C$8)+1)+"00:05">=B2))),"Confirmed","False")

 

[Marcelo Branco]

Maybe...

=IF(AND(R2="c",ISNUMBER(MATCH(D2,'Activity Report'!A3:A8,0)),ISNUMBER(MATCH(1,INDEX(--(ABS(B2-'Activity Report'!C3:C8)<=5/1440),),0))),"Confirmed","False")

Not sure about the 0 (I assumed you need an exact match)

M.