Time approximation in Index Match

[snejsnej]

Hi there,

I have a column containing a large-ish number of cells (column "A"). Each cell contains a concatenation of a dollar figure (can be pos or neg) & a card number & a datetime (MM/DD/YY hh:ss). Example:

[TABLE="width: 297"]
<tbody>[TR]
[TD="class: xl64, width: 297"]-2.23|6202xxxxxxxxxxxx|08/06/13 10:02[/TD]
[/TR]
</tbody>[/TABLE]

I have generally been successful looking for an exact match in two other columns (columns "B" & "C") using Index Match.

The problem I have is that the minute portion of the datetime group in the string (i.e. the last 2 characters) may be off by 1 minute in columns B & C compared to what is in column A. For example:

Column A: -2.23|6202xxxxxxxxxxxx|08/06/13 10:02
Column B & C: -2.23|6202xxxxxxxxxxxx|08/06/13 10:01

The data from B & C are from a different system: they are written to this system very shortly before being written to the second system (data in column A).

This means that most of the time, the data match. But every so often they don't (i.e. when the data in the first system is written a few milliseconds before the rollover to the next minute).

Is there a way to do a check with a tolerance of 1 minute on the datetime section of the string? Or how could this best be done?

Many thanks,
Jens

 

[snejsnej]

Hi Damon,

Sorry about this, but when I wrote yesterday that your UDF worked, I was a little hasty, i.e. I was sure it would work but didn't actually check it (I was in a hurry and wanted to thank you for your efforts...).

Anyway, I'm hoping you can help me out. I tried your macro but nothing is returning, i.e. the cells all remain blank.

I'm using it as follows: matchdatetime(G2,L:L,K:K), where G2 is the value to be searched for in column L, with the corresponding value in column K to be returned in E2 (where I entered the UDF).

Many thanks,
Jens

 

[Damon Ostrander]

Hi Jens,

At the absolute minimum it should be returning "---" if no match occurs. It worked quite well with a test worksheet I made. I am going to PM you with my email address, and if you can email your workbook to me I will debug it.

Damon