May 07 2021 04:17 AM
Hi all,
I'm trying to set up a rule for conditional formatting but kind of stuck.
I have a sheet with a list of invoices. I want to highlight rows that are past due date and haven't been paid yet. I have two columns that are used in this case. Column D (starting at D:2) is the "due date" column, and column E (starting at E:2) is the one where I type in the dates when I've made the payments.
So essentially I would like to set up a rule that tests column D for the date being older than today and column D if there is anything in that cell.
Can you assist me on this? All help would be much appreciated.
Have a great day!
Greg
May 07 2021 05:26 AM
Hi Greg,
You could Use the formula as shown below.
Note that dates are franche formated in this picture, and that references to E2 and D2 are relative so that the formula can work for every selected cell.
Hope this helps!
May 07 2021 05:29 AM
You may apply the rule to your entire range (e.g. D2:E1000) with rue formula
=($D2<TODAY())*($E2="")
May 07 2021 05:44 AM
thank you for the quick reply, however I couldn't manage to apply the rule properly. I have attached a slimmed down version of the XLSX, can you please have a look and let me know what I missed? I would like to highlight the entire row where the date is before today AND there is nothing in the "PAID" column.
thanks a lot
May 07 2021 05:45 AM
May 07 2021 05:55 AM
Here is your file with the conditional format formula.
@Sergei Baklan formula would also do the trick.
Use the one you prefer!
May 07 2021 05:57 AM
May 07 2021 05:57 AM
SolutionPlease check in attached file.
May 07 2021 05:59 AM
Full row is highlighted, please check the file.
As a comment, I'd convert your range into structured table (Ctrl+T), when rules will be expanded automatically.
May 07 2021 06:09 AM
May 07 2021 05:57 AM
Solution