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,081,753
Messages
5,361,093
Members
400,613
Latest member
Markdc123

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top