How Many Hours Photo Timestamp is OUTSIDE of Visit Start / End Timestamps

[HobbesIsReal]

I am trying to create a quality assurance report. This report has Photo Timestamps that should have been taken in between the start and end times of the visit conducted.

I have a column in the report that flags any photo timestamp outside of the visit start / end times, but that means if it was even 1 minute outside the visit times it will be flagged.

So it is easy to do an IF statement to confirm if outside of visit time or not. If it is, I then need to figure out how many hours the photo is outside of the conducted visit.

I want to calculate how many hours a photo's timestamp is outside of the visit start / end time. So I have 3 columns....Photo Date Time Timestamp / Visit Start Date Time / Visit End Date Time.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]Photo Timestamp[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]Visit Start Date Time[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]Visit End Date Time[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 201"]
<tbody>[TR]
[TD="class: xl65, width: 201"]How Many Hours Outside Photo is Outside of Visit[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/26/2018 2:29 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/26/2018 1:18 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/26/2018 2:14 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]02/14/2018 9:51 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/24/2018 10:18 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/24/2018 10:48 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]02/12/2018 8:55 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/24/2018 11:19 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/24/2018 11:32 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]02/12/2018 9:46 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/26/2018 3:49 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/26/2018 4:15 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/23/2018 3:14 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/23/2018 2:13 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/23/2018 2:55 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/23/2018 3:12 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/23/2018 2:13 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/23/2018 2:55 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/18/2018 9:33 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/24/2018 8:33 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/24/2018 8:40 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]06/12/2017 10:14 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/24/2018 9:01 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/24/2018 9:32 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/18/2018 9:33 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/24/2018 10:52 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]04/24/2018 11:10 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you for any help / insight / suggestions you might have.

 

[HobbesIsReal]

Is there a way to upload a small same spreadsheet that might help?

Hobbes

 

[HobbesIsReal]

[h=3]Chihiro over at Chandoo provided a perfect solution!!! (Here https://chandoo.org/forum/threads/how-many-hours-photo-timestamp-is-outside-of-visit-start-end-timestamps.38422/ )[/h]

To combine inside/outside check with hours/time outside.
=IF(MAX(A2:C2)=A2,A2-C2,IF(MIN(A2:C2)=A2,B2-A2,"Inside"))

The format the cells: [h]:mm;@