color cell if the time value for a given unit is within a range

[jiwanjot]

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

[#2]

@jiwanjot , apply below formula in conditional formatting on time cells as shown :-

This file is also attached for ref.

[#3]

@amit_bhola 

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

[#4]

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.

[#5]

@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)

[#6]

@amit_bhola 

Yes sort works I sorted first with unit and then by time.

Thank you so much for your help