| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)))) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using TODAY function to return a date as text | Excel Discussion (Misc queries) | |||
| Text and Today() function in VBA | Excel Worksheet Functions | |||
| Help Required!!! Macro to load data from version 1 to version 2 | Excel Worksheet Functions | |||
| How do I convert text to numbers in an old version of excel | Excel Worksheet Functions | |||
| Recover earlier version of excel sheet after new version saved? | Excel Discussion (Misc queries) | |||
Hybrid Mode
