Numbers Formula Help - Date Range Lookup & Highlight Cell With A Constraint

Hi guys,


Would really appreciate some help with creating a numbers formula as I can't get mine to work.


See screenshot of example table below which contains random dates.


I'm looking for a formula that will highlight all the cells (e.g. in yellow) if ANY of 3 or more of the dates within the entire table are within 3 weeks (21 days) of each other. (I have manually highlighted the cells in yellow as an example)


If another set of 3 or more different dates are also within 3 weeks of each other I would like all the applicable cells to be highlighted a different colour (e.g. in red). (I have manually highlighted the cells in red as an example)


The purpose of me needing this formula is because I have a much much bigger table that has dates that are updated every week which I need to highlight 3 or more dates which are all within 21 days of eachother. As you can imgine this is a very taxing and long process when trying to do it manually by eye.


Any help or suggestions would be great.


Cheers Nick


MacBook Pro 16″, macOS 13.0

Posted on Jan 23, 2023 8:23 PM

Reply
2 replies

Jan 24, 2023 2:53 AM in response to NickB2708

Hi Nick,

sorry but that will not work with Numbers.


Each cell can only have data (in your case a date) or a formula.


To highlight a cell you would use Conditional Highlighting, but here you can not use complicated rules / calculations to do what you want.


The system would also run into an issue if one date is part of two or more groups of dates.

What color should be used?


Is it possible to have all dates sorted in one single column?

That could make it easier to find matching groups.


What is the background of these random dates and their position in this table?

Every piece of background Information may help to get you closer to a solution.


Regards Ralf

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Numbers Formula Help - Date Range Lookup & Highlight Cell With A Constraint

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.