Conditional date formatting in numbers

dozzzzy,

I've worked out a solution that I think is close to your requirement. Here is an example. The five columns on the right, E through I, are intended to be hidden because they are intermediate calculations. Hiding them or not is your decision.

As you can see, I formatted the calculation called "Nearest Birthday", not the Age Cell. This is because it is much easier to add a conditional format to a cell that contains the actual conditional test value. It would't be impossible to Conditionally Format the Age Cell, but it's a lot more work.

Next I calculated numeric values for Year, Month and Day for the D.O.B., for use in later expressions.

Now I calculate the Age in Years.

Next I calculate the Date of the most recent birthday, the Last Birthday.

At this point I could proceed to determining the Nearest Birthday, but for clarity I first calculated the Next Birthday. I could have saved a column, but another column's expressions would have become slightly more complex.

Getting near the end here, I calculate the Nearest Birthday, the one closest in time to today's date.

Nothing left to do but apply the Conditional Formatting to the Nearest Birthday Date Cell.

Please remember that it was December 30 when I did this, so that date is the one that will be green in the example.

I hope this will suit your need.

Regards,

Jerry

Hi dozzzzy,

That seems like a lot of complex rules for Conditional Highlighting.

Apply those rules first in tables. Then we can work on ways to apply Conditional Highlighting to the Age column.

Here is a start. Use another table "Calculations":

Formula in "Calculations" A2 is DATE(YEAR(TODAY()),MONTH(Table 1::D2),DAY(Table 1::D2))

Formula in "Calculations" B2 is A2−"15d"

And so on in other columns through your other rules.

BTW, I am having trouble understanding your rules 🤔.

Regards,

Ian.

This is what it sounds like they are wanting to do.

I think the tests are worded a little odd for me, but what I think they are trying to do is a sliding color scale to tell them how far away the birthday is.

if it’s more than fifteen days, light orange

if it’s less than fifteen days, but not today, dark orange

if it’s today green

two weeks overdue (I.e. your forgot even with a spreadsheet!?) dark red

more than two weeks overdue light red.

i would just make a column doing that calculation (day difference) and put a very simple set of conditional format on that column.

jason

dozzzzy,

I don't think I would tackle conditional formatting as complex as that. I wonder if you wouldn't be just as happy without the "current year" and "31st of Dec" tests, rather just some number of days before or after today's date.

Jerry

dozzzzy

Thanks for the feedback. I did have some thoughts about how you will be using the table. Mainly, if you are going to have 20K entires, it could be time-consuming to get to the interesting part. A lot of scrolling. If you began the "Nearest Birthday" at -15, you could sort on that column and have much less scrolling to get to the current date, which otherwise would be in the middle of the list.

It was an interesting project to program.

Best regards,

Jerry