Counting hours spent ( within certain shift hours) between two dates.

[AhmadHamouda25]

Hello, I'm looking for a solution or equation to my problem

I have to count how many hours employees worked on cases during two shifts "day shift" 5:00-17:00, and other "night shift" (17:00-5:00).

I have no idea how to make it with one equation but I know the other would be subtraction from duration hopefully! any help please?

Raw_Data_1713471755929.xlsx
	A	B	C	D	E
1	Start Date	End Date	Duration	Hours between 5 - 17	Hours between 17 - 5
2	10/1/2023 0:19	10/6/2023 19:47	139:28		139:28
Sheet1

Cell Formulas
Range	Formula
C2	C2	=B2-A2
E2	E2	=C2-D2

 

[eiloken]

try:

night shift=INT(B2-A2)*12-IF(MOD(A2,INT(A2))>17/24,MOD(A2,INT(A2))*24-17,0)+IF(MOD(B2,INT(B2))>17/24,MOD(B2,INT(B2))*24-17,0)

,

day shift=(B2-A2)*24-night shift