Re: Re-post as I still can't seem to get this to work 100%

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
work*** 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 <Danny...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
=IF(AND(I4=1,O5="Yes"),"Level 1 Program [...]

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

Again, if you did not cut-and-paste from your work*** 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 work***, 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 <Danny...@xxxxxxxxxxxxxxxxxxxxxxxxx>
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!


.

Loading