Formula to appear specific values/text

[Kookai]

Hi Guys, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
am not really good in excel and would like to ask help.... might be easy for u guys....<o></o>
<o></o>
I have 2 work sheets... (request & Report). <o></o>
<o></o>
In Request Sheet, I've got column <o></o>
<o></o>
B1 - Reference <o></o>
C1 - Date Received <o></o>
I1 - Status (Pending, awaitng Reply,cancelled,Complete,Under study or blank) <o></o>
J1 - Reply Date <o></o>
N1-NOC status (Cancelled,Released,Blank) <o></o>
<o></o>
Now what i want to happen is, in column I1, there is 5 days review cycle after receiving the request. So, if the reviewer, didnt give it after 5 days after received (today()-C1>5, I1 = Pending , then if its less than 5 days (today()<5, I1 = Under Study. However, if my N1 is RELEASED, I1 = Completed, and if N1 is Cancelled, I1 = Cancelled and if N1 is blank my I1 = Awaiting Response, then lastly if C1 is blank, I1 = blank. Is this possible? <o></o>
<o></o>
Hope you can help me guys Thank You

 

[Kookai]

Hi AA,

Nope it isnt a date .... C.1 Non Compliance with NOC Conditions ;
C.2 NOC Expired - NOC Under Renewal ; C.2 NOC Expired - NOC was Closed ; C.3 Work Completed those are the list on my drop down...

 

[Aladin Akyurek]

Hi AA,

Nope it isnt a date .... C.1 Non Compliance with NOC Conditions ;
C.2 NOC Expired - NOC Under Renewal ; C.2 NOC Expired - NOC was Closed ; C.3 Work Completed those are the list on my drop down...

In that case, try...

=IF(E1="","",IF(J1<>"","Close",IF(ISNUMBER(E1),IF(TODAY()-E1>=0,"Expired",IF(TODAY()-E1>=-8,"Expires in 7 days or less","Active")))))

instead.

 

[Kookai]

thank You Mr. Genius... it works fine now... thanks for the help really appreciate it!

 

[Aladin Akyurek]

thank You Mr. Genius... it works fine now... thanks for the help really appreciate it!

You are welcome. Thanks for providing feedback.