Calculating Minutes between Date and Time Stamps only durnig working hours

S

scpournara

New Member
Joined
Aug 24, 2014
Messages
45
I am trying to calculate the amount of minutes it takes our company to complete an Order. We have captured the Date and Time Received and the Date & Time Completed.

I would like to get the number of minutes it takes to complete the Order during normal office hour from 7:00 AM PST to 4:00PM PST.

If I do a simple Date and Time Completed minus Date and Time Complete, it takes into account evenings, weekends and holiday. I want to eliminate this time.

I would like to have on formula and reference a Holiday Table and a Office Hours Table.

I have a sample xlsx file I could send with sample Date & Time Stamps.

[TABLE="width: 1161"]
<tbody>[TR]
[TD]Date & Time Received[/TD]
[TD]Date & Time Order Completed[/TD]
[TD][/TD]
[TD]Total Minutes to Complete[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/19/2016 7:03[/TD]
[TD]7/19/2016 8:47[/TD]
[TD][/TD]
[TD]105[/TD]
[TD]Correct[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/18/2016 14:59[/TD]
[TD]7/19/2016 8:26[/TD]
[TD][/TD]
[TD]1047[/TD]
[TD="colspan: 5"]Incorrect as it calculates minutes during non working hours[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/15/2016 15:22[/TD]
[TD]7/18/2016 13:55[/TD]
[TD][/TD]
[TD]4234[/TD]
[TD="colspan: 6"]Incorrect as it calculates minutes during non working hours and weekends[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Hi,

I couldn't match your correct answer on the first line of the example. Mathematically i can't see how 7 minus 3 ever results in an outcome with 5.
However take a look if this is what your after:


Book1
ABCDEFGHIJ
1Date & Time ReceivedDate & Time Order CompletedFormulaTotal Minutes to CompleteStartEndHolidays
219-7-2016 07:0319-7-2016 08:47104105Correct07:00:0016:00:001-1-2016
318-7-2016 14:5919-7-2016 08:26861047Incorrect as it calculates minutes during non working hours
415-7-2016 15:2218-7-2016 13:554154234Incorrect as it calculates minutes during non working hours and weekends
Sheet1
Cell Formulas
RangeFormula
C2=(NETWORKDAYS(A2,B2,holidays)-1)*($H$2-$G$2)+IF(NETWORKDAYS(B2,B2,holidays),MEDIAN(MOD(B2,1),$H$2,$G$2),$H$2)-MEDIAN(NETWORKDAYS(A2,B2,holidays)*MOD(A2,1),$H$2,$G$2)
C3=(NETWORKDAYS(A3,B3,holidays)-1)*($H$2-$G$2)+IF(NETWORKDAYS(B3,B3,holidays),MEDIAN(MOD(B3,1),$H$2,$G$2),$H$2)-MEDIAN(NETWORKDAYS(A3,B3,holidays)*MOD(A3,1),$H$2,$G$2)
C4=(NETWORKDAYS(A4,B4,holidays)-1)*($H$2-$G$2)+IF(NETWORKDAYS(B4,B4,holidays),MEDIAN(MOD(B4,1),$H$2,$G$2),$H$2)-MEDIAN(NETWORKDAYS(A4,B4,holidays)*MOD(A4,1),$H$2,$G$2)
Named Ranges
NameRefers ToCells
holidays=Sheet1!$J$2:$J$10
 
Upvote 0
jorismoerings said:
Hi,

I couldn't match your correct answer on the first line of the example. Mathematically i can't see how 7 minus 3 ever results in an outcome with 5.
However take a look if this is what your after:

ABCDEFGHIJ
Date & Time ReceivedDate & Time Order CompletedFormulaTotal Minutes to CompleteStartEndHolidays
Correct
Incorrect as it calculates minutes during non working hours
Incorrect as it calculates minutes during non working hours and weekends

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]19-7-2016 07:03[/TD]
[TD="align: right"]19-7-2016 08:47[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]105[/TD]

[TD="align: right"][/TD]
[TD="align: right"]07:00:00[/TD]
[TD="align: right"]16:00:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1-1-2016[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]18-7-2016 14:59[/TD]
[TD="align: right"]19-7-2016 08:26[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]1047[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]15-7-2016 15:22[/TD]
[TD="align: right"]18-7-2016 13:55[/TD]
[TD="align: right"]415[/TD]
[TD="align: right"]4234[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=(NETWORKDAYS(A2,B2,holidays)-1)*($H$2-$G$2)+IF(NETWORKDAYS(B2,B2,holidays),MEDIAN(MOD(B2,1),$H$2,$G$2),$H$2)-MEDIAN(NETWORKDAYS(A2,B2,holidays)*MOD(A2,1),$H$2,$G$2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]=(NETWORKDAYS(A3,B3,holidays)-1)*($H$2-$G$2)+IF(NETWORKDAYS(B3,B3,holidays),MEDIAN(MOD(B3,1),$H$2,$G$2),$H$2)-MEDIAN(NETWORKDAYS(A3,B3,holidays)*MOD(A3,1),$H$2,$G$2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]=(NETWORKDAYS(A4,B4,holidays)-1)*($H$2-$G$2)+IF(NETWORKDAYS(B4,B4,holidays),MEDIAN(MOD(B4,1),$H$2,$G$2),$H$2)-MEDIAN(NETWORKDAYS(A4,B4,holidays)*MOD(A4,1),$H$2,$G$2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]holidays[/TH]
[TD="align: left"]=Sheet1!$J$2:$J$10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
jorismoerings said:
Hi,

I couldn't match your correct answer on the first line of the example. Mathematically i can't see how 7 minus 3 ever results in an outcome with 5.
However take a look if this is what your after:

ABCDEFGHIJ
Date & Time ReceivedDate & Time Order CompletedFormulaTotal Minutes to CompleteStartEndHolidays
Correct
Incorrect as it calculates minutes during non working hours
Incorrect as it calculates minutes during non working hours and weekends

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]19-7-2016 07:03[/TD]
[TD="align: right"]19-7-2016 08:47[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]105[/TD]

[TD="align: right"][/TD]
[TD="align: right"]07:00:00[/TD]
[TD="align: right"]16:00:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1-1-2016[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]18-7-2016 14:59[/TD]
[TD="align: right"]19-7-2016 08:26[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]1047[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]15-7-2016 15:22[/TD]
[TD="align: right"]18-7-2016 13:55[/TD]
[TD="align: right"]415[/TD]
[TD="align: right"]4234[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=(NETWORKDAYS(A2,B2,holidays)-1)*($H$2-$G$2)+IF(NETWORKDAYS(B2,B2,holidays),MEDIAN(MOD(B2,1),$H$2,$G$2),$H$2)-MEDIAN(NETWORKDAYS(A2,B2,holidays)*MOD(A2,1),$H$2,$G$2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]=(NETWORKDAYS(A3,B3,holidays)-1)*($H$2-$G$2)+IF(NETWORKDAYS(B3,B3,holidays),MEDIAN(MOD(B3,1),$H$2,$G$2),$H$2)-MEDIAN(NETWORKDAYS(A3,B3,holidays)*MOD(A3,1),$H$2,$G$2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]=(NETWORKDAYS(A4,B4,holidays)-1)*($H$2-$G$2)+IF(NETWORKDAYS(B4,B4,holidays),MEDIAN(MOD(B4,1),$H$2,$G$2),$H$2)-MEDIAN(NETWORKDAYS(A4,B4,holidays)*MOD(A4,1),$H$2,$G$2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]holidays[/TH]
[TD="align: left"]=Sheet1!$J$2:$J$10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Thanks for the help. Copy/Paste formula worked well, however, the value returned in cell C2 was expressed in HH:MM, 1:04. Can you help me with changing it to minutes, 104

Also, in examples 2 and three, the formula only seems to be calculating the minutes to completion on the second day and is not adding in the first day. Can you take another look?

Thanks again for the great help!
 
Upvote 0
Hi,

change the formula to this and format the cell with minutes as a number.


Excel 2013 32 bit
ABCDEFGHIJ
1Date & Time ReceivedDate & Time Order CompletedFormulaTotal Minutes to CompleteStartEndHolidays
219-7-2016 07:0319-7-2016 08:47104105Correct07:00:0016:00:001-1-2016
318-7-2016 14:5919-7-2016 08:261471047Incorrect as it calculates minutes during non working hours
415-7-2016 15:2218-7-2016 13:554534234Incorrect as it calculates minutes during non working hours and weekends
Sheet1
Cell Formulas
RangeFormula
C2=((NETWORKDAYS(A2,B2,holidays)-1)*($H$2-$G$2)+IF(NETWORKDAYS(B2,B2,holidays),MEDIAN(MOD(B2,1),$H$2,$G$2),$H$2)-MEDIAN(NETWORKDAYS(A2,A2,holidays)*MOD(A2,1),$H$2,$G$2))*24*60
Named Ranges
NameRefers ToCells
holidays=Sheet1!$J$2:$J$7
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Working Hours Taken to Complete a Task
Replies
6
Views
239
AhoyNC
A
R
  • Solved
Adding Hours & Minutes to Dates & Times w/in Working Hours
2
Replies
11
Views
568
Asbestos_Jen
Asbestos_Jen
W
  • Question Question
Calculate total time taken to complete task in working days (6am to 6pm)
Replies
4
Views
437
Wibab
W
J
  • Solved
Hours Between 2 Dates Excluding Non-Business Hours ONLY
Replies
7
Views
394
felixstraube
F
earthworm
  • Question Question
Calculate Time and Date between 2 Date
Replies
8
Views
447
Dave Patton
D

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

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