On Feb 18, 3:07 am, Danny Boy
wrote:
The only thing I need to modify is the actual language
of the flags themselves (back to what I originally had)

Right, a rather silly oversight on my part. In case the necessary
change is not obvious:

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


I'm still fairly an Excel "newbie".

So let me explain a few things.

The "&" is the concatenation operator. So "Level "&I4 becomes "Level
1" or "Level 2", depending on the value of I4. This avoids needing to
have separate IF conditions for each level.

The logic OR(I4=1,P4<"") is equivalent to saying ``I4=1, or I4=2 and
P4<""``. The ``I4=2 and`` part is implied by the fact that if I4<1
(that is, the first OR argument is false), we assume that I4=2.

The logic OR(O4="No",AND(I4=2,TODAY()EDATE(M4,3))) is equivalent to
saying ``O4="No", or P4="" and I4=2 and TODAY()EDATE(M4,3)``. The
``P4="" and`` part is implied because if P4<"" and I4=2, the first IF-
true condition would have been returned ("Program Completed").

The point here is: we can economize on the specification of some
conditions by relying on the success or failure of other conditions.


I thank you so much

Glad it helped -- finally! "Third time's the charm" ;-).


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

On Feb 18, 3:07*am, Danny Boy
wrote:
=IF(AND(O4="Yes",OR(I4=1,P4<"")),"Program "&I4&" Completed",
*IF(OR(O4="No",AND(I4=2,TODAY()EDATE(M4,3))),"Pro gram "&I4&" Not
Completed", ""))

Thank you so much Joe. The change you made (see above) seemed to do the
trick. I just woke up a bit ago and re-tested. The only thing I need to
modify is the actual language of the flags themselves (back to what I
originally had):

Level 1 Program Completed
Level 1 Program Not Completed
Level 2 Program Completed
Level 2 Program Not Completed

Your wording for the flags is slightly different (e.g. Program 2 Completed,
Program 2 Not Completed, Program 1 Completed, Program 1 Not Completed).

Other than that everything seems to work PERFECTLY. I thank you so much for
all your time and effort and kindness. I'm still fairly an Excel "newbie"..

Dan"joeu2004" wrote:
On Feb 17, 6:56 pm, Danny Boy
wrote:
I apologize for any lack of clarity on my part.

I think the mistakes were my misreadings of the requirements. *Looking
back, everything you say below was stated in the previous posting.

I must say that at this point, I would hope that I've shown enough of
the "tools" that you would be able to make corrective modifications.
But I'll give it another go.

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

I believe that implements the rules exactly at you specified, namely
for Level 2, the status is "completed" only if both O4="Yes" and P4
has a date. *If O4="Yes", P4 is blank and today M4 plus 3 months,
the status is "not completed".

If the formula does not work, feel free to let me know what condition
I overlooked.

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

On Feb 17, 6:56 pm, Danny Boy
wrote:
Hi Joe:

Your assumption is correct (if the final interview beyond the 3-
month limit, you would simply leave P4 blank, and Q4 would continue to
read "not complete").

We give clients three months from the date of their final class (identified
in Column M) to complete their final interview (Column P). If a client does
not return for their final Interview, than Column Q would flag "Level 2
Program Not Completed". Until the three month date triggers however, Column Q
would remain blank (as non completion would only be triggered if they did not
return for their final interview within three months).

I did test your formula on a Level 2 client who is currently in a class
scheduled to end this weekend (February 21st). Column Q triggered the "Level
2 Program Not Completed", and at this juncture that flag should not appear
(given that the client hasn't yet finished class, and has up to three months
to have his final interview).

I also tested the formula on a Level 1 client (who is also slated to
complete class this weekend), and the "Level 1 Program Not Completed" flag
triggered for him as well.

Again, if the Completion Status of the client (Column O) is blank, than the
flag in Column Q would not appear.

I apologize for any lack of clarity on my part.

Dan

"joeu2004" wrote:
Start with the following:

=IF(O4<"Yes", "Program "&I4&" Not Completed",
*IF(I4=1, "Program 1 Completed",
*IF(AND(P4<"",TODAY()=P4), "Program 2 Completed",
* * "Program 2 Not Completed")))

And that could be rewritten as:

=IF(O4<"Yes", "Program "&I4&" Not Completed",
*IF(OR(I4=1, AND(P4<"",TODAY()=P4)),
* * "Program "&I4&" Completed",
* * "Program 2 Not Completed"))

But that might be harder to understand and/or maintain (change in the
future).

Note that I made some simplifying assumptions, which may or may not
meet your needs.

The key assumption is that whether or not P4 is within 3 months of the
program end date (M4) is a policy issue that does not need to encumber
the Q4 logic. *That is, I assume that if the interview beyond the 3-
month limit, you would simply leave P4 blank, and Q4 would continue to
read "not complete".

If that is not sufficient, perhaps the following logic would meet your
needs:

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

Or similar to the previous "optimization":

=IF(O4<"Yes", "Program "&I4&" Not Completed",
*IF(OR(I4=1, AND(P4<"",P4<=EDATE(M4,3),TODAY()=P4)),
* * "Program "&I4&" Completed",
* * "Program 2 Not Completed"))

(And you could exclude the condition TODAY()=P4 if P4 is filled in
only after the interview is completed.)

If neither formula does not meet your need, let me know what
combination of conditions fails.

HTH.

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

On Feb 17, 5:45 pm, Danny Boy
wrote:
Hi Joe:

Your right, after further testing I did find a few instances where the flags
in Column Q (where the original formula was posted) did not work at 100%.
Perhaps I should start from scratch in explaining what I'm looking for and
identify what the various columns are designed to do:

Column I= Program Assignment (Level 1 or 2)
Column M=Date that class is scheduled to end
Column O= Program Completion Status (Yes versus No)
Column P=Final Program Interview Date (only applicable for Level 2 clients)

The various flags which should trigger in Column Q are based upon the above
conditions. These flags include:

Level 1 Program Completed
Level 1 Program Not Completed
Level 2 Program Completed
Level 2 Program Not Completed

For a Level 1 client to be deemed as "completed", Column I must say "1", and
Column O must say "Yes".

If Column O says "No", than the Level 1 Non Completion Flag should trigger.

If Column O does not identify the class status (Yes/No), than Column Q
should stay blank. In other words, if a client is currently in a class, and
the class yet ended, than no completion status in Column Q would be yet
identified. Once the class is over, I toggle Column O to identify Yes or No..

For a Level 2 client to be deemed as "complete", Column I must say "2",
Column O must say "Yes", and Column P must include a final program interview
date.

If a Level 2 client has not had their final program interview within three
months of their last class date (identified in Column M), or if Column O says
"No", than the flag "Level 2 Program Not Completed" would trigger..

As is true for Level 1 client (if Column O does not identify the class
status-Yes/No), than Column Q should stay blank. In other words, if a client
is currently in a class, and the class yet ended, than no completion status
in Column Q would be yet identified. Once the class is over, I toggle Column
O to identify Yes or No.

I hope this narrative better explains what I am trying to accomplish with my
formula.

Thanks so much for your time!

Dan

"joeu2004" wrote:
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!