# Thread: Conditional Formatting based on a formula Thanks:  1 Post #5204427 (1) Likes:  1 Post #5204427 (1)

1. ## Conditional Formatting based on a formula

Hi all,

I've got a spreadsheet with dates in two columns, the date in the second column is calculated automatically based on the date in the first column.

I need to apply Conditional Formatting to the cells in the second column so if the difference between that cell and today's date is greater than 90, make it green, between 1-90 orange, and 0 or under red.

Just concentrating on the first part initially, I've got the following formula for anything above 90, however, it doesn't change the formatting:

="(Value(M6)-Value(TODAY())>90)"

(M6 is in the second column, and is the top of the dates)

Can anyone point me in the right direction?

2. ## Re: Conditional Formatting based on a formula

Assuming M6 is a numerical date
TODAY() is ALWAYS a number, you dont need to do a VALUE() on it.

Select the range you want highlight

Conditional Formatting
New Rule
Use a formula to determine...

=(M6-TODAY()<=0)
Format as red

=(M6-TODAY()>1)
Format as orange

=(M6-TODAY()>90)
Format as green

3. ## Re: Conditional Formatting based on a formula

Thanks! I'd added the VALUE() to TODAY() clutching at straws...
That seems to be working nicely, thanks!