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: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-04-08T00:45:22+00:00

    Thanks, that worked great!

    For the second issue I need help with, I want the status to change dependent on the due date. Currently the 'Status' section can be manually changed to BLUE (On Target), RED (Overdue), GREEN (Completed).

    I want this to automatically change.

    • Once I input a date in the 'Date Closed' column, I want the 'Status' cell to automatically change to 'Completed (GREEN)'
    • If there has been no date input in the 'Date Closed' column and the due date has passed, I want the 'Status' to change to 'Overdue (RED)'
    • Once I do enter a new action and input a date in the 'Due' column, I want the 'Status' to automatically change to 'On Target (BLUE)'

    I hope this explains my second issue and isn't too difficult to do.

    Was this answer helpful?

    0 comments
  2. Ashish Mathur 102.4K Reputation points Volunteer Moderator
    2014-04-07T08:02:48+00:00

    Hi,

    In cell C2, enter this formula in the Conditional formatting dialog box and format the cell as Green Fill colour

    =AND(C2<>"",C2<=A2)

    In cell C2, enter this formula in the Conditional formatting dialog box and format the cell as Red Fill colour

    =AND(C2<>"",C2>A2)

    Now you may copy the cell > Select the range of cells below > right click > Paste Special > Formats.

    I am not clear with your second question.

    Hope this helps.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-04-07T00:31:43+00:00

    Hi Ashish,

    I have two conditional formatting issues I require help with.

    1. Once I input a date in the 'Date Closed' column, I want it to be highlighted either GREEN if it is completed on time i.e. on the due date or earlier, or RED if it completed after the due date.
    2. I have set the 'Status' section as a drop down select for either: 'On Target', 'Overdue' and 'Completed'. Once an action has past its due date and has not been completed I would like that action status to change to 'Overdue'

    .

    Was this answer helpful?

    0 comments
  4. Ashish Mathur 102.4K Reputation points Volunteer Moderator
    2014-03-18T05:16:47+00:00

    You are welcome.  If my reply helped, please mark it as Answer.

    Was this answer helpful?

    0 comments