How to highlight the cells fill colour when the formula value is less than '0'

[jk51]

Hi,
Quick help please. How to highlight the cells fill colour when the formula in a cell value is less than zero. I have tried conditional formatting and highlight the cell less than 0 but no colour fill. Doesn't work.

For example see two columns below first column is 'random date' and second column is 'how many days left?' I want to highlight yellow colour fill less than '0' in the second column. The result output is 3 cells because less than 0 days to go?

Date How many days left?
14/06/2017 -2 days to go
25/11/2019 892 days to go
05/06/2017 -11 days to go
02/06/2017 -14 days to go
18/11/2017 155 days to go

Thank you

Mr Singh

 

[bosco_yip]

Please check the Conditional Formatting new rule formula

Regards

 

[NARAYANK991]

Hi ,

From what I can see , the result in the second column ( the column on the right ) is negative if the date in the first column ( the column on the left ) is earlier than today's date.

So you can use the following to conditionally format the cells in the right hand column ; suppose your data range is A2:B6 , you can select the range B2:B6 and use the CF formula :

=$A2 < TODAY()

and use a Fill Colour of your choice.

Alternatively , you can enter the formula =TODAY() in some cell , say J1 , and use this in your CF formula :

=$A2 < $J$1

Narayan

 

[jk51]

Thank you for all help, Narayank991 and bosco_yip. It is working.

Best,

Mr Singh