Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Evaluate Date in Text string with vlookup help!

Status
Not open for further replies.
Drivium

Drivium

Technical User
Oct 6, 2011
46
US
I have a situation where I may have more than 1 job for a well. Therefore the well shows up twice in my data, which is ok. But in some cases the cell is a text string with a date at the end such as "Pending as of 7/8/2011". I need a vlookup formula to evaluate the date portion, figure out if it's bigger than the other dates for that time period and use it. For example, on the MAIN tab, in cell L20, I need a vlookup formula to choose between two dates (which may or may not be in text strings)

The Well is LCP017
Record 1 (on data tab)
Prior_Year Q1 Q2 Q3 Q4
10/12/2009 2/1/2011 Pending as of 4/8/2011 5/6/2011 5/6/2011
Record 2 (on data tab)
Prior_Year Q1 Q2 Q3 Q4
10/12/2009 Pending as of 1/23/2011 5/6/2011 5/6/2011 5/6/2011

The result should be:
Prior_Year Q1 Q2 Q3 Q4
10/12/2009 2/1/2011 5/6/2011 5/6/2011 5/6/2011

But, if the PENDING date happened to be higher (which in this case it's not), I still need it to show the "Pending as of" part of the string in the cell. Well LYD247 is an example where this would apply.
 
Sort by date Sort by votes


hi,

Some of us are restricted by company security from downloading files like your.

Please post the data for Well LYD247, if you would like help.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Well, the same logic should work on either. If we can figure out how to make the data for LCP017 work, it should also work for LYD247. However, here is that data:

LYD247
record 1
prioryear q1 q2 q3 q4
2/6/2010 2/6/2010 6/27/2011 Pending as of 7/8/2011 Pending as of 7/8/2011
record 2
prioryear q1 q2 q3 q4
2/6/2010 2/6/2010 Pending as of 4/13/2011 6/27/2011 6/27/2011

should be:
2/6/2010 2/6/2010 6/27/2011 Pending as of 7/8/2011 Pending as of 7/8/2011

In this case, the first record is right, so I need vlookup to use this record. But ideally the formula would know to leave this result in tact.
 
Upvote 0 Downvote



I need vlookup
Click to expand...
Exactly what VALUE are you using to lookup in what RANGE and returning what VALUE?

Please be specific to a posted example and specify what value you expect to be returned.


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
In case it helps to clarify, each of these dates refer to the last install date. Pending means there is a job in the works but it hasn't completed yet, therefore it's pending. But if there is a later date for the same period, that means the job is completed and we need to use that date. If the PENDING date is later than the other record, we need to use the pending record. I had this spreadsheet SINGING until I discovered I have wells with more than 1 job.
 
Upvote 0 Downvote
Exactly what VALUE are you using to lookup in what RANGE and returning what VALUE? Please be specific to a posted example and specify what value you expect to be returned."

I am looking up data by well on the data tab based on a named range called "ESP_Master_Data". So the MAIN tab has the well I'm searching on with VLOOKUP and the DATA tab has the record for the well (as well as the well name). LCP017 and LYD247 have 2 records while the others only have one. In the previous posts, I listed what the returned values should evaluate to. To put it simply, if I have
Q1
PENDING AS OF 5/1/2011 the next record for the same well has 4/1/2011, I need the formula to take PENDING AS OF 5/1/2011 because the date is more recent.
 
Upvote 0 Downvote



But the solution has NOTHING at all to do with VLOOKUP, if you understand VLOOKUP!

You want to PARSE the data that begins with "Pending as of " to determine the date and then compare that date to some other date in the same column.

You have the data to analyse in two rows.

Could there be more than 2 rows?
Where does the "should be:" data go?
Does the sheet look EXACTLY like your examples, containing FIVE columns and SEVEN rows for LCP017, for instance? If not, then what?

Please answer each of these questions.




Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Ultimately, I need to know how to evaluate a date in a text string and compare it to another date. I can figure out how to apply that to a vlookup. So, if it makes it easier and less confusing, let's forget the vlookup thing. Just need a formula that looks at 2 cells and takes the higher date even if the date is in a text string.
 
Upvote 0 Downvote


If your value to evaluate is in C14, then this will return the date either way.
[tt]
=IF(ISNUMBER(C14),C14,DATEVALUE(RIGHT(C14,LEN(C14)-14)))
[/tt]


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Yes, that extracts the date, but I don't need to extract the date. I simply need to check if the date portion of the text string is higher than another cell with a date.

Like:
A1
Pending as of 5/1/2011
A2
4/1/2011
A3
If (a1>a2,a1,a2)
Desired result: Pending as of 5/1/2011

But, this evaluates to an error because the date in a1 is in a text string. So I need to somehow formulate it evaluate the date in the text string
 
Upvote 0 Downvote



Why are you using, if (a1>a2,a1,a2)???

My formula returns a date. all you have to do is use THAT in YOUR formula!!!!

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote


[tt]
=IF(IF(ISNUMBER(A1),A1,DATEVALUE(RIGHT(A1,LEN(A1)-14)))>IF(ISNUMBER(A2),A2,DATEVALUE(RIGHT(A2,LEN(A2)-14))),A1,A2)
[/tt]

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
That did it! THANK YOU!! Sorry for the poor explantion.
 
Upvote 0 Downvote
Works for that scenario...but when I tried to apply it to my vlookup and evaluated to an error. Every where there was an A1 reference, I popped in VLOOKUP($B36,ESP_Master_Status,8,FALSE) and everywhere there was an A2 reference, I popped in VLOOKUP($B36,ESP_Master_Status,8,True). No dice...I dont get it.

=IF(IF(ISNUMBER(VLOOKUP($B36,ESP_Master_Status,8,FALSE)),VLOOKUP($B36,ESP_Master_Status,8,FALSE),DATEVALUE(RIGHT(VLOOKUP($B36,ESP_Master_Status,8,FALSE),LEN(VLOOKUP($B36,ESP_Master_Status,8,FALSE))-14)))>IF(ISNUMBER(VLOOKUP($B36,ESP_Master_Status,8,TRUE)),VLOOKUP($B36,ESP_Master_Status,8,TRUE),DATEVALUE(RIGHT(VLOOKUP($B36,ESP_Master_Status,8,TRUE),LEN(VLOOKUP($B36,ESP_Master_Status,8,TRUE))-14))),VLOOKUP($B36,ESP_Master_Status,8,FALSE),VLOOKUP($B36,ESP_Master_Status,8,TRUE))
 
Upvote 0 Downvote


What is the VLOOKUP range?

What are the lookup values in the first column of the VLOOKUP range?

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
The range is a named range defined by ESP_Master_Data. Which is on the data tab: =Data!$F$3:$N$130. And contains the following columns:
WELL|METHOD|TEAM|FLOOD|Last_Install_Prior_Year|Last_Install_Q1|Last_Install_Q2|Last_Install_Q3|Last_Install_Q4


On Data Tab:
Record 1 for LCP017
J7 (column is prior year) data is: 10/12/2009
K7 (column is q1) data is: 2/1/2011
L7 (column is q2) data is: Pending as of 4/8/2011
M7 (column is q3) data is: 5/6/2011
N7 (column is q4) data is: 5/6/2011
Record 2 for LCP017
J8 (column is prior year) data is: 10/12/2009
K8 (column is q1) data is: Pending as of 1/23/2011
L8 (column is q2) data is: Pending as 5/6/2011
M8 (column is q3) data is: 5/6/2011
N8 (column is q4) data is: 5/6/2011

Record 1 for LYD247
J24 (column is prior year) data is: 2/6/2010
K24 (column is q1) data is: Pending as of 2/6/2010
L24 (column is q1) data is: Pending as 6/27/2011
M24 (column is q1) data is: Pending as of 7/8/2011
N24 (column is q1) data is: Pending as of 7/8/2011
Record 2 for LYD247
J25 (column is prior year) data is: 2/6/2010
K25 (column is q1) data is: Pending as of 2/6/2010
L25 (column is q1) data is: Pending as of 4/13/2011
M25 (column is q1) data is: 6/27/2011
N25 (column is q1) data is: 6/27/2011
 
Upvote 0 Downvote
Goofed on LYD247, they aren't all Q1...see below:


Record 1 for LYD247
J24 (column is prior year) data is: 2/6/2010
K24 (column is q1) data is: Pending as of 2/6/2010
L24 (column is q2) data is: Pending as 6/27/2011
M24 (column is q3) data is: Pending as of 7/8/2011
N24 (column is q4) data is: Pending as of 7/8/2011
Record 2 for LYD247
J25 (column is prior year) data is: 2/6/2010
K25 (column is q1) data is: Pending as of 2/6/2010
L25 (column is q2) data is: Pending as of 4/13/2011
M25 (column is q3) data is: 6/27/2011
N25 (column is q4) data is: 6/27/2011
 
Upvote 0 Downvote
I do appreciate the help so far. It's too bad you can't actually see the spreadsheet, makes this challenging. :)
 
Upvote 0 Downvote
I get an actual value if I change all of the true's and false's to all false, or all true. Not a correct result, but at least it's not an error. I think we're close!
 
Upvote 0 Downvote



I get an actual value
Click to expand...
using what formula in what cell on what sheet?

BTW, I have downloaded your workbook at home.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Tropometer
  • Locked
  • Question
Help with dates
Replies
1
Views
195
SkipVought
S
Cpreston
  • Locked
  • Question
whats wrong with Vlookup 2
2
Replies
20
Views
352
SkipVought
S
Cpreston
  • Locked
  • Question
vlookup issue 1
Replies
4
Views
128
combo
combo
cneill
  • Locked
  • Question
Hi, I need some help with Pre an
Replies
7
Views
174
SkipVought
S
arvarr
  • Locked
  • Question
Excel Formula Help 1
Replies
7
Views
303
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top