Help with complicated if function.

[rthakur]

RT New Log 111805.xls
	F	G	H	I	J	K	L	M	N	O
2	Review	1st Letter	21 days	Date 2nd	42 days	Open	Date of last	To U/W	Days in
3	Start Date	Sent	2nd Letter	Letter Sent	Deadline	Tickle	Activity		U/W	Action Status
4
5	9/1/05	9/5/05	9/26/05	9/28/05	10/19/05					Consult With Manager - Over 42 days!
6	1/3/05	7/5/05	7/26/05				6/1/05			Send Second Letter
Current Cases

Under O" column I am having trouble getting the expected message.
This is what I am looking for in "O" column.

if F5 is empty = *No message Displayed*
If F5 is filled with date and G5 is empty = "Case Logged - Pending Review"
If F5 is filled with date and G5 is also filled with date = "Review Initiated"
If today() >=21 days from G5 = "Second Letter" however if within 21 days from G5 = "No action Required"
If I5 is filled with date and today() >= 21 days from I5 = "Consult with Manager" however if within 21 days from I5 = "No Action Required)

I would still like to keep the beginning of this formula to give preference to =IF(Q5="y","Case completed",IF(M5,"Referred to U / W").

Thank you for all your kind help.

rthakur

 

[mikebecker]

Try this: -not tested.

=IF(Q5="Y","Case completed",IF(F5="","",IF(AND(F5<>"",G5=""),"Case Logged - Pending Review",IF(AND(F5<>"",G5<>""),"Review Initiated",IF(TODAY()-G5>=21,"Second Letter",IF(AND(TODAY()-I5>=21,I5<>""),"Consult with Manager","No action Required"))))))

 

[rthakur]

Not working.

The only message is "Review Initiated". Other criteria do not work.

Thanks!

 

[mikebecker]

How about this.

=IF(Q5="Y","Case completed",IF(AND(F5<>"",G5=""),"Case Logged - Pending Review",IF(AND(G5<>"",TODAY()-G5>=21),"Second Letter",IF(OR(TODAY()-G5<21,AND(I5<>"",TODAY()-I5<21)),"No Action Required",IF(AND(TODAY()-I5>=21,I5<>""),"Consult with Manager",IF(AND(F5<>"",G5<>""),"Review Initiated",""))))))

 

[rthakur]

We are One step forward.

After the Second letter cell is filled with the date message remians as "Second letter". After the second letter cell is filled and today is <21 days from that second letter cell, message should be, "No Action required".

Thank you!

 

[mikebecker]

You need another condition that differentiates between:

If today()< 21 days from G5 = "No action Required"

and

If F5 is filled with date and G5 is also filled with date = "Review Initiated"

The logic sees them as the same.

 

[rthakur]

If I can get this in a formula, that will be helpful. My brain cells are dying as I try to adit this formula.

Thanks!

 

[Yogi Anand]

Hi rthakur:

I have not fully reconciled all of your requirements ... but let us see how far does the following formula take you ...

=IF(Q5="y","Case completed",IF(M5,"Referred to U / W",IF(ISNUMBER(F5),IF(ISNUMBER(G5),IF(TODAY()>=G5+21,"Second Letter",IF(ISNUMBER(I5),IF(TODAY()>=I5+21,"Consult with Manager","No action required"),"Case Logged - Pending Review")),"Case Logged"),"")))

Let me know how it goes!

 

[rthakur]

=IF(Q13="y","Case completed",IF(M13,"Referred to U / W",IF(ISNUMBER(F13),IF(ISNUMBER(G13),IF(TODAY()>=G13+21,"Second Letter",IF(ISNUMBER(I13),IF(TODAY()>=I13+21,"Consult with Manager","No action required"),"Review Initiated")),"Case Logged"),"")))

Minor change to above: "Case Logged pending Review" is changed to "Review Initiated"

Formula does not go any further from giving the message of "Second Letter".

Let us say, the message is Second letter cuz today is >= 21 days from G5. I send the second letter today and enter today's date in I5 cell. Now the message should turn to "No Action required" again.

And then let's say today is >=21 days from the I5 (day I sent the second letter), the message should be "Consult with Manager"

Thank you!

 

[Yogi Anand]

Hi rthakur:

You had done a fairly good job of explaining the rules, but some how when it came to entry in cell I5, it seemed to be disjointed with the rest of the rule structure. So, I think you may need to tighten up the specification (your rules) -- the rules need to be clear and unamibguous.