Forum Discussion

jiwanjot's avatar
jiwanjot
Copper Contributor
May 05, 2020

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

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:003813
2020-05-01 09:56:003813
2020-05-01 09:50:003813
2020-05-01 08:33:003813
2020-05-01 12:29:003959
2020-05-01 16:42:004344
2020-05-01 15:10:004344
2020-05-01 13:30:004344
2020-05-01 11:51:004344
2020-05-01 10:15:004344
2020-05-01 08:27:004344
2020-05-01 16:46:004838
2020-05-01 15:39:004838
2020-05-01 14:33:004838
2020-05-01 13:24:004838
2020-05-01 12:22:004838
excel

5 Replies

    • jiwanjot's avatar
      jiwanjot
      Copper Contributor
      May 05, 2020

      amit_bhola 

       

      Thanks for this. But it still doesnot color all cells

      This time does not get highlighted

      2020-05-01 09:50:003813

       

      also i dont think this formula works if same time values are seperated by more than one cell

      2020-05-01 11:35:003642
      2020-05-01 10:10:003642
      2020-05-01 08:17:003642
      2020-05-01 16:34:003813
      2020-05-01 14:53:003813
      2020-05-01 09:48:003813
      2020-05-01 12:15:003813
      2020-05-01 11:14:003813
      2020-05-01 10:05:003813
      2020-05-01 10:01:003813
      2020-05-01 09:56:003813
      2020-05-01 09:50:003813
      2020-05-01 08:33:003813
      2020-05-01 09:48:003959
      2020-05-01 16:42:004344
      2020-05-01 15:10:004344
      2020-05-01 13:30:004344
      2020-05-01 11:51:004344
      2020-05-01 10:15:004344
      2020-05-01 08:27:004344
      • amit_bhola's avatar
        amit_bhola
        Iron Contributor
        May 06, 2020

        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)

         

Resources