Forum Discussion
color cell if the time value for a given unit is within a range
Thanks for this. But it still doesnot color all cells
This time does not get highlighted
| 2020-05-01 09:50:00 | 3813 |
also i dont think this formula works if same time values are seperated by more than one cell
| 2020-05-01 11:35:00 | 3642 |
| 2020-05-01 10:10:00 | 3642 |
| 2020-05-01 08:17:00 | 3642 |
| 2020-05-01 16:34:00 | 3813 |
| 2020-05-01 14:53:00 | 3813 |
| 2020-05-01 09:48:00 | 3813 |
| 2020-05-01 12:15:00 | 3813 |
| 2020-05-01 11:14:00 | 3813 |
| 2020-05-01 10:05:00 | 3813 |
| 2020-05-01 10:01:00 | 3813 |
| 2020-05-01 09:56:00 | 3813 |
| 2020-05-01 09:50:00 | 3813 |
| 2020-05-01 08:33:00 | 3813 |
| 2020-05-01 09:48:00 | 3959 |
| 2020-05-01 16:42:00 | 4344 |
| 2020-05-01 15:10:00 | 4344 |
| 2020-05-01 13:30:00 | 4344 |
| 2020-05-01 11:51:00 | 4344 |
| 2020-05-01 10:15:00 | 4344 |
| 2020-05-01 08:27:00 | 4344 |
jiwanjot , Like below :-
in fact same formula is now applied twice : once on A1 to A15 & then again on A2 to A16
See yellow highlighted in screenshot that follows
A1 to A15 rule : checks self and cell below
A2 to A16 rule : checks self and cell above
In combination, these two rules work fine for continuous cells with time differenc <10min
Here i changed some time values as test case to show it works :-
See modified attachment of this post
and for non-continuous cells, i'm afraid i can think of sorting as only option in excel formulas (i.e. without involving macro programing)