Conditional Formatting (cell colour – 3 tier) Based on value of one cell and date in another

Hi Ladies & Gents

I’m hopeless at anything beyond the very basic formula and would like help with the following if poss please:

I have a huge accounting spreadsheet with the following headings

A:Number, B:Date Sent, C:Debtor, D:Type, E:Gross, F:Payment Amount, G:Payment Date, H:Payment Method, I:Balance, J:Sent, K:Company Gross, L:Patient Gross, M:Company Balance, N:Patient Balance.

I need to be able to highlight (conditional formatting) all those outstanding payments that are over 30days old, over 60days, over 90days.

I was thinking of using a range of colours from pink to red as the debt gets older and basically thought I should be doing something along the lines of   IF column M or column N >0 and B:Date is Today-30 to -59 then colour light pink; B:Date is Today-60 to -89 then mid pink; B:Date is Today-90 or more then Red.

I am completely clueless as to how to make that work – any help would be great.

Many thanks


By: Nicky

Leave a Reply

Your email address will not be published. Required fields are marked *