Looking up text value version from today()
 

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

Looking up text value version from today()
 

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

Looking up text value version from today()
 

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" wrote:

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

Looking up text value version from today()
 

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

Looking up text value version from today()
 

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

Looking up text value version from today()
 

Ok, good deal. Thanks for the feedback!

Biff

"SteveC" wrote in message
...
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))))