If time matches list of time within 5 minutes

skoorBmaS

New Member
Joined
Feb 5, 2016
Messages
34
Hi All, I currently have the below formula:

Code:
[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?
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,518
Messages
5,523,352
Members
409,512
Latest member
Exceldoktor

This Week's Hot Topics

Top