Calculating Minutes between Date and Time Stamps only durnig working hours

[scpournara]

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.

Date & Time Received	Date & Time Order Completed		Total Minutes to Complete
7/19/2016 7:03	7/19/2016 8:47		105	Correct
7/18/2016 14:59	7/19/2016 8:26		1047	Incorrect as it calculates minutes during non working hours
7/15/2016 15:22	7/18/2016 13:55		4234	Incorrect as it calculates minutes during non working hours and weekends

<tbody>
</tbody>

 

[jorismoerings]

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
	A	B	C	D	E	F	G	H	I	J
1	Date & Time Received	Date & Time Order Completed	Formula	Total Minutes to Complete			Start	End		Holidays
2	19-7-2016 07:03	19-7-2016 08:47	104	105	Correct		07:00:00	16:00:00		1-1-2016
3	18-7-2016 14:59	19-7-2016 08:26	86	1047	Incorrect as it calculates minutes during non working hours
4	15-7-2016 15:22	18-7-2016 13:55	415	4234	Incorrect as it calculates minutes during non working hours and weekends
Sheet1

Cell Formulas
Range	Formula
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
Name	Refers To	Cells
holidays	=Sheet1!$J$2:$J$10

 

[scpournara]

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:

	A	B	C	D	E	F	G	H	I	J
1	Date & Time Received	Date & Time Order Completed	Formula	Total Minutes to Complete			Start	End		Holidays
2	19-7-2016 07:03	19-7-2016 08:47	104	105	Correct		07:00:00	16:00:00		1-1-2016
3	18-7-2016 14:59	19-7-2016 08:26	86	1047	Incorrect as it calculates minutes during non working hours
4	15-7-2016 15:22	18-7-2016 13:55	415	4234	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>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula 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) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To holidays =Sheet1!$J$2:$J$10 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[scpournara]

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:

	A	B	C	D	E	F	G	H	I	J
1	Date & Time Received	Date & Time Order Completed	Formula	Total Minutes to Complete			Start	End		Holidays
2	19-7-2016 07:03	19-7-2016 08:47	104	105	Correct		07:00:00	16:00:00		1-1-2016
3	18-7-2016 14:59	19-7-2016 08:26	86	1047	Incorrect as it calculates minutes during non working hours
4	15-7-2016 15:22	18-7-2016 13:55	415	4234	Incorrect as it calculates minutes during non working hours and weekends

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula 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) <tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To holidays =Sheet1!$J$2:$J$10 <tbody> </tbody>

<tbody>
</tbody>

 

[scpournara]

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!

 

[jorismoerings]

Hi,

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

Excel 2013 32 bit
	A	B	C	D	E	F	G	H	I	J
1	Date & Time Received	Date & Time Order Completed	Formula	Total Minutes to Complete			Start	End		Holidays
2	19-7-2016 07:03	19-7-2016 08:47	104	105	Correct		07:00:00	16:00:00		1-1-2016
3	18-7-2016 14:59	19-7-2016 08:26	147	1047	Incorrect as it calculates minutes during non working hours
4	15-7-2016 15:22	18-7-2016 13:55	453	4234	Incorrect as it calculates minutes during non working hours and weekends
Sheet1

Cell Formulas
Range	Formula
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
Name	Refers To	Cells
holidays	=Sheet1!$J$2:$J$7