Business Hours calculation

[benny.yohannan]

Hi All,

I need a help on business hours calculation. my business hours are 07:00 AM to 04:00 PM CST. I am currently using the below formula for calulating business hours. It is excluding non business hours and weekends. However on few calculation it is thowing the business hours as "00:00:00". (end time 7/17/2011 9:31:00 PM - start time 7/16/2011 6:51:00 AM)

Please assist me in fixing this issue. Your assistance is much appreciated.

=(NETWORKDAYS(E2,X2)-2)*("16:00"-"07:00")+IF(NETWORKDAYS(X2,X2),MEDIAN(MOD(X2,1),"16:00","07:00"),"16:00")-MEDIAN(NETWORKDAYS(E2,E2)*MOD(E2,1),"16:00","07:00")

 

[MikeWx]

The example you have provided starts and ends in the same weekend. Since weekends are not business hours, what result did you want it to calculate instead of zero?

 

[MrVillareal]

Hi friend

You can try this one:

Excel Workbook
	A	B	C	D
1	Date	Start	End	# of Hours
2	Sun, August 07, 2011	7:00 AM	4:00 PM	0
3	Mon, August 08, 2011	7:00 AM	4:00 PM	8
4	Tue, August 09, 2011	holiday		0
5	Wed, August 10, 2011	7:00 AM	4:00 PM	8
6	Thu, August 11, 2011	7:00 AM	4:00 PM	8
7	Fri, August 12, 2011	no work		0
8	Sat, August 13, 2011	7:00 AM	4:00 PM	0
Sheet24

Excel 2010

Cell Formulas
Range	Formula
D2	=IF(WEEKDAY(A2,2)<=5,IF(ISNUMBER(B2),(C2-B2-1/24)*24,0),0)
D3	=IF(WEEKDAY(A3,2)<=5,IF(ISNUMBER(B3),(C3-B3-1/24)*24,0),0)
D4	=IF(WEEKDAY(A4,2)<=5,IF(ISNUMBER(B4),(C4-B4-1/24)*24,0),0)
D5	=IF(WEEKDAY(A5,2)<=5,IF(ISNUMBER(B5),(C5-B5-1/24)*24,0),0)
D6	=IF(WEEKDAY(A6,2)<=5,IF(ISNUMBER(B6),(C6-B6-1/24)*24,0),0)
D7	=IF(WEEKDAY(A7,2)<=5,IF(ISNUMBER(B7),(C7-B7-1/24)*24,0),0)
D8	=IF(WEEKDAY(A8,2)<=5,IF(ISNUMBER(B8),(C8-B8-1/24)*24,0),0)

Hope for your feedback

 

[MrVillareal]

Hi friend

By the way I consider 1 hour break as nonbusiness hour and that is what 1/24 stands for.

Hope for your feedback

 

[barry houdini]

=(NETWORKDAYS(E2,X2)-2)*("16:00"-"07:00")+IF(NETWORKDAYS(X2,X2),MEDIAN(MOD(X2,1),"16:00","07:00"),"16:00")-MEDIAN(NETWORKDAYS(E2,E2)*MOD(E2,1),"16:00","07:00")

That formula should work for all scenarios if you change the highlighted 2 to a 1.....but as MikeWx says your example should result in zero anyway....shouldn't it?

 

[benny.yohannan]

Hi,

Thank for your assistance on this. I now found that the reason for value 00:00 is that; these requests are approved after the business hours. Can someone assist me in fixing this issue. I need excel to capture the time if is approved outside the business hours also. Below are the few examples.

<TABLE style="WIDTH: 254pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=338><COLGROUP><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4900" width=134><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 5120" width=140><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d8d8d8; WIDTH: 101pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=17 width=134>Received Date & time</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 105pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=140>Complete Date & Time</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=64>TAT</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>7/12/11 1:07 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>7/12/11 2:37 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl71>0:00:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>7/16/11 6:51 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>7/17/11 9:31 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl71>0:00:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>7/16/11 6:35 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>7/17/11 9:22 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl71>0:00:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>7/16/11 6:47 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>7/17/11 9:28 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl71>0:00:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>7/16/11 6:32 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>7/17/11 9:19 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl71>0:00:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>7/16/11 6:43 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>7/17/11 9:26 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl71>0:00:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>7/27/11 4:39 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>7/27/11 4:43 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl71>0:00:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>7/27/11 4:43 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>7/27/11 4:47 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl71>0:00:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>7/27/11 4:47 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>7/27/11 4:50 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl71>0:00:00</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 height=18>7/27/11 4:49 PM</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>7/27/11 4:54 PM</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl74>0:00:00</TD></TR></TBODY></TABLE>

The formula which I use is =(NETWORKDAYS(D5,J5)-1)*("16:00"-"07:00")+IF(NETWORKDAYS(J5,J5),MEDIAN(MOD(J5,1),"16:00","07:00"),"16:00")-MEDIAN(NETWORKDAYS(D5,D5)*MOD(D5,1),"16:00","07:00")

Thanks again for all your responses.

Rgds,

Ben

That formula should work for all scenarios if you change the highlighted 2 to a 1.....but as MikeWx says your example should result in zero anyway....shouldn't it?

 

[benny.yohannan]

Yes, I receive the result as 00:00 for the above mentioned examples. can someone please assist me in fixing this issue.