Change text based on another cell's condition

[nate777]

Hi there,

I have three column headings


Due Date Closed Date Status
28-Sep-06 21-Sep-06 Closed
23-Aug-06 21-Aug-06 Closed
10-Sep-06 ----------- Overdue
24-Sep-06 ----------- Open


Note: Status column has data validation list "Open", "Closed" and "Overdue"


I want to do the following.

If Due Date is a day in the future and corresponding Closed Date field is empty or blank....I want Status to show as "Open"

If Due Date is past due(<Today()?), and Closed Date field is blank, Status to show as "Overdue"

If Closed Date field has a date entry, then Status to show as "Closed"

Any suggestions ?

Thanks,
Nate

 

[Oaktree]

C2=IF(B2<>"","Closed",IF(A2<TODAY(),"Overdue","Open"))

Copy C2 down.

Book9
	A	B	C	D
1	Due Date	Closed Date	Status
2	28-Sep-06	21-Sep-06	Closed
3	23-Aug-06	21-Aug-06	Closed
4	10-Sep-06		Overdue
5	24-Sep-06		Open
Sheet1

 

[nate777]

Cool Oaktree! It works!