If-and-how??

[UniAmyColeman]

Hi All

I have spent fruitless hours on something that I am pretty sure should be straightforward, please help my sanity

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]AJ[/TD]
[TD="align: center"]AK[/TD]
[TD="align: center"]AN[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Date expected[/TD]
[TD="align: center"]Overdue?[/TD]
[TD="align: center"]Submitted[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]05/05/2018[/TD]
[TD][/TD]
[TD]07/05/2018[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]16/07/2018[/TD]
[TD][/TD]
[TD]#/N/A[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]05/09/2017[/TD]
[TD][/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]


So, I want the word "Overdue" to appear in AK IF AJ is older than today AND AN is blank - does not contain a date (AN is pulling a VLOOKUP producing a date from another sheet when the submission is received). So in the above example "Overdue" would appear only in AK5

I have the dynamic date in cell B3 of the spreadsheet if that's an important factor?

Any advice appreciated

Amy

 

[AlanY]

try this in AK3 and copy down

=IF(AND(AJ3 < TODAY(),NOT(ISNUMBER(AN3))),"Overdue","")

 

[UniAmyColeman]

Thank you AlanY, it was the NOT(ISNUMBER) bit that I was overlooking!

 

[AlanY]

you're welcome