Calculating Time duration from time stamp and grouping them by the hour

[Chi]

I have time stamps when each person logs in and out of a terminal through out the day. I want to calculate the utilization of each terminal grouped by the hour. My challenge is when the time stamp overlaps the whole hour. For Eg. between 8 to 9, Terminal A was used for 35+15 = 50 mins. Input Data is for more than 250 terminals and they are used 24 hours. (no output for person required). Is there a way to figure this with formulas? Please help.


Input DATA
Terminal Person Time in Time Out
A Chris 6/29/2017 7:35 6/29/2017 8:35
A Chris 6/29/2017 8:45 6/29/2017 9:15
A Betty 6/29/2017 9:45 6/29/2017 10:15
A Betty 6/29/2017 10:25 6/29/2017 10:35
A Betty
A Betty
B Nancy
B Nancy
B Nancy
B Nancy

Desired Output
Utilization/Duration (in Mins)
From --> 7:00 8:00 9:00 10:00
To --> 8:00 9:00 10:00 11:00
A =25 =35+15 =15+15 =15+10
B

 

[bizlife]

Use the formula
=TEXT(RIGHT(C3,17),"hh")

This will display only hours.
I hope this helps.

 

[bosco_yip]

1] In E3, formula copy down :

=IF(C3="","",ROUND((D3-C3)*24*60,0))

2] In F3, formula copy across and down :

=IF($C3="","",MAX(0,MIN(G$2,--TEXT($D3,"hh:mm"))-MAX(F$2,--TEXT($C3,"hh:mm")))*24*60)

3] See attached file.

Regards
Bosco

 

[Chi]

exactly what I need - thanks Bosco!