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 "Level2
Program Not Completed". Until the three month date triggers however, Column Q
would remain blank (as non completion would only be triggered if theydid 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 threemonths
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
:
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 the3-
month limit, you would simply leave P4 blank, and Q4 would continueto
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 <
[email protected]>
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 uponthe 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 identifyYes 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
:
On Feb 17, 4:19 pm, Danny Boy <
[email protected]>
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), Iam 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 beas
simply as changing "Not Completed" to "Completed".
But I'm concerned that there might yet-another condition that Idid
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 explanationof the
FALSE return value.
----- original posting -----
On Feb 17, 4:19 pm, Danny Boy <
[email protected]>
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
:
PS....
On Feb 17, 3:06 pm, Danny Boy <
[email protected]>
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 assumptionscan 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 <
[email protected]>
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!
