Need help with conditional formatting using an IF function to recognise blank cells and overdue dates

Anonymous
2013-07-04T01:44:42+00:00

Hi

I am wanting to add conditional formatting to a spreadsheet which lists invoices and their due dates, along with payment date.

One column (H) has the payment due date.

Another column (M) has the date paid. This column is left blank if unpaid to date.

I want to highlight only cells in column H in which the due date is anything less than today, but ignoring those where there is a value in column M already. Essentially, I do not want to highlight anything which is overdue, but already paid (if that makes sense).

Having trouble working out what formula I need to do to show this in the conditional formatting area. I can get it to do the dates past easy enough, but cant get it to also ignore blank cells.

Have tried using ISBLANK.

Would greatly appreciate anyone's help with this formula!!

Thanks!!

Microsoft 365 and Office | Excel | For home | 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
Ashish Mathur 102.4K Reputation points Volunteer Moderator
2013-07-04T02:40:07+00:00

Hi,

Try this

  1. Click on cell H2 and go to Home > Conditional formatting > New Rule > Use a formula to determine which cells to format
  2. In the formula bar there, enter this formula

=AND(H2<today(),I2="")

  1. Click on Format and select any Fill/Font colour
  2. Click on OK/Apply
  3. Copy cell H2, select H3:H50 right click > Paste Special > Formats

Hope this helps.

Was this answer helpful?

2 people found this answer helpful.
0 comments

59 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2014-03-03T00:14:57+00:00

    Hi,

     

    Try this

     

    1. Click on cell H2 and go to Home > Conditional formatting > New Rule > Use a formula to determine which cells to format
    2. In the formula bar there, enter this formula

     

    =AND(H2<today(),I2="")

     

    1. Click on Format and select any Fill/Font colour
    2. Click on OK/Apply
    3. Copy cell H2, select H3:H50 right click > Paste Special > Formats

     

    Hope this helps.

    Hello

    I believe this formula could help me as well, but, I can't get it to work with changed columns.

    In my case,

    Column G has the due date.

    Column H has the date paid. This column is left blank if unpaid to date.

    As in the inital query, I want to highlight only cells in column G in which the due date is anything less than today, but ignoring those where there is a value in column H already. I do not want to highlight anything which is overdue, but already paid.

    Many thanks for any help!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-02-27T05:06:57+00:00

    Thank you, it worked based on what I had written in this thread.

    However, I had made an error.

    I need Column G to be highlighted if (i) it is blank after 7 days (8 days or more) following the date in Column F, or, (ii) the date entered in column G is after 7 days (8 days or more) following the date in Column F

    At present, with this formula, if Column G is blank even a day after the date in Column F, it will change the formatting in Column F (fill red). With a date entered in Column G, it works fine.

    Can you help me resolve this, please?

    Also, in addition to the change in formatting (fill red), if the date is overdue in Column G (i.e. the date is 8 days or over after the date in column F or blank after 8 days or over), can I have the related entire row copied automatically to another sheet in the same workbook? Thanks very much.

    Was this answer helpful?

    0 comments
  3. Ashish Mathur 102.4K Reputation points Volunteer Moderator
    2014-02-26T22:56:13+00:00

    Hi,

    Try this

    1. Click on cell F2 and go to Home > Conditional formatting > New Rule > Use a formula to determine which cells to format
    2. In the Formula bar there, enter this formula

    =OR(G2="",G2-F2>=8)

    1. Click on Format > Fill > Red
    2. Click on OK/Apply
    3. Copy cell F2, select F3:F50, Right click > Paste Special > Formats > OK

    Hope this helps.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-02-26T17:34:27+00:00

    Hello

    Need assistance! 

    For my conditional formatting on an excel spreadshhet, I have Date Issue Received in column F and Date Issue Resolved in column G. If the issue is not yet resolved, column G will be blank. I need to highlight cells in column F where column G is blank or the date entered into column G is 8 days or over from the date entered in column F.  

    What formula do I use?

    Many thanks!

    Was this answer helpful?

    0 comments