Time spent calculation: exclude non-business hours and weekends

[ebrandonwhite]

Thanks in advance for any help that can be offered.

I am using Excel 2007.

Variables:
StartDT = The time the ticket came into the system
EndDT = The time the agent closed the ticket
StartTime = The time the business day starts
StopTime = The time the business day ends
TimeSpent = The calculated time spent

Business hours are 0600 - 1800.

Weekends and after hours are not included in time spent.

ex.
StartDT
4/9/2010 17:00

EndDT
4/12/2010 7:00

Scenario: A case comes in at 17:00 on Friday 4/9/2010. The case is closed at 07:00 on Monday 4/12/2010. Total time spent (calculated manually) is 2 hours.

So the columns would look like:

|StartDT|EndDT|StartTime|StopTime|TimeSpent|


So what I need is a formula for the TimeSpent column that calculates the time spent using the given information in the previous columns.

Thanks!

 

[barry houdini]

Assuming that StartDT and EndDT are always within business hours you can use this formula

=(NETWORKDAYS(StartDT,EndDT)-1)*(StopTime-StartTime)+MOD(EndDT,1)-MOD(StartDT,1)

format result cell as [h]:mm

 

[ebrandonwhite]

Assuming that StartDT and EndDT are always within business hours you can use this formula

=(NETWORKDAYS(StartDT,EndDT)-1)*(StopTime-StartTime)+MOD(EndDT,1)-MOD(StartDT,1)

format result cell as [h]:mm

Barry,
Unfortunately life isn't that easy. Customer can submit any time of any day so sometimes the start or end dates will land in the untracked hours.

Any ideas?

Thanks!

~Eric

 

[barry houdini]

OK, Eric, in that case you can use this version

=(NETWORKDAYS(StartDT,EndDT)-1)*(StopTime-StartTime)+IF(NETWORKDAYS(EndDT,EndDT),MEDIAN(MOD(EndDT,1),StopTime,StartTime),StopTime)-MEDIAN(NETWORKDAYS(StartDT,StartDT)*MOD(StartDT,1),StopTime,StartTime)

 

[ebrandonwhite]

OK, Eric, in that case you can use this version

=(NETWORKDAYS(StartDT,EndDT)-1)*(StopTime-StartTime)+IF(NETWORKDAYS(EndDT,EndDT),MEDIAN(MOD(EndDT,1),StopTime,StartTime),StopTime)-MEDIAN(NETWORKDAYS(StartDT,StartDT)*MOD(StartDT,1),StopTime,StartTime)

Barry,
So that works perfectly! Thank you so much!

I only have one problem.

Time spent displays: 142183
But the actual value is: 4/7/1900 5:43:20 PM

How do I get the answer to be just 142183?

Thanks again!

~Eric

 

[HectorD]

Thanks Barry for the tip.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have a pretty similar scenario to the previous problem. We need to include another variance; holidays.<o></o>
<o></o>
For example the scenario below mentioned we had September 6th as holiday, so we need to substract that day:<o></o>
<o></o>
<TABLE class=MsoNormalTable style="WIDTH: 388pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 0cm 0cm 0cm" cellSpacing=0 cellPadding=0 width=517 border=0><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 0cm; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 83pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=111>StartDT<o></o>
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 0cm; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 83pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=111>EndDT<o></o>
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 0cm; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 83pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=111>StartTime<o></o>
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 0cm; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 83pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=111>StopTime<o></o>
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 0cm; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 56pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=75>TimeSpent<o></o>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 0cm; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent">

03/09/2010 16:01<o></o>​

</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 0cm; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent">

07/09/2010 11:01<o></o>​

</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 0cm; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent">

08:00:00 a.m.<o></o>​

</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 0cm; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent">

05:00:00 p.m.<o></o>​

</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 0cm; BORDER-TOP: #f0f0f0; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; PADDING-TOP: 0cm; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent">

12:59<o></o>​

</TD></TR></TBODY></TABLE>
<o></o>
The manual calculation should show 03:59 hours.<o></o>
<o></o>
Do you have any idea?<o></o>
<o></o>
Thanks in advance for your help.<o></o>
Best regards,<o></o>
Hector Diaz

 

[barry houdini]

Hello HectorD, welcome to MrExcel

You can insert the holiday range into each NETWORKDAYS function, so if your start and end times will always be within business hours you can use

=(NETWORKDAYS(StartDT,EndDT,Holidays)-1)*(StopTime-StartTime)+MOD(EndDT,1)-MOD(StartDT,1)

or for any start and end times

=(NETWORKDAYS(StartDT,EndDT,Holidays)-1)*(StopTime-StartTime)+IF(NETWORKDAYS(EndDT,EndDT,Holidays),MEDIAN(MOD(EndDT,1),StopTime,StartTime),StopTime)-MEDIAN(NETWORKDAYS(StartDT,StartDT,Holidays)*MOD(StartDT,1),StopTime,StartTime)

 

[HectorD]

You are a genius!! Thank you!!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have tried, but I did not understood, who to add the holidays, so I added as a day and it worked, but what if there are a more than 1 holiday in the month? How should I write the holidays?
<o></o>
<TABLE style="WIDTH: 472pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=630 border=0><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" span=4 width=111><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=111 height=20><TABLE style="WIDTH: 453pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=604 border=0><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" span=4 width=111><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=111 height=20>StartDT</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=111>EndDT</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=111>StartTime</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=111>StopTime</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 65pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=86>Holiday</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 56pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=74>TimeSpent</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>03/09/2010 16:01</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>07/09/2010 11:01</TD><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>08:00:00 a.m.</TD><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>05:00:00 p.m.</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>06/09/2010</TD><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>03:59</TD></TR></TBODY></TABLE></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=111></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=111></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 83pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=111></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 65pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=86></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 75pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=100></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20></TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl68 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right></TD></TR></TBODY></TABLE>
Thank you,

Best regards,

 

[barry houdini]

Typically you would list all holidays for the year (or for the period that interests you, whether shorter or longer), one date per cell somewhere on the worksheet, you can then use that range directly in NETWORKDAYS function, e.g.

=NETWORKDAYS(A2,B2,H$1:H$10)

where H1:H10 contains the holiday dates.....or you can name that range holidays and then use

=NETWORKDAYS(A2,B2,holidays)

 

[Mote36]

I need to make a similar calculation as this one. The only difference is that I don't need to eliminate the weekends only hours from 3rd shift (10:30 PM to 6:00 AM)