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

realjam

New Member
Joined
Jul 26, 2019
Messages
4
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.

ABC
START TIME/DATEEND TIME/DATETIME DIFFERENCE
6/21/2019 10:15

<tbody>
</tbody>
6/21/2019 12:40

<tbody>
</tbody>
2:25

<tbody>
</tbody>
6/14/2019 20:32

<tbody>
</tbody>
6/17/2019 10:28

<tbody>
</tbody>
1:28

<tbody>
</tbody>

<tbody>
</tbody>


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!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Your first sentence says weekdays then you want weekends. Which one is it?
 
Upvote 0
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")
 
Upvote 0
Alternatively, given that your dates are in cells A2 and B2, try the following formula:

Code:
=(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).
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Steve you're absolutely right! Many thanks

@realjam try this updated function:

Code:
=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)))
 
Last edited:
Upvote 0
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)))
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top