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.

Photo Timestamp <tbody> </tbody>	Visit Start Date Time <tbody> </tbody>	Visit End Date Time <tbody> </tbody>	How Many Hours Outside Photo is Outside of Visit <tbody> </tbody>
04/26/2018 2:29 PM <tbody> </tbody>	04/26/2018 1:18 PM <tbody> </tbody>	04/26/2018 2:14 PM <tbody> </tbody>
02/14/2018 9:51 AM <tbody> </tbody>	04/24/2018 10:18 AM <tbody> </tbody>	04/24/2018 10:48 AM <tbody> </tbody>
02/12/2018 8:55 AM <tbody> </tbody>	04/24/2018 11:19 AM <tbody> </tbody>	04/24/2018 11:32 AM <tbody> </tbody>
02/12/2018 9:46 AM <tbody> </tbody>	04/26/2018 3:49 PM <tbody> </tbody>	04/26/2018 4:15 PM <tbody> </tbody>
04/23/2018 3:14 PM <tbody> </tbody>	04/23/2018 2:13 PM <tbody> </tbody>	04/23/2018 2:55 PM <tbody> </tbody>
04/23/2018 3:12 PM <tbody> </tbody>	04/23/2018 2:13 PM <tbody> </tbody>	04/23/2018 2:55 PM <tbody> </tbody>
04/18/2018 9:33 AM <tbody> </tbody>	04/24/2018 8:33 AM <tbody> </tbody>	04/24/2018 8:40 AM <tbody> </tbody>
06/12/2017 10:14 AM <tbody> </tbody>	04/24/2018 9:01 AM <tbody> </tbody>	04/24/2018 9:32 AM <tbody> </tbody>
04/18/2018 9:33 AM <tbody> </tbody>	04/24/2018 10:52 AM <tbody> </tbody>	04/24/2018 11:10 AM <tbody> </tbody>

<tbody>
</tbody>

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;@