Look at one column if date is = Pending Approval, if > Announced, if <> Selected

[CLCoop]

I have a formula but need to extend it to have three parameters.
If column L has no starting date meaning 01/0/1900 column M is blank = Pending Approval
If column L has a starting date older or = to current date (Column T1 has a formula =Today()) and column M is blank=Announced
If column L has a starting date older or = to current date (Column T1 has a formula = Today() and column M is NOT blank = Selected

Right now I know how to build off of two parameters:
=IF(L39="01/0/1900","Pending Approval", IF(L39>+$T$1,"Selected","Announced"))

What would be the formula for three parameters and if column M is blank or not blank?
This needs to be kept to ONE column as many other calculations are based on the words Pending Approval, Announced, and Selected.

 

[RasGhul]

Maybe try

Book1
	L	M							T	U
10	0/01/1900	Yes								Pending Approval
11	0/01/1900	Yes								Pending Approval
12	0/01/1900									Pending Approval
13	0/01/1900									Pending Approval
14	1/01/2022	Yes								Selected
15	3/03/2022	yes								Selected
16	3/03/2022									Announced
17	4/01/2022									Announced
18	10/01/2022									Announced
Sheet1

Cell Formulas
Range	Formula
U10:U18	U10	=IF(L10="0/01/1900","Pending Approval",IF(AND(L10<=TODAY(),M10=""),"Announced","Selected"))

 

[jtakw]

Hi,

Your written requirements differ from your posted picture, this is the best I can interpret between your written requirements and your picture.
Also, unless the Dates in Column L are Text, your value of "1/0/1900" is actually just 0 (zero) formatted as date.
But if Column L dates are Text, than none of your date comparisons would work.
I'm not referencing your T1 date of today, as it looks like you may have merged cells, it looks more like U1 in the pic.

This formula assumes Column L dates are Real dates, and considers the information/description in your Column U regarding Announced/Not Announced.
If it doesn't work for you, you need to further explain in detail of All your requirements, and clarify what I pointed out above.

Book3.xlsx
	L	M	N	O	P	Q	R	S	T	U
10	1/0/1900	YES								Selected
11	1/0/1900									Pending Approval
12	1/0/1900									Pending Approval
13	2/25/2022									Not Announced
14	2/25/2022	YES								Selected
15	3/31/2022									Announced
Sheet1029

Cell Formulas
Range	Formula
U10:U15	U10	=IF(M10="",IF(L10=0,"Pending Approval",IF(L10>TODAY(),"Announced","Not Announced")),"Selected")

 

[CLCoop]

Thank you jtakw, this worked! I added a column to show person was been selected so your formula I modified by a column.

You are correct those are dates so the 01/0/1900 is a blank on another spreadsheet when the formula brings it over it shows as a date.

I ended up using this: =IF(M10="",IF(L10=0,"Pending Approval",IF(L10>U1,"Not Announced","Announced")),"Selected")

Quick question the "Not Announced" doesn't really work meaning it should be based on FUTURE dates, of the Open Date in column K is before current date should show as not announced.... But this still gets what I need to help protect cost!! In your screen shot above row 13 shows not announced but the date given has past so it should be Announced. But as Open date is in column K might be difficult to include that in the formula.

 

[jtakw]

I'm not following 100%, so may be we just "swap" the result ?
Like below ?

My original formula followed the logic in your Column U where it said "Status based on closing date, if pass current date = Announced, if not pass current date = Not Announced"

If this is not what you want, further detailed explanation is needed, along with sample data and desired results, preferrably post using XL2BB (see my signature), or at least in Table format, so helpers can copy for testing.

Book3.xlsx
	L	M	N	O	P	Q	R	S	T	U
10	1/0/1900	YES								Selected
11	1/0/1900									Pending Approval
12	1/0/1900									Pending Approval
13	2/25/2022									Announced
14	2/25/2022	YES								Selected
15	3/31/2022									Not Announced
Sheet1029

Cell Formulas
Range	Formula
U10:U15	U10	=IF(M10="",IF(L10=0,"Pending Approval",IF(L10>TODAY(),"Not Announced","Announced")),"Selected")