Time spent calculation: exclude non-business hours and weekends

E

ebrandonwhite

New Member
Joined
Jun 11, 2010
Messages
6
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!
 
Sort by date Sort by votes
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
 
Upvote 0

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
barry houdini said:
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
Click to expand...


<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!:confused:
<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>
 
Upvote 0
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
 
Upvote 0
barry houdini said:
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
Click to expand...

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-com:eek:ffice:eek:ffice" /><o:p>(y)</o:p>
<o:p>Cheers!</o:p>
<o:p>Mohit</o:p>
 
Upvote 0
Hi Barry,

Need your :rolleyes:




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.




StartDTWaitingDTEndDTStartTimeStopTimeTimeSpent
1/23/2012 12:301/26/2012 15:4513:3022:3029:15:00
1/23/2012 12:301/24/2012 15:451/26/2012 15:4513:3022:30(Expected value = 11:15:00)

<tbody>
</tbody>


Many Thanks,
Anand





mohit360 said:
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-com:eek:ffice:eek:ffice" /--><o:p></o:p>
<o:p>Cheers!</o:p>
<o:p>Mohit</o:p>
Click to expand...
 
Upvote 0
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
 
Upvote 0
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

StartDTEndDTStartTime StopTimeTimeSpent

<tbody>
</tbody>
21:5:309:31:128:00:0016:30:00

<tbody>
</tbody>
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
 
Upvote 0
barry houdini said:
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)
Click to expand...

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! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Using IFS to calculate a task due by date but needing to remove weekends
Replies
3
Views
147
awoohaw
awoohaw
C
  • Question
Need a formula for Business Hours Mon-Fri 8AM - 5PM between two dates and times!!
Replies
3
Views
109
etaf
etaf
Asbestos_Jen
  • Question
Calculate end date and time excluding non-work hours
Replies
2
Views
416
Asbestos_Jen
Asbestos_Jen
S
  • Question
VBA for calculating hours
Replies
1
Views
250
PeteWright
PeteWright
J
  • Question
Formula for calculating business hours between 2 date/times
Replies
1
Views
1K
vladimiratanasiu
V

Forum statistics

Threads
1,215,824
Messages
6,127,104
Members
449,358
Latest member
Snowinx

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top