Calculating hours between selected cell entries in regards to a certain cell

[David2]

I have cells with the following, Day/Month/Year/Time/Timezone, format:
15. March 2018 13:18:42 CET


I would like to chose a certain cell, mark it as t0 (by putting 0 as value or checking a box or something) and then having Excel calculate the time difference for other cells (in hours, rounded down) in regards to that cell.


Example:

https://imgur.com/GRJvR8b


Additional info:
- If possible without macros
- If I could choose which cells I'd like to have time calculated for, that'd be great (see Example 3).

Hopefully I've explained it at least somewhat clear.

 

[edmitchell]

How about?

[TABLE="class: grid, width: 0"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Time[/TD]
[TD]Initial[/TD]
[TD]Step1[/TD]
[TD]Difference[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]15/03/2018 13:18:42[/TD]
[TD][/TD]
[TD]07:06:03[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]15/03/2018 06:12:39[/TD]
[TD]y[/TD]
[TD]00:00:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]15/03/2018 02:09:10[/TD]
[TD][/TD]
[TD]##############[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

where the formula in C2 (and then copied to the others rows):

=IF(ISERROR(MATCH("Y",$B$2:$B$4,0)),"",A2-INDEX($A$2:$A$4,MATCH("Y",$B$2:$B$4,0),1))

and that in D2 is (and then copied to the others rows)

=IF(C2>0,C2,"")

You enter 'Y' into column B in the row you need to use as the reference point.

You can obviously hide column C, which just gives the intermediate result (in hh:mm:ss)

the cell format for d2, etc should just be set to "h" to display just the hours of the result


does that do what you need?