Using "IF" and "AND"

[paulwatson5]

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!

 

[mandukes]

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

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

 

[mandukes]

Part of the code was not pasted so here again :

=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 !!

 

[FormR]

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
	O	P	Q
2	07/12/2017	07/12/2017	Review Complete
3		07/12/2017	Review Complete
4	07/12/2017		On Hold
5			In Progress
Sheet1

Cell Formulas
Range	Formula
Q2	=IF(ISNUMBER(P2),"Review Complete",IF(ISNUMBER(O2),"On Hold","In Progress"))

 

[paulwatson5]

Part of the code was not pasted so here again :

=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!!!!!

 

[paulwatson5]

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

Excel 2013/2016

	O	P	Q
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?

 

[FormR]

Those which are not started would have no details in column O.

Hi - Doesn't this conflict with..

If O is blank and P has a date, return "Review Complete"

Perhaps a little more clarification is needed?

 

[paulwatson5]

Hi - Doesn't this conflict with..



Perhaps a little more clarification is needed?

Sorry, if Column L is blank and also O & P are blank then show "Not Started"

Thank you (again)

 

[FormR]

Hi, you can try:

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

 

[paulwatson5]

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"