Formula to appear specific values/text

K

Kookai

New Member
Joined
May 29, 2011
Messages
32
Hi Guys, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
am not really good in excel and would like to ask help.... might be easy for u guys....<o:p></o:p>
<o:p></o:p>
I have 2 work sheets... (request & Report). <o:p></o:p>
<o:p></o:p>
In Request Sheet, I've got column <o:p></o:p>
<o:p></o:p>
B1 - Reference <o:p></o:p>
C1 - Date Received <o:p></o:p>
I1 - Status (Pending, awaitng Reply,cancelled,Complete,Under study or blank) <o:p></o:p>
J1 - Reply Date <o:p></o:p>
N1-NOC status (Cancelled,Released,Blank) <o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Hope you can help me guys ;-) Thank You
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Kookai said:
Hi Guys, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
am not really good in excel and would like to ask help.... might be easy for u guys....<o:p></o:p>
<o:p></o:p>
I have 2 work sheets... (request & Report). <o:p></o:p>
<o:p></o:p>
In Request Sheet, I've got column <o:p></o:p>
<o:p></o:p>
B1 - Reference <o:p></o:p>
C1 - Date Received <o:p></o:p>
I1 - Status (Pending, awaitng Reply,cancelled,Complete,Under study or blank) <o:p></o:p>
J1 - Reply Date <o:p></o:p>
N1-NOC status (Cancelled,Released,Blank) <o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Hope you can help me guys ;-) Thank You
Click to expand...
I1:
Code: 
=IF(N1="RELEASED","Completed",
    IF(N1="Cancelled","Cancelled",
      IF(AND(N1="",C1<>""),"Awaiting Response",
        IF(C1="","",IF(TODAY()-C1>5,"Pending","Under Study")))))

Hope I didn't miss anything...
 
Upvote 0
Hi,

thank you for your response and i Tried the formula but it says i have entered to many arguments for this function.

Please bear me on this am a newbie...
 
Upvote 0
Kookai said:
Hi,

Excel 2007... thank you
Click to expand...

I think the formula should run...

=IF(N1="RELEASED","Completed",IF(N1="Cancelled","Cancelled",IF(AND(N1="",C1<>""),"Awaiting Response",IF(C1="","",IF(TODAY()-C1>5,"Pending","Under Study")))))

You need to check whether you specified your conditions exhaustively though.
 
Upvote 0
Hi AA,

thank you, it works but have some issues (forgot to tell that if J1 - has a value and N1 is blank, I1= awaiting response,

see below table and the remarks, on the last column.... there are Pending but should be Completed, and there are Awaiting Response that should be Under Study...

Would really appreciate your Help.... thank you very much.
<TABLE style="WIDTH: 584pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=777 border=0><COLGROUP><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 5741" width=157><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6509" width=178><TBODY><TR style="HEIGHT: 39pt; mso-height-source: userset" height=52><TD class=xl69 id=td_post_2752144 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 97pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 39pt; BACKGROUND-COLOR: #ccffcc" width=129 height=52>B</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc" width=85>C</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 118pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc" width=157>I</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 83pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc" width=111>J</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc" width=117>N</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 134pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc" width=178>REMARKS / SHOULD BE </TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 33.75pt; BACKGROUND-COLOR: #92d050" width=129 height=45>TSD-NOC-11-266</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0 0.5pt; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=85>25-May-11</TD>

<TD class=xl96 id=td_post_2752144 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #92d050" align=left>Awaiting Response

</TD>

<TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=111> </TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0 0.5pt; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=117> </TD>

<TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=left>Pending

</TD></TR><TR style="HEIGHT: 45pt" height=60><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 45pt; BACKGROUND-COLOR: #92d050" width=129 height=60>TSD-NOC-11-267</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=85>25-May-11</TD><TD class=xl96 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #92d050" align=left>Awaiting Response</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=111> </TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=117> </TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=left>Pending</TD></TR><TR style="HEIGHT: 56.25pt" height=75><TD class=xl105 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 56.25pt; BACKGROUND-COLOR: transparent" width=129 height=75>TSD-NOC-11-277</TD><TD class=xl106 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent" width=85>2-Jun-11</TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=left>Awaiting Response</TD><TD class=xl106 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent" width=111>2-Jun-11</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent" width=117> </TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD class=xl105 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 33.75pt; BACKGROUND-COLOR: transparent" width=129 height=45>TSD-NOC-11-278</TD><TD class=xl106 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent" width=85>2-Jun-11</TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=left>Completed</TD><TD class=xl106 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent" width=111>5-Jun-11</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent" width=117>RELEASED</TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD class=xl105 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 33.75pt; BACKGROUND-COLOR: transparent" width=129 height=45>TSD-NOC-11-279</TD><TD class=xl106 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent" width=85>5-Jun-11</TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=left>Completed</TD><TD class=xl106 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent" width=111>8-Jun-11</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent" width=117>RELEASED</TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 33.75pt; BACKGROUND-COLOR: #92d050" width=129 height=45>TSD-NOC-11-280</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=85>5-Jun-11</TD><TD class=xl96 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #92d050" align=left>Pending</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=111>7-Jun-11</TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=117>RELEASED </TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=left>Completed</TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl105 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=129 height=30>TSD-NOC-11-281</TD><TD class=xl106 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent" width=85>6-Jun-11</TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=left>Awaiting Response</TD><TD class=xl106 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent" width=111>7-Jun-11</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent" width=117> </TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 45pt" height=60><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 45pt; BACKGROUND-COLOR: #92d050" width=129 height=60>TSD-NOC-11-282</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=85>6-Jun-11</TD><TD class=xl96 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #92d050" align=left>Pending</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=111>8-Jun-11</TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=117>RELEASED </TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=left>Completed</TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 33.75pt; BACKGROUND-COLOR: #92d050" width=129 height=45>TSD-NOC-11-283</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=85>6-Jun-11</TD><TD class=xl96 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #92d050" align=left>Pending</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=111>8-Jun-11</TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=117>RELEASED </TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=left>Completed</TD></TR><TR style="HEIGHT: 45pt" height=60><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 45pt; BACKGROUND-COLOR: #92d050" width=129 height=60>TSD-NOC-11-284</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=85>6-Jun-11</TD><TD class=xl96 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #92d050" align=left>Awaiting Response</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=111> </TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=117> </TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=left>Pending</TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 33.75pt; BACKGROUND-COLOR: #92d050" width=129 height=45>TSD-NOC-11-285</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=85>6-Jun-11</TD><TD class=xl96 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #92d050" align=left>Pending</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=111>9-Jun-11</TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=117>RELEASED </TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=left>Completed</TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 33.75pt; BACKGROUND-COLOR: #92d050" width=129 height=45>TSD-NOC-11-286</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=85>7-Jun-11</TD><TD class=xl96 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #92d050" align=left>Awaiting Response</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=111> </TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=117> </TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=left>Pending</TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD class=xl105 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 33.75pt; BACKGROUND-COLOR: transparent" width=129 height=45>TSD-NOC-11-288</TD><TD class=xl106 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent" width=85>8-Jun-11</TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=left>Awaiting Response</TD><TD class=xl106 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent" width=111>12-Jun-11</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent" width=117> </TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 45pt" height=60><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 45pt; BACKGROUND-COLOR: #92d050" width=129 height=60>TSD-NOC-11-289</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=85>12-Jun-11</TD><TD class=xl96 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #92d050" align=left>Awaiting Response</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=111> </TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=117> </TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=left>Under Study </TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 33.75pt; BACKGROUND-COLOR: #92d050" width=129 height=45>TSD-NOC-11-290</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=85>12-Jun-11</TD><TD class=xl96 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #92d050" align=left>Awaiting Response</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=111> </TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=117> </TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=left>Under Study </TD></TR><TR style="HEIGHT: 45pt" height=60><TD class=xl91 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 45pt; BACKGROUND-COLOR: #92d050" width=129 height=60>TSD-NOC-11-291</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=85>12-Jun-11</TD><TD class=xl96 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #92d050" align=left>Awaiting Response</TD><TD class=xl92 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=111> </TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #92d050" width=117> </TD><TD class=xl81 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=left>Under Study </TD></TR></TBODY></TABLE>
 
Upvote 0
Would you systematically stipulate the conditions and the results they are associated with?

Pending when:
Completed when:
Awaiting Response when:
Under Study when:

Try to be complete after each "when".
 
Upvote 0
Sorry and thank you again for responding...

Pending when: today - C1 > 5
Completed when: N1 = Released
Awaiting Response when: N1 = Blank ; J1 = has a value (date)
Under Study when: today - C1 < 5
Cancelled when: N1 = Cancelled
 
Upvote 0
Kookai said:
Sorry and thank you again for responding...

Pending when: today - C1 > 5
Completed when: N1 = Released
Awaiting Response when: N1 = Blank ; J1 = has a value (date)
Under Study when: today - C1 < 5
Cancelled when: N1 = Cancelled
Click to expand...

Let's try again...

=IF(N1="RELEASED","Completed",IF(N1="Cancelled","Cancelled",IF(AND(N1="",J1<>""),"Awaiting Response",IF(C1="","",IF(TODAY()-C1>5,"Pending","Under Study")))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Count # of values that fall within a certain date range
Replies
1
Views
572
RobP
R
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
K
  • Question Question
Modifying Existing VBA Code to add 2 additional data columns
Replies
0
Views
329
keranali
K
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
sparky2205
  • Question Question
Work calendar to display several people
Replies
2
Views
759
sparky2205
sparky2205

Forum statistics

Threads
1,224,521
Messages
6,179,286
Members
452,902
Latest member
Knuddeluff

Share this page

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