Help needed in Conditional Formatting please

[DebbieK]

Hi

Can someone help me please, im pretty new to Excel and am having my first attempt at conditional formatting. I have created a spread sheet which has a column of dates.

I need the sheet to highlight todays date in red so I have:

=INT(G5)=TODAY()

I then need it to flag up dates in yellow up to 31 days ahead of todays date:

=AND(INT(G5)>TODAY(),(INT(G5)-TODAY())<31)

My problem is that I also need it to flag up dates prior to today, i.e. overdue dates in another colour. So what test do I need to put in for condition 3. Any help gratefully received

[fairwinds]

Welcome!

How about:

=INT(G5) < TODAY()

[onlyadrafter]

Hello and welcome to the board,

The three conditional formats you require are as follows:

CONDITION 1

Cell Value is equal to =TODAY()

CONDITION 2

Cell Value is less than = TODAY()

CONDITION 3

Cell Value is between =TODAY() =TODAY()+31

Select formats as required.

Use the format painter to format the other cells/column.

Is should be obvious into which INPUT BOXES the data goes in, any problems/queries let me know.

[Whisperer14]

Debbie,

As Fairwind has shown, that will give you the answer you require and I offer the following formulae for you to experiment with. You can do it without formulae by using cell value. EDIT (as shown above)

If G5 is one of the dates in your column (formatted as a date format) that you wish to apply conditional formatting to then there is no need for the INT part of your formulae and they can be rewritten as shown below:

=G5

[DebbieK]

Thank you all so much for your help I can stop tearing my hair out now Wish I had found these message boards before, they are great thanks again

[Aladin Akyurek]

Originally Posted by DebbieK

Thank you all so much for your help I can stop tearing my hair out now Wish I had found these message boards before, they are great thanks again

I think G5 should at least be checked to house a number with each condition, otherwise the empty cells would satisfy some of your conditions and colored accordingly.