Looking up text value version from today()

[Guest]

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$66,"m/dd/yyyy"),Data!$A$3:$G$254,2,FALSE)),VLOOKUP(TEXT(H$66,"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(VLOOKUP(TEXT(H$66,"m/dd/yyyy"),Data!$A$3:$G$254,2,FALSE)),VLOOKUP(TEXT(H$66,"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(ISERROR(VLOOKUP(TEXT(H$66,"m/dd/yyyy"),Data!$A$3:$G$254,2,FALSE)),VLOOKUP(TEXT(H$66,"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(VLOOKUP(TEXT(H$66,"m/dd/yyyy"),Data!$A$3:$G$254,2,FALSE)),VLOOKUP(TEXT(H$66,"mm/dd/yyyy"),Data!$A$3:$G$254,2,FALSE),VLOOKUP(TEXT(H$66,"m/dd/yyyy"),Data!$A$3:$G$254,2,FALSE))))

 

[T. Valko]

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

 

[Guest]

Biff, thanks.

H66 is this: =today()-1 (returning 2/14/2007)

the date in h66 is in this format: *3/14/2001


I don't think converting H66 into =text(today()-1,"mm/dd/yyyy") will work
because then i have the same problem -- 4 different possible formats to match
up against.

Thanks for taking a look... I'll see if I can tweak your suggestions (not
currently working since H66 is not in a text format).

 

[T. Valko]

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 Data>Text to Columns
Click Next>Next>Date
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

 

[Guest]

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]

Ok, good deal. Thanks for the feedback!

Biff

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!