Using "IF" and "AND"

paulwatson5

New Member
Joined
Apr 22, 2016
Messages
20
Hi guys, to stop me banging my head against the wall any more, could you please help me out on a formula?

I have 2 columns (O & P) that have either blanks or dates.
In Column "S" I want a formula in there that will bring back one of 3 possible outcomes.

If O is blank and P has a date, return "Review Complete"
If O has a date and P has a date, return "Review Complete" (same outcome as above)
If O has a date and P is blank, return "On Hold"
If O is blank and P is also blank, return "In Progress"

Could anyone help me out here please??

Thanks!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hi guys, to stop me banging my head against the wall any more, could you please help me out on a formula?

I have 2 columns (O & P) that have either blanks or dates.
In Column "S" I want a formula in there that will bring back one of 3 possible outcomes.

If O is blank and P has a date, return "Review Complete"
If O has a date and P has a date, return "Review Complete" (same outcome as above)
If O has a date and P is blank, return "On Hold"
If O is blank and P is also blank, return "In Progress"

Could anyone help me out here please??

Thanks!



Here is the code

Code:
=IF(AND(O7="",P7<>""),"Review Complete",IF(AND(O7<>"",P7<>""),"Review Complete",IF(AND(O7<>"",P7=""),"ON HOLD",IF(AND(O7="",P7=""),"In

015ac5f53aacf1e0bb16ad2b8a3a2d50.gif
 
Upvote 0
Part of the code was not pasted so here again :

Code:
=IF(AND(O7="",P7<>""),"Review Complete",IF(AND(O7<>"",P7<>""),"Review Complete",IF(AND(O7<>"",P7=""),"ON HOLD",IF(AND(O7="",P7=""),"In Progress",""))))

Cheeers !! ;)
 
Upvote 0
If O is blank and P has a date, return "Review Complete"
If O has a date and P has a date, return "Review Complete" (same outcome as above)
If O has a date and P is blank, return "On Hold"
If O is blank and P is also blank, return "In Progress"

Hi, here is another option that I think covers the bases:


Excel 2013/2016
OPQ
207/12/201707/12/2017Review Complete
307/12/2017Review Complete
407/12/2017On Hold
5In Progress
Sheet1
Cell Formulas
RangeFormula
Q2=IF(ISNUMBER(P2),"Review Complete",IF(ISNUMBER(O2),"On Hold","In Progress"))
 
Upvote 0
Part of the code was not pasted so here again :

Code:
=IF(AND(O7="",P7<>""),"Review Complete",IF(AND(O7<>"",P7<>""),"Review Complete",IF(AND(O7<>"",P7=""),"ON HOLD",IF(AND(O7="",P7=""),"In Progress",""))))

Cheeers !! ;)

Thank you, thank you, thank you!!!!!
 
Upvote 0
Hi, here is another option that I think covers the bases:

Excel 2013/2016
OPQ
Review Complete
On Hold
In Progress

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]07/12/2017[/TD]
[TD="align: right"]07/12/2017[/TD]
[TD="bgcolor: #FFFF00"]Review Complete[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]07/12/2017[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]07/12/2017[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q2[/TH]
[TD="align: left"]=IF(ISNUMBER(P2),"Review Complete",IF(ISNUMBER(O2),"On Hold","In Progress"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Sorry, need to add something in to the formula and I cant figure it out.

Of the "In Progress" pot, I need to know which of these are "Not started". Those which are not started would have no details in column O.
Could you help me with a formula to add in please?
 
Upvote 0
Hi, you can try:

=IF(ISNUMBER(P2),"Review Complete",IF(ISNUMBER(O2),"On Hold",IF(L2="","Not Started","In Progress")))
 
Upvote 0
Hi, you can try:

=IF(ISNUMBER(P2),"Review Complete",IF(ISNUMBER(O2),"On Hold",IF(L2="","Not Started","In Progress")))

That one didn't work, it just populated every cell as "Review Complete"

The whole criteria is at yesterdays message plus the extra bit which in total is:

If O is blank and P has a date, return "Review Complete"
If O has a date and P has a date, return "Review Complete" (same outcome as above)
If O has a date and P is blank, return "On Hold"
If O is blank and P is also blank, return "In Progress" (might have to put in that L is non-blank?)
If O is blank and P is blank and L is also blank "Not Started"
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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