How to do duration based conditional highlighting without adding a column to calculate the duration

Question

Level 1

19 points

How to do duration based conditional highlighting without adding a column to calculate the duration

Hello Numbers users. I am trying to figure out if it is possible to do conditional highlighting using dates & durations without needing to add a dedicated column, as my spreadsheet has a lot of columns already and I can't add yet another one.

Use case: If an event has started and is not completed, and if 48 - 72 hours have passed (aka 2-3 days) since the start date), I would like the 'start date' to get conditional highlighting (yellow). And then if it has been 3-4 days it is highlighted orange, if more than 4 days it is highlighted as red. If the event is completed (regardless of the time since start), there would be no conditional formatting. In essence if there is a live (uncompleted) event that is between 2, 3, or 4 + days since start I'd like to be alerted by the start date being highlighted yellow, orange, then red.

I have attached a screenshot of a table which I formatted manually to show what it would look like if I can get it to work.

If such programming is possible please let me know if you know how to get it to work.

Alternatively if you have another idea on how to get some form of visual signal that will alert me as needed feel free to give me your suggestions.

Thanks - Rod

Posted on Aug 31, 2022 2:22 PM

Reply

Answer 1

Badunit

Level 7

30,300 points

Aug 31, 2022 3:02 PM in response to RWforumID777

I'm not sure if I got this exactly right but here is a go at it. I think it does generally what you want but I may have the "days before" numbers wrong. I think the date highlighting rules do not look at the time of day. If you require that, you may need that extra column you don't want.

Today is 8/31/22.

Select the entire column of dates when creating the rule. For the first rule, to get the B2 in the field click on the green oval then click on cell B2. When the rule is created, row 2 will refer to B2, row 3 to B3, etc.

The first rule assumes the completed date&time will be after the start date&time. If there is any possibility that they may be exactly the same (which is theoretically impossible), you can add a rule to cover that case.

Reply

Answer 2

RWforumID777 Author

Level 1

19 points

Aug 31, 2022 3:26 PM in response to Badunit

That works nicely. One issue, perhaps minor, the conditional highlighting does not have an option to include hours. After testing it looks like it is counting full days, likely based on midnight as the date of change. As a result some of my events may be more than 72 hours old (3 full days, resulting in orange highlighting if accurate to the hour) but would be formatted as 2 days old (resulting in yellow highlighting). In some ways that is fine and I can go with that. If you have any ideas on how make the highlighting triggered based on the time in the cell instead of defaulting to midnight that would be great. After looking through the menu options I don't think that can be done, unless if I want to create an additional column and then somehow do formatting such as "If column X value is 48-72 hours then yellow, 72-96 hours then orange, 120 or more then red".

Reply

Answer 3

Badunit

Level 7

30,300 points

Aug 31, 2022 3:49 PM in response to RWforumID777

I think you can do it with a few "number is less than" rules if somewhere in your spreadsheet you have the date&times you need to compare with. I'll work this up and get back to you.

Reply

Answer 4

Badunit

Level 7

30,300 points

Aug 31, 2022 3:58 PM in response to Badunit

C2 =NOW()-2

C3 =NOW()-4

C4 =NOW()-6

Make sure the rules other than the first one have that $ in the cell reference.

Reply

Answer 5

RWforumID777 Author

Level 1

19 points

Sep 1, 2022 10:51 AM in response to Badunit

Hello Badunit! That works also! Thanks for both methods, they are fantastic and either will work for me, I'll just have to select which to go with. - Rod

Reply

How to do duration based conditional highlighting without adding a column to calculate the duration

Similar questions