Conditional formatting

[BIGPHILL]

Morning, I hope someone can help.

I have 2 columns. Column A represents a Target Date, Column B represents a Review date.

If the review date is within a month of the target date then i would like the review date cell to go green.

Also if the review date is 1 month out of the target date i would like the review date cell go amber,

and if the review date is 2 months out of the target date then the review date cell go red.

Can anyone please advise me of how to achieve this or what formula to use.

Thanks,

 

[Gerald Higgins]

Hi, welcome to the board.

This is your basic conditional formating, BUT you will need to give some thought to the details of how this will work.

What exactly does this mean . . . ?

If the review date is within a month of the target date

Let's assume you mean, if the review date is no earlier than the target date, and no later than one month after the target date.
You might think I'm being pedantic, but this could also be read to mean if the review date is up to one month earlier or later than the target date.
Or, it could mean if the review date is any date up to the date one month after the target date.

Also, because months are different lengths, it will be easiest if we define "month" as a standard length of, say, 30 days.

Let's assume Target Date is in A1, Review Date is in B1.
For making B1 green, select B1.
Open the CF dialog box, and choose "Use a formula to determine which cells to format"
"Format values where this formula is true"

=B1< A1+30

and select a green format.

If you can get this to work, then add more similar conditions for the other colours.

Remember to check the sequencing in the CF dialog box - conditions can be re-prioritised if necessary.

Remember - if ALL data HAS to be either green, amber, or red, you can do this with only two conditions, and use default formating to apply one of the other formats.
However if you really have green, amber, red, and let's say blank for others, you will need three conditions.

 

[BIGPHILL]

Hello thanks for reply, i have tweeked my spreadsheet now. I have a date column in my spreadsheet. I want the date in the cell (which i type) to go Red if the date has come and gone, amber if the current month and green if the date is ahead.

I know there is conditional formatting, however it only allows me to choose next month to go green, and last month to go red, not all the other months, for example 2 months in advance.


Anyone have any suggestions for me on how to do this.

Thanks

 

[Gerald Higgins]

Can you give us full details of an example of "2 months in advance", and what exactly you want to do here ?