Get amount of time inside and outside of two overlapping time periods

[brux2dc]

I have hit a wall figuring out how to best get this done...

Based on this:

	5. Employees Normal Work Hours	8:00:00 AM	to	4:45:00 PM
Date	Start	Stop	Total During Working Hours	Total Outside Working Hours
12/1/19	6:30:00 PM	9:30:00 PM	NEED THIS VALUE	NEED THIS VALUE
12/2/19	7:00:00 AM	12:00:00 PM	NEED THIS VALUE	NEED THIS VALUE
12/3/19	9:00:00 AM	6:00:00 PM	NEED THIS VALUE	NEED THIS VALUE
12/5/19	21:00:00 PM	1:00:00 AM	NEED THIS VALUE	NEED THIS VALUE
12/6/19	09:00:00 AM	01:00:00 PM	NEED THIS VALUE	NEED THIS VALUE

With the working hours as entered what is the number of hours in the Total during working hours and the total outside working hours for each of the Start and Stop pairs? The Working hours is variable as is the Start and Stop times. One huge wrinkle is that sometimes the stop time will roll over midnight. The hours should be presented as an integer, but not critical as I can do that part. It is the logic I am truly seeking, and correct formulas to use.

I don't have any code to share as I would start down a path and quickly see it was grossly wrong in logic.

I appreciate any help with this...

 

[lrobbo314]

Here is a way using Power Query. I made 2 named ranges sHour for the start of normal hours and eHour for the end.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	5. Employees Normal Work Hours	8:00:00 AM	to	4:45:00 PM
2
3	Date	Start	Stop	Total During Working Hours	Total Outside Working Hours		Date	Start	Stop	Total Outside Working Hours	Total Durng Working Hours
4	12/1/2019	6:30:00 PM	9:30:00 PM	NEED THIS VALUE	NEED THIS VALUE		12/1/2019 0:00	6:30:00 PM	9:30:00 PM	3	0
5	12/2/2019	7:00:00 AM	12:00:00 PM	NEED THIS VALUE	NEED THIS VALUE		12/2/2019 0:00	7:00:00 AM	12:00:00 PM	1	4
6	12/3/2019	9:00:00 AM	6:00:00 PM	NEED THIS VALUE	NEED THIS VALUE		12/3/2019 0:00	9:00:00 AM	6:00:00 PM	1.25	7.75
7	12/5/2019	21:00:00 PM	1:00:00 AM	NEED THIS VALUE	NEED THIS VALUE		12/5/2019 0:00	9:00:00 PM	1:00:00 AM	4	0
8	12/6/2019	9:00:00 AM	1:00:00 PM	NEED THIS VALUE	NEED THIS VALUE		12/6/2019 0:00	9:00:00 AM	1:00:00 PM	0	4
Sheet4

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    sHour = Time.From(Excel.CurrentWorkbook(){[Name="sHour"]}[Content]{0}[Column1]),
    eHour = Time.From(Excel.CurrentWorkbook(){[Name="eHour"]}[Content]{0}[Column1]),
    WH = List.Times(sHour,Number.From(eHour-sHour)*24*4+1,#duration(0,0,15,0)),
    Types = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Start", type time}, {"Stop", type time}}),
    AllHours = Table.AddColumn(Types, "Custom", each if [Start]<[Stop] then List.Times([Start],Number.From([Stop]-[Start])*24*4+1,#duration(0,0,15,0)) else List.Times([Start],Number.From([Start]-[Stop])+16,#duration(0,0,15,0))),
    Outside = Table.AddColumn(AllHours, "Outside WH", each List.Difference([Custom],WH)),
    OutsideTotal = Table.AddColumn(Outside, "Total Outside Working Hours", each if List.Count([Custom]) = List.Count([Outside WH]) then (List.Count([Outside WH])-1) /4 else List.Count([Outside WH])/4),
    During = Table.AddColumn(OutsideTotal, "DWH", each List.Difference([Custom], [Outside WH])),
    DuringTotal = Table.AddColumn(During, "Total Durng Working Hours", each if List.Count([DWH]) = 0 then 0 else (List.Count([DWH])-1)/4),
    Select = Table.SelectColumns(DuringTotal,{"Date", "Start", "Stop", "Total Outside Working Hours", "Total Durng Working Hours"})
in
    Select

 

[brux2dc]

I really appreciate the answer.

Unfortunately, I don't know how to use power query in Excel (just PowerBI). The way I presented the data in the question was for clarity. The real data is on a form that has those start and stop pairs scattered, all using the shift start stop as reference. The goal is to determine the number of hours the travel occurred inside the sift and outside the shift. I already setup the Usual Work Day flag which will be used to determine if the math needs to be done for inside shift hours.

Is there a way to do this using a traditional formula, or dynamically creating lookups or ???

5. Employees Normal Work Hours	8:00:00 AM	to	4:45:00 PM	and Days	2	through	6
DEPART OFFICIAL DUTY STATION
Event	Date	Usual Work-Day (y/n)	Departure	Arrival	Total During Shift	Total Outside Shift
6. Arrival at Airport/Train Station	11/10/2020	Y	xxxxxxxxxxxxxxxxxxx	6:30:00 PM
7. Wait Time	11/10/2020	Y	6:30:00 PM	9:30:00 PM		3
8. Departure	11/10/2020	Y	9:30:00 PM	xxxxxxxxxxx
a. Arrival
b. Wait time +
c. Departure
d. Arrival
e. Wait time +
f. Departure
9. Final Arrive Time	11/10/2020	Y	xxxxxxxxxxxxxxxxx	11:30:00 PM		2
10. Arrival at final destination (hotel, work site etc)	11/10/2020	Y		12:30:00 AM		1
11. TOTAL HOURS (Rounded to newest 1/4 hour)						6

Thanks.