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
1 |
{=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.
1 |
=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.