How to change a cell’s color based on another cell’s date?

fd,

Conditional formatting only works on the data in the cell being formatted. There are some ways to get the effect you want. Probably the easiest is to add an adjacent column in which you have data that mirrors the conditional data, and then conditionally format the new column. In this way you draw attention to the data by coloring the adjacent cell.

Another way is to add some innocuous format condition data to the cell you want to highlight. For instance, if you want to highlight the cell with a distinctive background color, you could do this: =IF(BondMaturityDate warrants highlight, InitialExpression & "**", initialExpression) The highlight condition can then be based on the text value ending in "**". The "**" is itself a signal.

A little more awkward to manage if the table changes shape in the future is to construct a single column table with the maturity dates in it. Conditionally format that 1-col table to have the format signal background, and set the text color to zero opacity. Then, place this new table directly below the column that needs formatting in the main table. This is a very powerful way to apply formatting when it would be otherwise very difficult.

Jerry

Conditional formatting is a great way to visually highlight specific dates in your spreadsheet. While you can't directly change a cell's color based only on another cell's date, there are clever workarounds. You can add a helper column with a formula to trigger the formatting, or even use a hidden table with conditional formatting. If you're looking for fun ways to teach kids about dates and colors, check out the coloring pages on Disegnidacoloraremondo.com!