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.
=B2-MIN({0.617418981481482;0.617905092592593;0.6184375;0.618946759259259;0.619108796296296;0.619398148148148;"";"";"";"";"";"";""})
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.
