Excel conditional formatting to track overdue invoices while ignoring paid invoices

Anonymous
2025-01-31T18:46:18+00:00

Hi there,

We need some help on our invoice log. I need past due invoices to highlight in red while paid invoices are left blank. Preferable for column B with the invoice date to be the highlighted cell once past due i.e. Net30.

Current columns on our table are as follows:

A) Invoice Number

B) Invoice Date

C) Project #

D) client

E) Invoice Amount

F) Cumulative Total

G) Paid (Y/N)

H) Receivables

I) Total

J) Income

K) State

Only formula's currently in the sheet are:

=SUM(H23:H2580)
=SUM(E23:E2580)

Appreciate any help! Thank you

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 No comments
Answer accepted by question author
Rich~M 20,370 Reputation points Volunteer Moderator
2025-01-31T20:22:51+00:00

Here is the formula to add the Conditional Formatting to rows where the Invoice Date is more than 30 days prior to today and Column G has an "N" to indicate that the invoice has not been paid. Follow this procedure.

  1. Select all of the cells that you would like to be formatted on the delinquent rows. This could include only Column B or multiple columns if you want more of the row highlighted.
  2. On the Home ribbon open Conditional Formatting>New Rule>Use a Formula. In the Format Values line enter this formula.

=AND($B2+30<TODAY(),$G2="N")

  1. Click on the Format button at the lower right and choose the formatting you want in the Format Cells dialogue. Then click OK and OK.

Was this answer helpful?

3 people found this answer helpful.
0 comments No comments

1 additional answer

Sort by: Oldest
  1. Anonymous
    2025-01-31T22:14:56+00:00

    Rich, that worked perfect. Thank you for the help! Good to know for moving forward as well.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No comments