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.
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.
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"}.
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.