Partial Units Produced in Shift

[Robandemmy]

I am trying to build a somewhat simple report to display production by shift and by day. My problem is that the unit production times long often cross over shifts. We currently have morning and evening shifts of 12 hours (6am-6pm and 6pm to 6am). I have a table with a start time stamp and an end time stamp....say a unit is started at 4pm on dayshift and is finished at 9pm nightshift, how can I get 0.40 units assigned to M shift and 0.60 units assigned to E shift?​

​

Machine	Start Time	End Time	Unit Time (minutes) (End-Start)*1440
M03	Mar-25 23:48:00	Mar-26 8:07:00	499.00
M03	Mar-25 14:50:00	Mar-25 23:05:00	495.00
M03	Mar-25 5:39:00	Mar-25 13:56:00	497.00
M03	Mar-24 20:13:00	Mar-25 4:33:00	500.00
M03	Mar-24 11:07:00	Mar-24 19:23:00	496.00

My extract looks pretty much like this...I add the Unit Time column. Is there a way sum the production for the 6AM and 6PM shifts?

Thanks,

Rob

 

[Robandemmy]

Because the unit times are in the 8hr range...for each 12 hour shift, there is a partial unit completed that was started the previous shift + the full unit produced + the partial unit completed up until shift change

 

[Robandemmy]

I'm thinking that I need to break it out into multiple columns...

Produced 100% on M Shift	Produced 100% on E Shift	Produced M Shift but Started on E Shift	Produced M Shift but Ended on E Shift	Produced E Shift but Started on M Shift	Produced E Shift but Ended on M Shift

The first two columns are pretty straight forward, however, I get spun around in circles with the IF AND/ORs especially when a unit starts on one side of midnight E shift and finishes after midnight on M shift.