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/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
 
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
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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!
 
Upvote 0
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")),"")))
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top