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 start time/date in A2 and end time/date in B2 try this formula for the total work hours

=(SUM(INT((WEEKDAY(A2-{2,3,4,5,6,7})+INT(B2)-INT(A2))/7))-SUMPRODUCT((Holidays>=INT(A2))*(Holidays< B2)*(WEEKDAY(Holidays)>1))-1)*(StopTime-StartTime)+MOD(B2,1)-MEDIAN((WEEKDAY(A2)>1)*(COUNTIF(Holidays,A2)=0)*MOD(A2,1),StartTime,StopTime)

format result cell as [h]:mm

 

[mohit360]

Assuming start time/date in A2 and end time/date in B2 try this formula for the total work hours

=(SUM(INT((WEEKDAY(A2-{2,3,4,5,6,7})+INT(B2)-INT(A2))/7))-SUMPRODUCT((Holidays>=INT(A2))*(Holidays< B2)*(WEEKDAY(Holidays)>1))-1)*(StopTime-StartTime)+MOD(B2,1)-MEDIAN((WEEKDAY(A2)>1)*(COUNTIF(Holidays,A2)=0)*MOD(A2,1),StartTime,StopTime)

format result cell as [h]:mm

<TABLE style="WIDTH: 282pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=374><COLGROUP><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" span=2 width=74><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #00b0f0; WIDTH: 78pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=104>Srt Date& Time</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00b0f0; WIDTH: 92pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_3109313 class=xl66 width=122>Close Date& Time</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 56pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=74>Shift Start</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 56pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=74>Shift Close</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=xl68 height=17 align=right>3/23/2012 5:00</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=xl63 align=right>3/30/12 13:00</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=xl64 align=right>8:00:00 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=xl64 align=right>8:00:00 PM</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=xl68 height=17 align=right>3/27/2012 17:20</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=xl63 align=right>3/30/12 13:44</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=xl64 align=right>8:00:00 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=xl64 align=right>8:00:00 PM</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=xl68 height=17 align=right>3/27/2012 21:00</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=xl63 align=right>3/29/12 14:44</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=xl64 align=right>8:00:00 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=xl64 align=right>8:00:00 PM</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" id=td_post_3109313 class=xl68 height=17 align=right>3/24/2012 5:00</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=xl63 align=right>3/25/12 12:44</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=xl64 align=right>8:00:00 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=xl64 align=right>8:00:00 PM</TD></TR>Thanks Barry.....
<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=xl68 height=17 align=right>3/24/2012 10:00</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=xl63 align=right>3/26/12 12:44</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=xl64 align=right>8:00:00 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=xl64 align=right>8:00:00 PM</TD></TR>Pls find the Sample below.... It is calculating the Working Hours, but not calculating actual working hrs.... If u see the first record start date is 23 Mar'12 & End Date is 30 Mar'12...... So 23rd was "Monday" & 30th was "Monday" & 25th Mar'12 was "SUNDAY" then, total working days are 23,24,26,27,28,29,30.....
Now we received mail on 23rd 5AM & our shift timings are 8AM to *PM (12 Hrs Shift) so
TAT would be >>>
6(working Days)*12 = 72 Hrs...Equation 1
on 30th Mar'12 closed at 1:00PM so 13:00 - 8:00 = 5hrs...... Equation 2
TAT=72hrs + 5hrs i.e. "77hrs"
Pls Help!
<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=xl68 height=17 align=right>3/27/2012 17:20</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=xl63 align=right>3/29/12 17:04</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=xl64 align=right>8:00:00 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=xl64 align=right>8:00:00 PM</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=xl68 height=17 align=right>3/27/2012 17:20</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=xl63 align=right>3/29/12 12:44</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=xl64 align=right>8:00:00 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=xl64 align=right>8:00:00 PM</TD></TR></TBODY></TABLE>

 

[barry houdini]

I get that result - 77:00 - using your example with my suggested formula

What result did you get? Make sure you format result cell as [h]:mm (with square brackets) otherwise it doesn't correctly display values over 24 hours

 

[mohit360]

I get that result - 77:00 - using your example with my suggested formula

What result did you get? Make sure you format result cell as [h]:mm (with square brackets) otherwise it doesn't correctly display values over 24 hours

Hi Barry,

You are correct!.... This is exactly what I was looking for..
Thanks a lot u r Genius
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o>Cheers!</o>
<o>Mohit</o>

 

[reachanan]

Hi Barry,

Need your




I have used the following formula of yours to calculate the timespent. But I have a new condition 'waitingtime' which is to be excluded from the timespent if there a value in that column.

=(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)

As seen below, I might have rows where the WaitinDT will be empty and with values. In case, it has values, the time from WaitingDT to EndDT is to be excluded in the final TimeSpent calculation. Can you please help me with this scenario.




[TABLE="class: grid, width: 575"]
<tbody>[TR]
[TD]StartDT[/TD]
[TD]WaitingDT[/TD]
[TD]EndDT[/TD]
[TD]StartTime[/TD]
[TD]StopTime[/TD]
[TD]TimeSpent[/TD]
[/TR]
[TR]
[TD="align: right"]1/23/2012 12:30[/TD]
[TD][/TD]
[TD="align: right"]1/26/2012 15:45[/TD]
[TD="align: right"]13:30[/TD]
[TD="align: right"]22:30[/TD]
[TD="align: right"]29:15:00[/TD]
[/TR]
[TR]
[TD="align: right"]1/23/2012 12:30[/TD]
[TD="align: right"]1/24/2012 15:45[/TD]
[TD="align: right"]1/26/2012 15:45[/TD]
[TD="align: right"]13:30[/TD]
[TD="align: right"]22:30[/TD]
[TD="align: right"](Expected value = 11:15:00)[/TD]
[/TR]
</tbody>[/TABLE]


Many Thanks,
Anand

Hi Barry,

You are correct!.... This is exactly what I was looking for..
Thanks a lot u r Genius
<!--?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /--><o></o>
<o>Cheers!</o>
<o>Mohit</o>

 

[barry houdini]

Try replacing all instances of EndDT in the formula with this

IF(WaitingDT="",EndDT,WaitingDT)

 

[reachanan]

Thanks a lot Barry ! I have got was I was expecting !!

Try replacing all instances of EndDT in the formula with this

IF(WaitingDT="",EndDT,WaitingDT)

 

[andy famela]

How if want to calculate with this condition :
1. Start time 08:00
2. End time 17:00
3. Rest time 12:00 until 13:00

So if i work on 12/19/2012 at 16.00 until 12/20/2012 at 09:00, i will have been worked for 2 hours.
If i work on 12/19/2012 at 10:00 until 12/19/2012 at 14:00, i will have been worked for 3 hours because there is a rest time at 12:00 until 13:00.

Thanks before

 

[jholar]

I am using excel 2010 and the formula =(NETWORKDAYS(C2,D2)-1)*(F2-E2)+IF(NETWORKDAYS(D2,D2),MEDIAN(MOD(D2,1),F2,E2),F2)-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1),F2,E2) gives me timespent = 0:00:00
I have these five coloumns in cells C,D,E,F,G Respectively

[TABLE="width: 624"]
<tbody>[TR]
[TD="class: xl63, width: 149"]StartDT[/TD]
[TD="class: xl63, width: 132"]EndDT[/TD]
[TD="class: xl63, width: 102"]StartTime [/TD]
[TD="class: xl63, width: 124"]StopTime[/TD]
[TD="class: xl63, width: 117"]TimeSpent[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 624"]
<tbody>[TR]
[TD="class: xl66, width: 149"]21:5:30[/TD]
[TD="class: xl66, width: 132"]9:31:12[/TD]
[TD="class: xl67, width: 102"]8:00:00[/TD]
[TD="class: xl67, width: 124"]16:30:00[/TD]
[TD="class: xl67, width: 117"][/TD]
[/TR]
</tbody>[/TABLE]
So when i try to calculate time spent using the above formula it gives me value 0:00:00
Any help would be much appreciated. thank you

 

[sticky667]

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)

Hi Barry,

This formula works great for what I need but how can I make it adjust the time for different time zones? The data is always GMT but the input is always PST.

thanks!