HELP: How to calculate time difference including weekends within time range

[realjam]

Please help. I have to calculate the machine downtime between start date and end date between 9:00-18:00, assuming that there is no holiday and includes weekdays.

What I did:

1. Calculated the time difference between START DATE/TIME (Column A) and END DATE/TIME (COLUMN B) which results to TIME DIFFERENCE (Column C) using formula (B-A)*24.

2. I have found the formula to solve Hours and Minutes within the working hours of 9AM-6PM. Using this formula
=(NETWORKDAYS(A11,B11)-1)*("18:00"-"9:00")+IF(NETWORKDAYS(B11,B11),MEDIAN(MOD(B11,1),"18:00","9:00"),"18:00")-MEDIAN(NETWORKDAYS(A11,A11)*MOD(A11,1),"18:00","9:00")


The problem is NETWORKDAYS excluded the WEEKENDS. As you can see in the table below, the time difference of June 14, 2019 20:32 and June 17 10:28 is only 1 hour and 28 minutes since June 14 is Friday past the time range of 9:00 - 18:00 and it only includes June 17, Monday 9:00 - 10:28. I need the weekends included in the result with the same time range.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]START TIME/DATE[/TD]
[TD="align: center"]END TIME/DATE[/TD]
[TD="align: center"]TIME DIFFERENCE[/TD]
[/TR]
[TR]
[TD][TABLE="width: 223"]
<tbody>[TR]
[TD="class: xl65, width: 223, align: center"]6/21/2019 10:15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 184"]
<tbody>[TR]
[TD="class: xl65, width: 184, align: center"]6/21/2019 12:40[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 368"]
<tbody>[TR]
[TD="class: xl67, width: 368, align: center"]2:25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 223"]
<tbody>[TR]
[TD="class: xl65, width: 223, align: center"]6/14/2019 20:32[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 184"]
<tbody>[TR]
[TD="class: xl65, width: 184, align: center"]6/17/2019 10:28[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 368"]
<tbody>[TR]
[TD="class: xl67, width: 368, align: center"]1:28[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


I have searched for formulas with the weekends included but haven't found any. I have 500+ entries with different dates and time in excel. It will be awesome if you could help me with this.

Thank you in Advance!

 

[JustynaMK]

Yup looks even better! + one new thing for me to learn.