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 {=B2-MIN(IF($A$2:$A$14=A2,$B$2:$B$14,""))}, 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.