May 05 2020 10:05 AM
Hi All,
I am trying to color the time cells red if the time differnces for a specified unit is less than 10 minutes.
For example, for unit 3813 if any of the first, second, third, fourth time values are within 10 minute of each other then they should be higlighted red. Thanks
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 12:29: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 |
2020-05-01 16:46:00 | 4838 |
2020-05-01 15:39:00 | 4838 |
2020-05-01 14:33:00 | 4838 |
2020-05-01 13:24:00 | 4838 |
2020-05-01 12:22:00 | 4838 |
May 05 2020 11:52 AM
@jiwanjot , apply below formula in conditional formatting on time cells as shown :-
This file is also attached for ref.
May 05 2020 02:22 PM
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 |
May 05 2020 07:15 PM
May 05 2020 07:42 PM - edited May 05 2020 07:43 PM
@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)
May 06 2020 11:52 AM
Yes sort works I sorted first with unit and then by time.
Thank you so much for your help