How many hours fall between a day and time range

[ElBB23]

Hi all,

I have been trying to find a formula that will tell me how many hours fall between two specific date/time ranges.

So

Range One
Starts: Friday 01:00
Ends: Sunday 17:59:59


Range Two
Starts: Sunday 18:00
Ends: Friday 00:59:59


The ranges can be variable and I need it to round up to a whole hour. So if I had a time span of Sunday 16:00-23:00.

Range 1------------Range 2

2.00------------------5.00


I have been trying for a while now and am struggling with the day and time so any help would be appriciated. I'm happy to split things up or add things.

Thanks in advance.

 

[footoo]

Format A1:A6 as : dd/mm/yy hh:mm:ss
Input :
A1 - 10/08/18 01:00:00
A2 - 12/08/18 17:59:59
A3 - 12/08/18 18:00:00
A4 - 17/08/18 00:59:59
A5 - 12/08/18 16:00:00
A6 - 12/08/18 23:00:00

Format B5:B6 as : 0
Enter in B5 : =IF(A2>A5,ROUND((A2-A5)*24,0),0)
Enter in B6 : =IF(A3 < A6,ROUND((A6-A3)*24,0),0)<a6,round((a6-a3)*24,0),0)< html=""></a6,round((a6-a3)*24,0),0)<>