Biff, thanks. I ended up inserting a new column b and using the
=datevalue(a3) in col B. That did the trick! Thanks very much!

"T. Valko" wrote:

I think I have it backwards. H66 is a true Excel date and column A of your
lookup table are the *TEXT* dates in various formats.

Try converting the TEXT dates to true Excel dates:

Select the range of text dates.
Go to the menu DataText to Columns
Click NextNextDate
Select the date format MDY
Finish

Then you should be able to use a simpler formula:

=VLOOKUP(H$66,Data!$A$3:$G$254,2,FALSE)

Biff

"T. Valko" wrote in message
...
What's in H66? Is it a *TEXT* date that might be in any number of formats?
And column A of your lookup table has true Excel dates?

If so, try one of these:

=VLOOKUP(H$66+0,Data!$A$3:$G$254,2,FALSE)

=VLOOKUP(DATEVALUE(H$66),Data!$A$3:$G$254,2,FALSE)

Biff

"SteveC" wrote in message
...
Hi,

I data service provider returns dates in text format. The problem with
this
is that it either returns dates in the format of mm/dd/yyyy, mm/d/yyyy,
m/dd/yyyy or m/d/yyyy.

So I've created 4 nested if statements to convert a value produced by
=today() to lookup these values -- very ugly! (see below). Anyone know
of a
better way?

thanks very much for your time.

SteveC

=IF(ISERROR(IF(ISERROR(IF(ISERROR(VLOOKUP(TEXT(H$6 6,"m/dd/yyyy"),Data!$A$3:$G$254,2,FALSE)),VLOOKUP(TEXT(H$6 6,"mm/dd/yyyy"),Data!$A$3:$G$254,2,FALSE),VLOOKUP(TEXT(H$66 ,"m/dd/yyyy"),Data!$A$3:$G$254,2,FALSE))),VLOOKUP(TEXT(H$ 66,"mm/d/yyyy"),Data!$A$3:$G$254,2,FALSE),IF(ISERROR(VLOOKU P(TEXT(H$66,"m/dd/yyyy"),Data!$A$3:$G$254,2,FALSE)),VLOOKUP(TEXT(H$6 6,"mm/dd/yyyy"),Data!$A$3:$G$254,2,FALSE),VLOOKUP(TEXT(H$66 ,"m/dd/yyyy"),Data!$A$3:$G$254,2,FALSE)))),VLOOKUP(TEXT(H $66,"m/d/yyyy"),Data!$A$3:$G$254,2,FALSE),IF(ISERROR(IF(ISE RROR(VLOOKUP(TEXT(H$66,"m/dd/yyyy"),Data!$A$3:$G$254,2,FALSE)),VLOOKUP(TEXT(H$6 6,"mm/dd/yyyy"),Data!$A$3:$G$254,2,FALSE),VLOOKUP(TEXT(H$66 ,"m/dd/yyyy"),Data!$A$3:$G$254,2,FALSE))),VLOOKUP(TEXT(H$ 66,"mm/d/yyyy"),Data!$A$3:$G$254,2,FALSE),IF(ISERROR(VLOOKU P(TEXT(H$66,"m/dd/yyyy"),Data!$A$3:$G$254,2,FALSE)),VLOOKUP(TEXT(H$6 6,"mm/dd/yyyy"),Data!$A$3:$G$254,2,FALSE),VLOOKUP(TEXT(H$66 ,"m/dd/yyyy"),Data!$A$3:$G$254,2,FALSE))))