Share via

Conditional formatting to track overdue and upcoming invoices while ignoring paid invoices

Anonymous
2023-12-13T02:42:37+00:00

I have multiple columns in my spreadsheet.

The condition will be if the today()>P+R, X not equal/less than to J and do shown in X, then i would like to show those are overdue in RED, and those upcoming for payment(P+R) in GREEN, if only they have not been paid.

column

J - invoice amount

P - invoice date

R - payment term (30/45 days depends on different clients)

W - bank received payment date

X - bank received amount

I've seen suggestions for conditional formatting based on one criteria, but I'm not sure how to set multiple criteria to get it to work.

Please can someone advise how to do this.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2023-12-13T08:36:37+00:00

You are welcome. You may also give feedback on it.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2023-12-13T08:18:48+00:00

File > info> change source> then select the correct file.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2023-12-13T07:03:43+00:00

This is for red instead of additional one.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2023-12-13T06:42:24+00:00

=Or(AND(P3+R3<TODAY(),P3<>"",W3=""),J3<>X3)

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2023-12-13T06:19:49+00:00

Move the second rule to the top and apply again.

Image

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2023-12-13T04:15:29+00:00

Try this one.

=Or(AND(P2+R2<TODAY(),W2=""),J2<>X2)

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2023-12-13T03:14:36+00:00

=AND(P2+R2>=TODAY(),W2="")

=AND(P2+R2<TODAY(),W2="")

Was this answer helpful?

1 person found this answer helpful.
0 comments

12 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-12-13T04:07:13+00:00

    Appreciate your help. However, can I add another condition that if the wrong key or partial payment is entered, it also needs to be highlighted in red?

    Was this answer helpful?

    0 comments