Due, upcoming, overdue

I am working on an excel sheet and am relatively new to formulas, and this feels really complex. I have a cell that I need to do several things.

If j21 is later than today's date and k21 is blank and older than j21, O21 should be "overdue by XXX days" and red
If j21 is not later than today's date and k21 is not blank and not older than j21, O21 should be "submitted" and green
If k21 is later than today's date and j21 O21 should be "Submitted late" and yellow

If there is a way to show something is nearing a due date that would be great also...I am just not sure how to phrase that.

I hope someone can help me

Capture.PNG

Originally Posted by TMSmith

If j21 is later than today's date and k21 is blank and older than j21, O21 should be "overdue by XXX days" and red

K21 cannot be blank and older than J21 at the same time.

Should have said if j21 is later than today is and k21 is bland O21 should be overdue by XXX days (today's date - J21) Sorry

Instructions are a little confusing, but I think I have an idea of what you are trying to accomplish. Put this formula into O21 and copy down. As far as the coloring - that's conditional formatting. If this formula works how you want it to, we can look at the formatting afterwards.

Formula:

Please Login or Register  to view this content.

That worked! Thank you.. Here is where we are nowAttachment 812099

Attachment in that way does not work. You can attach a book following the instructions at the top. But if the formula worked, the conditional format is easy. So select the data from columns you want to highlight. Maybe you are selecting columns J:O. Click Conditional formatting -> Manage Rules. In that pop-up, click New Rule -> use a formula to determine. In the formula box enter

Formula:

Please Login or Register  to view this content.

Set the format by clicking the format button (fill, font, borders, etc)

For Overdue and Submitted Late, it will vary a bit. Using two more conditional formats:

Formula:

Please Login or Register  to view this content.

It is almost there..it seems that the formatting is not working all the way down and I cant figure it out Also is there a way to have "Due in XXX days" instead of "Not due yet" I hope I have attached the file correctly. I really appreciate all of you.123Perwinkle 123.xlsx

How's this work for you?

So some advice: in your workbook, you are using a table for your data which is great. You should look into the pros and cons of using a table and then how to apply formulas and conditional formats within a table. As you keep going and learning more, you will find that tables make life much easier

Thank you.