MATCH FUNCTION AND SERIAL DATE CONFLICTS

[Jones the Scouse]

I am using the Match Function to locate a user-entered date in an array
range that includes numbers, text and dates. This has worked fine for
3 years but I have today encountered a problem where Match erroneously
locates a number in the array that is the same value as the serial date
number of the date that is being searched for i.e. Date being searched
for 3/11/06 (d/mm/yy) but matches number 39024 (the serial date for 3rd
November 2006). The chances of this happening again are rare, but I
would like to eliminate the possibility.

Does anyone know of a way of forcing Excel to only match with dates,
rather than the serial date equivalent? I've tried changing formats of
various cells in the spreadsheets but Excel always seems to read the
date (behind the scenes) as the serial date number.

 

[Bob Phillips]

I can't see that this is avoidable because Excel does not have a date type,
it is just a serial number with a specific format. So a cell with 03/11/06
and another with 39024 have the same value as you know.

You could write your own UDF and test the format, assuming you know the
format, like this

Function SpecialMatch(lookup_val, lookup_range As Range)
Const DATE_FORMAT As String = "dd-mmm-yyyy" '<== change to suit
Dim rng As Range
Dim cPrev As Long
Dim tmp

Set rng = lookup_range
cPrev = 0
Do
tmp = 0
On Error Resume Next
tmp = Application.Match(lookup_val, rng, 0)
On Error GoTo 0
If tmp = 0 Then
SpecialMatch = CVErr(xlErrNA)
Exit Do
ElseIf rng.Cells(tmp, 1).NumberFormat = DATE_FORMAT Then
SpecialMatch = tmp + cPrev
Exit Do
Else
Set rng = rng.Offset(tmp, 0).Resize(rng.Rows.Count - tmp, 1)
cPrev = cPrev + tmp
End If
Loop Until tmp = 0

End Function

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Biff]

The only thing I can think of is to use a helper column that returns the
format of the lookup_array and then base the MATCH on both the format and
the value of the lookup_array:

.............A.............B
1......39024...........
2........text..............
3......3/11/06..........

This formula entered in B1 and copied down:

=CELL("format",A1)

This formula will return a code for the format of the cells in column A. See
Help for all the different codes. Based on the sample above:

G
G
D4

G = GENERAL
D4 = one of the DATE codes

Then you can base your MATCH formula on both the look_value and the format
code.

Biff

 

[Jones the Scouse]

Thanks for the information - I will try it out sometime, but it's a bit
complicated for my little brain.

I've worked out a way to get over the problem by creating a separate
and hidden look-up table that gets the dates only from the original
array table and I then use Match on the "date array".

Thanks again.
J-t-S

 

[Lori]

You could make a text copy of the lookup column and use this for the
lookup. If it's a long column you could try this: Insert a column and
format as text, copy the lookup column to the clipboard (by pressing
ctrl+C twice) then paste to the new column using the clipboard icon.