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

Anonymous

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!!

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 Volunteer Moderator

Hi,

Try this

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

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

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

Hope this helps.

0 comments

59 additional answers

Ashish Mathur 102.4K Reputation points Volunteer Moderator

2014-03-04T04:12:12+00:00

Hi,

Try this formula in cell F2

=IF(OR(H2>G2,AND(G2<>"",G2<today(),H2="")),"Overdue",IF(H2<G2,"In Time",""))
Was this answer helpful?

0 comments No comments
Anonymous

2014-03-04T02:09:17+00:00

Hi,

Please ignore my query of earlier today (copied below).

I request assistance with this:

Column G has the due date

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

I needed to generate a value of "Overdue" or "In Time" in Column F depending on:

(i) If date in Column H is > date in Column G, Column F should show value of Overdue

(ii) If date in Column G is less than today, and Column H has no date (i.e. it is blank - it has not yet been paid), Column F should show value of Overdue

(iii) If date in Column H is < or equal to date in Column G, Column F should show value of "In Time"

I tried the below formula and it gives an error.

=IF(G3<TODAY(), IF(ISBLANK(H3),"","Overdue",IF(H3<G3()"In Time"))3<G3()"In Time"))

I'm also putting this as a new thread, as I have sent multiple replies to this post, and I don't know if the query will be seen.

Many thanks!

_______________________

Please ignore the below from earlier today:

Hello

This works perfectly. Thanks so much.

Hope to get assistance with below, to close this issue for me:

=and(G2<>"",G2<today(),H2="")

The above formula provided in last response will highlight Column G correctly. Is it possible to have a value generate in Column I (that is, the letters OD) if the due date is anything less than today, but ignoring those where there is a value in Column H already?

This will remain the same:

Column G has the due date

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

Many thanks!
Was this answer helpful?

0 comments No comments
Ashish Mathur 102.4K Reputation points Volunteer Moderator

2014-03-04T02:01:34+00:00

Hi,

Try this

=if(and(G2<>"",G2<today(),H2=""),"OD","")

Hope this helps.
Was this answer helpful?

0 comments No comments
Ashish Mathur 102.4K Reputation points Volunteer Moderator

2014-03-04T01:57:31+00:00

You are welcome. If my reply helped, please mark it as Answer.
Was this answer helpful?

0 comments No comments