Trying to calculate proportion per hour that stopwatch ran

[KATEYKAT]

Hi there I'm an undergrad using Excel for Mac 2011 working in a university lab and am having lots of trouble trying to calculate what fraction of each hour a fish spends in a certain swim chamber.
I have been able to calculate the proportion per hour using Pivot tables and it's working alright EXCEPT for when the start time is in a different hour than the end time. In these cases excel tells me that the whole "Time in Chamber" occurs in the same hour as "Time left chamber". Looking at visits number 3 and 5, excel is telling me that the whole "time in Chamber" occurs in the 14th and 16th hour respectively, which is what I am trying to avoid/solve). Is there any way to split up the data so that I am given the sum of seconds in the chamber PER HOUR (i.e. no more than 3600s per hour?) I have been trying it manually but that takes a really long time :/

Below is a sample of the data I'm working with:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Visit number[/TD]
[TD]Hour[/TD]
[TD]Time in Chamber (s)[/TD]
[TD]Time entered chamber[/TD]
[TD]Time left chamber[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12:00[/TD]
[TD]28.0[/TD]
[TD]12:54:47[/TD]
[TD]12:55:15[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]13:00[/TD]
[TD]86.4[/TD]
[TD]13:17:22[/TD]
[TD]13:18:49[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]14:00[/TD]
[TD]729.0[/TD]
[TD]13:48:10[/TD]
[TD]14:00:19[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]14:00[/TD]
[TD]8.8[/TD]
[TD]14:53:35[/TD]
[TD]14:53:44[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]16:00[/TD]
[TD]7200.0[/TD]
[TD]16:55:00[/TD]
[TD]14:55:00[/TD]
[/TR]
</tbody>[/TABLE]



Thank you so much!!!

 

[KATEYKAT]

Sorry shoot I mixed up the time values in visit 5.
Please consider that in visit 5:
- "Time entered chamber" is actually 14:55:00
- "time left chamber" is 16:55:00
Thanks again!