how can I add an AND function into an IF nest within Excel to check multiple cells

We try to track how our product is manufactured and if it was completed on time, we have Column J record where the product is ON TIME, LATE or EARLY using the following formula:

=IF(K8>0,"LATE",IF(K8<0,"EARLY","ON TIME"))

Which is checking the completed quantity column has a number and then the date that it was completed and recording the difference between the due date and completed date in column K. But this doesn't record the products that have not been completed at all. We would like to show those as LATE also. I tried to add an AND function nested within like this:

=IF(K8>0,"LATE",IF(K8<0,"EARLY","ON TIME"))+IF(AND(K8="", L8>=1), "LATE", "")

but tbh my knowledge in excel is not the greatest and I've obviously not got this right at all.

Can I add an AND function to this formula or is another method better suited to this task?

The non-coloured cells show that product has not been completed yet it is not recorded as LATE in column J even though it is past it's due date

Solution 1:

You're on the right track, but instead of trying to add two IF statements, you could add the blank in column K as either another IF statement like this:

=IF(AND(K8="",L8>=1),"LATE",IF(K8>0,"LATE",IF(K8<0,"EARLY","ON TIME")))

or as an OR statement like this:

=IF(OR(AND(K8="",L8>=1),K8>0),"LATE",IF(K8<0,"EARLY","ON TIME"))

Using an ISBLANK might not work. If you have a formula in column K that returns "" if it's zero, then it's not actually blank.