SOLVED

Help with Excel Function to return the correct status

Copper Contributor

Hi, 

 

I have an excel sheet where i need the "Status" column to either return a blank, "open", "overdue" or closed" status depending on the following scenarios:

 

If "Target" and "Completion" columns are blank then "Status" column should be blank too.

If "Target" has a date entered and "Completion" is blank then "Status" column should return "Open".

If "Target" and "Completion" columns both have dates then "Status" column should return "Closed".

However, if "Target" date has past today's date and "Completion" column is blank then "Status" column should return "Overdue".

reemav_1-1646918075044.png

 

 

i had used this formula but it's not showing the correct status:

=IF(J8=K8="","",IF(J8<>""=K8<>"","Closed",IF(J8<O1,"Overdue",IF(J8<>"","Open"))))

 

 

 

 

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@reemav 

=IF(AND(J8="",K8=""),"",IF(AND(J8<>"",K8<>""),"Closed",IF(AND(J8>O1,K8=""),"Overdue",IF(AND(J8<>"",K8=""),"Open"))))

Is this what you are looking for? 

Thank you so much! Yes that's exactly what i was looking for :)
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@reemav 

=IF(AND(J8="",K8=""),"",IF(AND(J8<>"",K8<>""),"Closed",IF(AND(J8>O1,K8=""),"Overdue",IF(AND(J8<>"",K8=""),"Open"))))

Is this what you are looking for? 

View solution in original post