# Help with complicated if function.

#### rthakur

##### Board Regular
I apologize for creating a little confusion.
Please allow me to rephrase and provide more clarity, here we go:
Current Formula: =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"),"Review Initiated")),"Case Logged"),"")))

A case is received to perform medical investigation. As soon as the case is received, it is logged under “Review Start date” ie Column F.
If F5 is filled with date = "Case Logged" but if F5 is empty = *No message should Display*
Above requirement is working fine.

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 today() is under 21 days from G5 = "No action Required"
Above requirement is working fine.

If I5 is filled with date and today() >= 21 days from I5 = "Consult with Manager" however if today() is under 21 days from I5 = "No Action Required)
Above underlined scenario is not working, the message remains “Second Letter” even when today() is under I5.

Additonal info: There are 2 letter notifications, Initial letter and Second letter. Everytime a letter is sent out, a reviewer have 21 days from the date that letter was sent out to act on that case again.

If after sending out first letter (G5), a reviewer does not get a response in 21 days, he/she sends out a second letter (I5) but if after 21 days from the second letter reviewer still does not get any response, he/she must consult with their manager to take administrative action. I am trying to create a message(s) for each scenario.

I hope I have provided more details on my issue. Please let me know if I missed anything or more info is needed.

Best Regards,

rthakur

Bump. Thank you!

Regards

#### Yogi Anand

##### MrExcel MVP
Hi rthakur:

In your setup, there are a number of dates, with a number of possibilities --you may have to sort them all out ... what happens under what set of conditions -- it may not be that easy to do without real data and realistic constraints. It will need a lot of tracking of what happens with what set of entries, and what constraints need to be built in.

So, I am sorry ... with what you have stated, I am not able to provide any improvement to the formulation I have already provided.

If you can describe fulfilling a specific condtion unambiguously, I will be glad to open this file up again.

Good Luck!

#### barry houdini

##### MrExcel MVP
I would think that you need to swap round the IF conditions so that those related to I5 appear before those related to G5, something like

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

#### rthakur

##### Board Regular
Mr. Anand, thank you for helping me out again. In future, I will try to explain all the conditions with more details in the first try...

Mr. Barry, your formula is just what the doctor ordered! Thank you, it is working!

Best Regards,

rthakur

1,081,849
Messages
5,361,673
Members
400,644
Latest member
ndroger1

### 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...