Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 115
  • Last Modified:

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.
0
wrt1mea
Asked:
wrt1mea
2 Solutions
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now