Conditional Formatting Based on Date

[ashdougx]

Hi,

I am working on a business development spreadsheet in work.

Column A = Name of contact
Column E = Date of last contact (in date format dd/mm/yy)

I want column A to:

Turn green if I contacted the person between 0 - 30 days ago
Turn yellow if I contacted the person between 31 - 60 days ago
Turn red if I contacted the person over 90 days ago

Is there a way I can have the colours change dependent on when I update the spreadsheet to change the "Date of last contact" column?

Thanks in advance,
Ash

[CK76]

Assuming Column E contains date value and not text string, and that row 1 is header row...

CF for Green
=(Today()-$E2)<31

CF for Yellow
=AND((Today()-$E2)>=31,(Today()-$E2)<=60)

CF for Red
=(Today()-$E2)>90

[Aardigspook]

Welcome to the forum.

Select your range in Column A. Click 'Conditional Formatting', 'New Rule', 'Use a formula to determine which cells to format.'
Put this in the formula box:

Please Login or Register  to view this content.

Choose green fill.
Repeat with these:

Please Login or Register  to view this content.

Note that a date between 61 and 90 days will be left un-coloured - is that what you want or was either the 60 or 90 a typo?

The formulae above assume that your data starts in row 2, so your A range is A2:Abiggernumber. If that's not the case, replace E2 in the formulae with Enumberofstartingdatarow.

Hope that helps.

[ashdougx]

Hi,

Thanks for the tips - this worked! I did mean for yellow to be between 31 - 89 so I managed to change the formula accordingly.

Thanks again!!

[Aardigspook]

You're welcome, glad we could help and thanks for the feedback.

If that takes care of your original question, please take a moment to mark this thread as Solved so others know there's an answer here (see instructions in my sig). Thanks.