# Help with complicated if function.

#### rthakur

##### Board Regular
RT New Log 111805.xls
FGHIJKLMNO
2Review1st Letter21 daysDate 2nd42 daysOpenDate of lastTo U/WDays in
4
59/1/059/5/059/26/059/28/0510/19/05 Consult With Manager - Over 42 days!
61/3/057/5/057/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

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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"))))))

Not working.

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

Thanks!

=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",""))))))

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!

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.

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!

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!

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

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.

Replies
7
Views
263
Replies
4
Views
496
Replies
1
Views
275
Replies
0
Views
194
Replies
18
Views
513

1,211,456
Messages
6,101,954
Members
447,765
Latest member
bhutta5437

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back