Forum Discussion
color cell if the time value for a given unit is within a range
jiwanjot , apply below formula in conditional formatting on time cells as shown :-
This file is also attached for ref.
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)
- 1. Regarding the 3rd cell not getting colored, it is easy : current conditional format formula from A1~Aend checks "self cell and cell *below* "
for time difference. One can add another such conditional formatting rule with another similar formula on range A2~onwards which but checks "self cell and cell *above* "
Both above 2 rules when applied together would color both cells for each continuous pair.
2. As for cells separated by more than one cell, if situation permits, data may be sorted first using data sort dialogue box, with 2 levels of sorts : first by unit no. and next level by time. Then more than one cell separation case won't exist. After sorting, conditional formatting rule to be applied.
3. If situation doesn't permit data sorting, then, probably it needs creating a loop (macro programming), which is not possible by excel sheet formulas.