I need to update the following Excel formula

I need to update the following formula:

=IFERROR(IF($A$2>=DATEVALUE(LEFT(H10,FIND(" ",H10)-1)),IF(AND(DATEVALUE(LEFT(H10,FIND(" ",H10)-1))>K10,DATEVALUE(LEFT(H10,FIND(" ",H10)-1))<=L10),"INFO1",IF(DATEVALUE(LEFT(H10,FIND(" ",H10)-1))>L10,"INFO2","")),""),"")

Right now there is only a date like 10/31/15 (E) entered into column H. I will also be inserting a similiar date format, 10/31/15 (A) when needed. FYI (E) = Estimated & (A) = Actual

So H10 could look like:

10/31/15 (E)
10/31/15 (A)

I need to update the formula to see if there is an "(A)" in H10. If so, it returns blank. If not, it proceeds to run the rest of the formula.
LVL 1
wrt1meaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sean FitzpatrickSr Lab Systems EngineerCommented:
Do you have the ability to separate the fields and put the E and A into it's own column? Then you could search by the column instead of having to parse the value.  Then just put everything together at the end to form the value you want.

ie. 10/13/15 in column A and A in column B, then column c = A + B, you could parse through column B much faster if it's just the value by itself.
0
Rgonzo1971Commented:
Hi,

pls try

=IFERROR(IF(ISERROR(SEARCH("(A)",H10)),IF($A$2>=DATEVALUE(LEFT(H10,FIND(" ",H10)-1)),IF(AND(DATEVALUE(LEFT(H10,FIND(" ",H10)-1))>K10,DATEVALUE(LEFT(H10,FIND(" ",H10)-1))<=L10),"INFO1",IF(DATEVALUE(LEFT(H10,FIND(" ",H10)-1))>L10,"INFO2","")),""),""),"")

Regards
0
barry houdiniCommented:
This one works for me

=IFERROR(IF($A$2>=SUBSTITUTE(H10,"(E)","")+0,LOOKUP(SUBSTITUTE(H10,"(E)","")+0,K10:L10,{"INFO1","INFO2"}),""),"")

If H10 ends with (A) that will trigger an error so the IFERROR will return a blank

regards, barry
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

wrt1meaAuthor Commented:
Hye guys...I am trying to test it out, but my PC is currently going nuts....give me just a few.
0
barry houdiniCommented:
My version needs a little tweak......

=IFERROR(IF($A$2>=SUBSTITUTE(H10,"(E)","")+0,LOOKUP(SUBSTITUTE(H10,"(E)","")+1,K10:L10,{"INFO1","INFO2"}),""),"")

barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wrt1meaAuthor Commented:
THANKS FOR THE HELP GUYS!

HEY BARRY....HAVENT SEEN YOU IN A WHILE. HOPE ALL IS WELL. DO YOU MIND PROVIDING AN EXPLANATION ON THE FORMULA???

YOU TOO IF YOU DONT MIND RGONZO1971....
0
Glenn RayExcel VBA DeveloperCommented:
Hey wrt1mea,

I was monitoring this thread and, if I'm not out of bounds, I'll explain the two formula approaches.  Good exercise for me also.

Rgonzo1971:
The whole formula is wrapped in an IFERROR statement to produce a blank cell if there are any issues.  There is first a test for (A) inside H10; if it's not there, a blank cell is returned.  Otherwise, he parses out the cell by finding the space between the date and (E) or (A) status, using the leftmost characters before the space to first determine if the date in A2 is greater than the datevalue in H10, and if so, then compare to the date in cell K10 and L10.  Exploded view:
=IFERROR(
    IF(ISERROR(SEARCH("(A)",H10)),
        IF($A$2>=DATEVALUE(LEFT(H10,FIND(" ",H10)-1)),
            IF(AND(DATEVALUE(LEFT(H10,FIND(" ",H10)-1))>K10,DATEVALUE(LEFT(H10,FIND(" ",H10)-1))<=L10),
                "INFO1",
                IF(DATEVALUE(LEFT(H10,FIND(" ",H10)-1))>L10,
                    "INFO2",
                "")
            ),
        ""),
    ""),
"")


barry houdini:
The whole formula is wrapped in an IFERROR statement to produce a blank cell if there are any issues.  The character string (E) is removed (using the SUBSTITUTE function) and zero is added to make the result numeric.  If A2 is greater than or equal to this result, the formula then uses the LOOKUP function to see if the same numeric result from the first test - plus 1 - falls within the two dates shown in K10:L10 and if so, returns the related string value contained in the array {"INFO1","INFO2"}.

Exploded view:
=IFERROR(
    IF($A$2>=SUBSTITUTE(H10,"(E)","")+0,
        LOOKUP(SUBSTITUTE(H10,"(E)","")+1,K10:L10,{"INFO1","INFO2"}),
    ""),
"")
0
wrt1meaAuthor Commented:
Outstanding Glenn Ray!!!!

Much Appreciated...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.