Conditional format based on corresponding date



I hoped you could help me with an issue I still haven’t gotten my head around.


I am working with a table that shows the uptime in percentage for different services:TableMy issue is that currently all cells in a column in colored either red or green based on their value being greater or lower than the value in the black cell in the same column.

e.g. for Service 1, all values are compared to the black cell above them. Therefore, those values that are 99,33% and 97,21% are red.

The plot twist:

The 99.4%  threshold applies only to those cell values that are after 2018. All cell values that have a date in 2017 has a threshold that is 0.1% lower. In other words, the 99.33% values under service 1 should be green,as they should be conditonally formatted towards a lower threshold.

Also, this table uses the INDEX function to gather data from a larger table. This means that I can’t simply change the conditional format to the cells that in this view are before 2018.


My guess is that I have to apply a conditional format to all cells that first checks the date, and then apply the threshold accordingly.

If there exists any other methods, I would truly appreciate to hear your ideas.


Thank you!

By: Even

Leave a Reply

Your email address will not be published. Required fields are marked *