Formula to distinguish 'day shift hours' from 'night shift hours'

[jana007]

Hi everyone,

I'm trying to calculate the exact day shift hours and the exact night shift hours.
The day hours are from 9:00 to 18:00 and the night shift hours are from 18:00 to 9:00.
I just can't get any formula to do what I need it to do.
I would really appreciate any help on this, I'm going nuts here!

 

[Ezguy4u]

Just to get the ball rolling. Now your example might have some typos. At least double check your numbers against mine. Now Cell Formatting is the key. Columns A & B is

Column C is

20-09-28 date 1.xlsx
	A	B	C
1	Entrance	Exit
2	06/09/2020 18:51	06/10/2020 02:53	08:02
3	06/09/2020 19:26	06/10/2020 04:53	09:27
4	04/09/2020 18:15	06/09/2020 20:00	1465:45
5	06/09/2020 11:02	06/09/2020 19:10	08:08
6	05/09/2020 18:19	05/09/2020 23:41	05:22
7	05/09/2020 20:07	05/09/2020 23:05	02:58
8	05/09/2020 18:15	05/09/2020 20:00	01:45
9	05/09/2020 19:01	05/09/2020 21:20	02:19
10	05/09/2020 15:01	05/09/2020 18:27	03:26
11	04/09/2020 17:34	04/10/2020 03:09	09:35
Time

Cell Formulas
Range	Formula
C2:C11	C2	=B2-A2

 

[Sulprobil]

I suggest to apply sbTimeDiff once for day shift hours (9:00 - 18:00) and twice for night shift hours (0:00 - 9:00, 18:00 - 24:00):


Another approach might be to subtract 9 hours from both start date time and end date time and count the hours < 9:00 vs > 9:00 per day. A bit error prone and maintenance unfriendly...