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!

 

[steve the fish]

Your first sentence says weekdays then you want weekends. Which one is it?

 

[realjam]

Hahah. Sorry. Including weekends.

 

[steve the fish]

There is a networkdays function that you can decide your own working days. For evey day a workday would be eg:

=NETWORKDAYS.INTL(A1,B1,"0000000")

 

[JustynaMK]

Alternatively, given that your dates are in cells A2 and B2, try the following formula:

=(DAY(B2)-DAY(A2))*0.375+IF(HOUR(B2)>18,0.75,IF(HOUR(B2)<9,0.375,TIME(HOUR(B2),MINUTE(B2),0)))-IF(HOUR(A2)>18,0.75,IF(HOUR(A2)<9,0.375,TIME(HOUR(A2),MINUTE(A2),0)))

You need to format your cells as Custom > [h]:mm to see the correct results.

So for your second example the result will be 19:28 (i.e. 0h for Friday + 9h for Saturday + 9h for Sunday + 1h 28 min for Monday).

 

[realjam]

Tried that Sir. It lets me choose the day-offs. I would like to get the downtime between 9AM-6PM everyday including day-offs and no holidays.

 

[realjam]

OMG. IT worked. Thank you so much!

 

[steve the fish]

Tried that Sir. It lets me choose the day-offs. I would like to get the downtime between 9AM-6PM everyday including day-offs and no holidays.

If your formula works then using NETWORKDAYS.INTL will work for exactly what it seems you want. Justyna you couldnt use DAY-DAY. What if start date was 30th June and end date 1st July?

 

[JustynaMK]

Steve you're absolutely right! Many thanks

@realjam try this updated function:

=INT(ROUNDUP(B2,0)-ROUNDUP(A2,0))*0.375+IF(HOUR(B2)>18,0.75,IF(HOUR(B2)<9,0.375,TIME(HOUR(B2),MINUTE(B2),0)))-IF(HOUR(A2)>18,0.75,IF(HOUR(A2)<9,0.375,TIME(HOUR(A2),MINUTE(A2),0)))

 

[steve the fish]

You could use INT and MOD:

=(INT(B2)-INT(A2))*0.375+IF(HOUR(B2)>18,0.75,IF(HOUR(B2)<9,0.375,MOD(B2,1)))-IF(HOUR(A2)>18,0.75,IF(HOUR(A2)<9,0.375,MOD(A2,1)))