[Danny boy]

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!

[Ashish Mathur[_2_]]

Hi,

You will have to install the Analysis ToolPack before you use the EDATE()
function. Please install this toolpack from Tools Addins.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Danny Boy" wrote in message
...
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!

[joeu2004]

On Feb 17, 3:06 pm, Danny Boy
wrote:
I'm having trouble getting the formula below to work
after adding the additional condition
[....]
IF(TODAY()EDATE(M4,3),AND(I4=2,O4="Yes"),[...]

There is a typo in what you posted. I don't know if the typo is just
in your posting, or if you cut-and-pasted from your worksheet (a good
practice) and it is in your worksheet.

The above should be written:

IF(AND(TODAY()EDATE(M4,3),I4=2,O4="Yes"),[...]

There could be more things wrong with your formula, and there might be
ways to simplify it.

But the above fix might get you going.


----- 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!

[Danny boy]

I do have the analysis tool pack installed. I suspect that something in my
formula is out of order, or something (e.g. parenthesis, and/or statement,
comma, etc).

Dan

"Ashish Mathur" wrote:

Hi,

You will have to install the Analysis ToolPack before you use the EDATE()
function. Please install this toolpack from Tools Addins.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Danny Boy" wrote in message
...
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!

[Danny boy]

I made the suggested fix Joe, however the formula only seems to work if the
"Completion Status" in Column O is toggled to "Yes". If it is toggled to
know, the flag in Colulmn Q (where the formula lives) says: "FALSE"...

=IF(AND(I4=1,O4="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(AND(TODAY()EDATE(M4,3),I4=2,O4="Yes "),IF(AND(I4=2,O4="No"),"Level 2 Program Not Completed","")))))

"joeu2004" wrote:

On Feb 17, 3:06 pm, Danny Boy
wrote:
I'm having trouble getting the formula below to work
after adding the additional condition
[....]
IF(TODAY()EDATE(M4,3),AND(I4=2,O4="Yes"),[...]

There is a typo in what you posted. I don't know if the typo is just
in your posting, or if you cut-and-pasted from your worksheet (a good
practice) and it is in your worksheet.

The above should be written:

IF(AND(TODAY()EDATE(M4,3),I4=2,O4="Yes"),[...]

There could be more things wrong with your formula, and there might be
ways to simplify it.

But the above fix might get you going.


----- 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!

[joeu2004]

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!

[Sheeloo[_3_]]

Danny,

Try
=IF(AND(I4=1,O4="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(AND(TODAY()EDATE(M4,3),I4=2,O4="Yes "),IF(AND(I4=2,O4="No"),"Level 2 Program Not Completed",""),"FALSE PATH I4 2 O4 No P4 Not Blank"))))

There was no ELSE path for AND(I4=2,O4="Yes",P4<"" that is why you were
getting FALSE. I have added "FALSE PATH I4 2 O4 No P4 Not Blank".

I have not tested beyond this. If you get any more FALSE then try adding
,"TEST"
starting from the right bracket and see...
"Danny Boy" wrote:

I made the suggested fix Joe, however the formula only seems to work if the
"Completion Status" in Column O is toggled to "Yes". If it is toggled to
know, the flag in Colulmn Q (where the formula lives) says: "FALSE"...

=IF(AND(I4=1,O4="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(AND(TODAY()EDATE(M4,3),I4=2,O4="Yes "),IF(AND(I4=2,O4="No"),"Level 2 Program Not Completed","")))))

"joeu2004" wrote:

On Feb 17, 3:06 pm, Danny Boy
wrote:
I'm having trouble getting the formula below to work
after adding the additional condition
[....]
IF(TODAY()EDATE(M4,3),AND(I4=2,O4="Yes"),[...]

There is a typo in what you posted. I don't know if the typo is just
in your posting, or if you cut-and-pasted from your worksheet (a good
practice) and it is in your worksheet.

The above should be written:

IF(AND(TODAY()EDATE(M4,3),I4=2,O4="Yes"),[...]

There could be more things wrong with your formula, and there might be
ways to simplify it.

But the above fix might get you going.


----- 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!

[joeu2004]

On Feb 17, 3:57 pm, Danny Boy
wrote:
If it is toggled to know, the flag in Colulmn Q
(where the formula lives) says: "FALSE"...

Sorry, I missed the other error in your formulation -- or perhaps just
an incompleteness in your logic. At the end, you have:

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

Note that you do not have a "value-if-false" argument for the
condition IF(AND(TODAY()...). I don't know your logic. Perhaps one
of the following will do the trick for you:

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

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


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

On Feb 17, 3:57*pm, Danny Boy
wrote:
I made the suggested fix Joe, however the formula only seems to work if the
"Completion Status" in Column O is toggled to "Yes". If it is toggled to
know, the flag in Colulmn Q (where the formula lives) says: "FALSE"...

=IF(AND(I4=1,O4="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(AND(TODAY()EDATE(M4,3),I4=2,O4="Yes "),IF(AND(I4=2,O4="No"),"*Level 2 Program Not Completed","")))))



"joeu2004" wrote:
On Feb 17, 3:06 pm, Danny Boy
wrote:
I'm having trouble getting the formula below to work
after adding the additional condition
[....]
IF(TODAY()EDATE(M4,3),AND(I4=2,O4="Yes"),[...]

There is a typo in what you posted. *I don't know if the typo is just
in your posting, or if you cut-and-pasted from your worksheet (a good
practice) and it is in your worksheet.

The above should be written:

IF(AND(TODAY()EDATE(M4,3),I4=2,O4="Yes"),[...]

There could be more things wrong with your formula, and there might be
ways to simplify it.

But the above fix might get you going.

----- 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!

[Danny boy]

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!

[joeu2004]

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!

[Danny boy]

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!

[joeu2004]

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.

[Danny boy]

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!

[joeu2004]

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!

[Danny boy]

=IF(AND(O4="Yes",OR(I4=1,P4<"")),"Program "&I4&" Completed",
IF(OR(O4="No",AND(I4=2,TODAY()EDATE(M4,3))),"Prog ram "&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!

[joeu2004]

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!

[Danny boy]

Your great Joe, and the additional explanations regarding economizing made
perfect sense.

I did add one additional piece of logic to correct a small issue that I
found in testing (the change below seems to work):

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

The change is the addition above of (R4="") PRIOR TO the EDATE piece of the
formula. Upon further testing, this alteration seems to be working. My
rationale for adding this has to do with Level 2 clients who never bother to
enroll at all (we often get referrals, and clients don't follow through). In
these cases, the Column Q Flag should NEVER trigger (for any Level 1 or 2
client who does not enroll at all).

Column R has a flag which advises me (as applicable): "Client Failed to
Enroll on Time", and the logic I added, was to tell the formula you provided
(in Column Q), to avoid a program completion status flag, if anything appears
in Column R (e.g. "Client Failed to Enroll on Time" flag).

The Column R "Client Failed to Enroll on Time" flag is triggered when no
enrollment date is posted in the enrollment column (Column K), and it has
been more than 10 days since the referral was sent to us (Column H).

As I said, my addition to your Column Q Formula (to account for the Column R
flag) seems to take care of the problem.

Again, you are wonderful at helping and explaining. If your not, you should
be a teacher!

Best,

Dan

"joeu2004" wrote:

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))),"Prog ram "&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"),