Finding the Earliest Time by Day

A reader needs to find the difference between the time listed and the earliest time listed for that same day. Here’s the data:

Date Time Difference
6/9/2014 14:49:05 0:00:00
6/9/2014 14:49:47 0:00:42
6/9/2014 14:50:33 0:01:28
6/9/2014 14:51:17 0:02:12
6/9/2014 14:51:31 0:02:26
6/9/2014 14:51:56 0:02:51
7/9/2014 6:19:55 0:00:00
7/9/2014 6:21:09 0:01:14
7/9/2014 6:21:31 0:01:36
7/9/2014 6:22:25 0:02:30
7/9/2014 6:22:53 0:02:58
7/9/2014 6:23:23 0:03:28
7/9/2014 6:23:47 0:03:52

The formula in the Difference column, C2, is

, filled down to fit the data.

It’s an array formula, so don’t type the curly braces, but enter with Ctrl+Shift+Enter, not just enter. The array part of the formula, the part subtracted from B2, is the smallest value where the date in column A is a match. By selecting everything in the MIN function in the formula bar and pressing Ctrl+=, you can see how Excel is calculating the minimum.

Because we’re dealing with times, the numbers aren’t so easy to read. But the important part is at the end of the array – a bunch of empty strings. When the date doesn’t match, the IF function returns an empty string. MIN ignores any text, so only the smallest of the numbers listed is returned.

5 thoughts on “Finding the Earliest Time by Day

  1. @Tristan, interesting but seems a little long-winded. Isn’t this a bit simpler for non-CSE?

    =C2-MIN(IF(N(INDEX($A$2:$A$14=B2,)),$B$2:$B$14,""))
  2. If the data is sorted by date + time (like the sample), this formula will be enough.

    =IF(A2A1,0,B2-N(B1)+C1)

    Regards

  3. @Ihm

    MIN also ignores logicals, so the value_if_false clause can be omitted:

    =B2-MIN(IF(N(INDEX($A$2:$A$14=A2,)),$B$2:$B$14))

    Regards

Leave a Reply

Your email address will not be published. Required fields are marked *