We help IT Professionals succeed at work.

I need to update the following Excel formula

wrt1mea
wrt1mea asked
on
126 Views
Last Modified: 2014-08-18
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.
Comment
Watch Question

Sean FitzpatrickSr Lab Systems Engineer

Commented:
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.
CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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

Author

Commented:
Hye guys...I am trying to test it out, but my PC is currently going nuts....give me just a few.
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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....
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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"}),
    ""),
"")

Author

Commented:
Outstanding Glenn Ray!!!!

Much Appreciated...

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.