Help Calculating Difference Between Dates But only Counting times within certain hours

[Tlund]

Hello,

I need to calculate the hours and minutes we respond to something but only counting the hours and minutes that fall within 08:00 to 17:00 Monday through Friday.

Example - if we received a call at 09:00 06/01/11 but did not arrive until 10:00 6/2/11 hour 'covered' hours are only from 08:00 to 17:00 so our response would be 11 hours.

Is there a way to put this into a formula? Also, there is no coverage on Saturday or Sunday so they would need to excluded completely.

the information is in 2 different cells

<TABLE style="WIDTH: 193pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=257 x:str><COLGROUP><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 4681" width=128><TBODY><TR style="HEIGHT: 39pt; mso-height-source: userset" height=52><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 97pt; HEIGHT: 39pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=52 width=129>Created</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 96pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=128>Onsite Arrival</TD></TR><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 25.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=34 x:num="40632.707638888889">30-Mar-11 16:59</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 x:num="40633.423611111109">31-Mar-11 10:10</TD></TR></TBODY></TABLE>

 

[Greg Truby]

Yup. I believe NETWORKDAYS() was not folded in to the core function list until 2007. Under 2003, go to Tools | Add-Ins... and in the dialog box look for the Analysis Toolpak add-in and check the checkbox (this will be necessary for anyone that uses this workbook).

And be aware that my formula is for row #2, not row #1.

Does that fix the problem?

 

[barry houdini]

Hello Greg,

You seem to be making the assumption that the start time could be any time/date but that the end time would be within working hours. If that's the case then you can use this formula

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+MOD(B2,1)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"8:00","17:00")

If start and finish times/dates are always within working hours then this would suffice

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+MOD(B2,1)-MOD(A2,1)

....or the same without NETWORKDAYS

=(SUM(INT((WEEKDAY(A2-{2,3,4,5,6})+B2-A2)/7))-1)*("17:00"-"8:00")+MOD(B2,1)-MOD(A2,1)

...or to accommodate any start or end time/date

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"8:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"8:00","17:00")

In all cases custom format result cell as [h]:mm

 

[Greg Truby]

Hiya, Barry! I was kinda hopin' you'd spot this one and show off some wizardry. I like the MOD(x,1) bit. I've jotted these down for further study. Thanks!

And yes, I was running on the assumption that the inbound call time might be logged automatically by software or something, if coming off the internet and therefore might occur at any time. Whereas the text of the original post implied that the end times would only happen during working hours.

 

[jbolt]

Mr. Excel,

I am trying calculate the number of hours and minutes, actual not rounded, that are worked which fall in the hours of 10:00 PM to 7:00 AM. I have 4 columns, the first has the employee's name, the second has the date, the third has the punch in time, and the fourth has the punch out time. Please advise.