Calculating day shift and night shift hours from date/time

[BenR]

I want to find the hours worked in day shift and night shift. Day shift is from 06:30 to 18:30 and night shift is from 18:30 to 06:30. Listed below is an example of my date/times.
Note that the night shift carries over to the next day.

Start/Finish
21/12/09 07:00 to 21/12/09 11:09
21/12/09 07:46 to 21/12/09 14:41
21/12/09 12:13 to 21/12/09 22:08
21/12/09 16:40 to 21/12/09 18:05
21/12/09 19:40 to 22/12/09 02:34
21/12/09 23:20 to 22/12/09 04:39
22/12/09 02:06 to 22/12/09 06:15

any ideas?

 

[Michael Avidan]

hi,

The formula solution is no longer available does anyone still have it?

Spacial offer for JimmyBoats,

I don't remember what I suggested in 2014(!) but I assume the following is not far from the old formula that, somehow, "vanished" from the Pictures hosting site...

The data in column "B" ,as copied from the first question in this therad.
Columns "C+D" are the result of running "Text to Clolumns" feature...

Enjoy.....................

Micky Avidan

 

[JimmyBoats]

Many Many Thanks!! You are awesome I owe u a beer!