Help with complicated if function.

rthakur

Board Regular
Joined
Feb 22, 2005
Messages
121
RT New Log 111805.xls
FGHIJKLMNO
2Review1st Letter21 daysDate 2nd42 daysOpenDate of lastTo U/WDays in
3Start DateSent2nd LetterLetter SentDeadlineTickleActivityU/WAction Status
4
59/1/059/5/05 9/28/05
61/3/057/5/05 6/1/05
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

Board Regular
Joined
Mar 28, 2004
Messages
227
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

Board Regular
Joined
Feb 22, 2005
Messages
121
Not working.

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

Thanks!
 

mikebecker

Board Regular
Joined
Mar 28, 2004
Messages
227
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

Board Regular
Joined
Feb 22, 2005
Messages
121
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

Board Regular
Joined
Mar 28, 2004
Messages
227
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

Board Regular
Joined
Feb 22, 2005
Messages
121
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

Board Regular
Joined
Feb 22, 2005
Messages
121
=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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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.
 

Forum statistics

Threads
1,077,829
Messages
5,336,647
Members
399,094
Latest member
Learner2019

Some videos you may like

This Week's Hot Topics

Top