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))))