Action Plan Overdue Status - Two different cell?

[philsew]

Hi,
I've tried to figure this out but am struggling.. I need to figure out how to make a cell say OVERDUE based on the information in two other cells.
If target completion date is older than today and the action progress says anything other than completed, including if it is blank I want to be able to say the Overdue cell says Yes. If the date is in the future I don't want it to say anything and if the date is today or in the past but the action progress cell says completed I also don't want it to say anything.


Any help would be massively appreciated!

Target Completion Date:	Overdue?	Action Progress:
08/09/2023		Completed

Thanks,

 

[Joe4]

Welcome to the Board!

Assuming that Target Completion date is in cell A2 and Action Prorgess is in cell C2, enter this formula in cell B2:

=IF(AND(A2<TODAY(),C2<>"Completed"),"Overdue","")

 

[philsew]

Wow, that was fast! Brilliant, thank you!

 

[Joe4]

You are welcome!
Glad I was able to help.

Hopefully, you can see what I did and it makes sense.
Feel free to ask any questions you may have about it.

 

[philsew]

Hi Joe,

I do have one question.. when the cell for the date is blank it still says overdue? Does it need an ISBLANK somewhere?

Thanks,

Phil

 

[Joe4]

Hi Joe,

I do have one question.. when the cell for the date is blank it still says overdue? Does it need an ISBLANK somewhere?

Thanks,

Phil

OK, you originally did not mention any possibility of the date field being blank. But if you do not want it to say "Overdue" if the date field is blank, just add one more condition to the AND clause, i.e.

=IF(AND(A2<TODAY(),A2>0,C2<>"Completed"),"Overdue","")

This works because dates in Excel are actually stored as numbers, specifically the number of days since 1/0/1900 (dates are really just numbers with special date formats).
So you can simply check to see if the value is greater than 0 (not blank/empty).

 

[philsew]

Perfect! Sorry I wasn't more specific.

 

[Joe4]

No worries.

In Excel 365, you can have up to 255 clauses in the AND function. So you can simply add more conditions to it to suit your needs.