On Feb 17, 4:19 pm, Danny Boy
wrote:
Your first formula suggest worked perfectly, and
covered all of the bullet proofing I needed. Thank
you very much for your time!

Glad it worked for you. But I hope you test it carefully, especially
the last condition, namely I4=2 and O4="Yes" and P4="" and TODAY()
EDATE(M4,3).

Based on your second posting (response to my first response), I am not
convinced I understood the intent of your logic correctly in the first
place.

If my "simplified" formula might has it wrong, the fix might be as
simply as changing "Not Completed" to "Completed".

But I'm concerned that there might yet-another condition that I did
not cover.

If you cannot figure out how to correct any mistake I might have in
the simplified formula, post back and fill in the logic error that I
explain in my response to your second response, the explanation of the
FALSE return value.


----- original posting -----

On Feb 17, 4:19*pm, Danny Boy
wrote:
Your first assumpiton was correct Joe (that I4 can only contain 1 or 2 and O4
can only contain Yes or No). 1 vs. 2 is a program level assignment, and Yes
versus No references whether or not the class was completed.

You are also correct that I did not copy and paste directly from the
worksheet as I should have, and thus the errors you noted.

Your first formula suggest worked perfectly, and covered all of the bullet
proofing I needed. Thank you very much for your time!

Best,

Dan



"joeu2004" wrote:
PS....

On Feb 17, 3:06 pm, Danny Boy
wrote:
=IF(AND(I4=1,O5="Yes"),"Level 1 Program [...]

I suspect O5 is a typo (in the worksheet or just the posting?). *I
suspect it should be 04.

Again, if you did not cut-and-paste from your worksheet to your
posting, it would be a good practice in the future, especially if the
issue is a likely typo in a formula, as you suspect.

PS: *It also seems like there is one less closing parenthesis in your
posting, further evidence that you probably did not cut-and-paste from
the worksheet, as you should.

As for any simplification, that depends on what assumptions can be
made about the contents of I4 and O4, and how bullet-proof you want to
make the formula.

For example, I4 can only have 1 or 2, and O4 can only have "Yes" or
"No", and you are not trying to detect unexpected values, you could
write:

=if(I4=1,if(O4="Yes","Level 1 Program Completed",
* * * * * * "Level 1 Program Not Completed"),
* * if(O4="No","Level 2 Program Not Completed",
* * if(P4<"","Level 2 Program Completed",
* * if(today()edate(M4,3),"Level 2 Program Not Completed",""))))

If that is not sufficient bullet-proofing, perhaps the following
modification would suffice:

if(or(and(I4<1,I4<2),and(O4<"Yes",O4<"No")),""
* *if(I4=1,[...as above...])))))

HTH

----- original posting -----

On Feb 17, 3:06 pm, Danny Boy
wrote:
I'm having trouble getting the formula below to work after adding the
additional condition of:

Additional Condition:

IF(TODAY()EDATE(M4,3)

Full Formula:

=IF(AND(I4=1,O5="Yes"),"Level 1 Program
Completed",IF(AND(I4=1,O4="No"),"Level 1 Program Not
Completed",IF(AND(I4=2,O4="Yes",P4<""),"Level 2 Program
Completed",IF(TODAY()EDATE(M4,3),AND(I4=2,O4="Yes "),"Level 2 Program Not
Completed",IF(AND(I4=2,O4="No"),"Level 2 Program Not Completed","")))

Any suggestions to rectify this problem?

Thanks for any suggestions!