Calculate working hours between two dates excluding weekends

micky666

New Member
Joined
Oct 6, 2014
Messages
5
working hours from 9:00-17:00. Excluding weekends. Have an Excel 2010.

Start Date 1/1/14 08:00
End Date: 3/1/14 11:00

Details above given in:
Date format: MM/DD/YY
24-hour time

Date and Time are in separate Tabs
i.e A1: 1/1/14
B1: 08:00
C1: 3/1/14
D1: 11:00
 
Hi Guys,

I'm working on the same issue, i'm trying to calculate the hours my team is spending on support tickets. So I need to only count our working hours of 9.00 - 5:30 and exclude weekends.

The dates in my report appear in the following format..

Assigned Time Resolved Date
05/01/2015 02:44:15 12/01/2015 01:03:33

So in this example I would expect from 2:44 to 5:30 to be counted, then a full day on the 6th, 7th, 8th, 9th then from 9 - 1 on the 12th.

I've tried using the following

=(NETWORKDAYS(J2,K2)-1)*("17:00"-"9:00")+IF(NETWORKDAYS(K2,K2),MEDIAN(MOD(K2,1),"17:00","9:00"),"17:00")-MEDIAN(NETWORKDAYS(J2,J2)*MOD(J2,1),"17:00","9:00")

Although for the above dates this makes the result come out to 16:00hrs when formatted to hhr:mm:ss, also if a ticket gets open and closed on the same day, the above formula shows a negative value.

Really confused :(
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hello Stokers, welcome to MrExcel

05/01/2015 02:44:15 12/01/2015 01:03:33

02:44:15 is between 2 AM and 3 AM so I'd expect that time range to be 5 full days only (the whole of the 5th, 6th, 7th, 8th and 9th). If you custom format the result cell as [h]:mm:ss you should see 40:00 (Note the square brackets around the hours).

Also if your finish time is 5:30 PM you need to change 17:00 to 17:30

I don't get any negative results, even if the start and end are the same date - is K2 always > J2? Can you give an example where you get negative results?
 
Upvote 0
That seems to work, I do get back 40.00 as you said.

I still get negative values though, or values that retun as ############### etc

Here is an example

Assigned Time Resolved Date
08/01/2015 10:42:11 08/01/2015 01:48:34

using the same formula, although from a different row

=(NETWORKDAYS(J10,K10)-1)*("17:00"-"9:00")+IF(NETWORKDAYS(K10,K10),MEDIAN(MOD(K10,1),"17:00","9:00"),"17:00")-MEDIAN(NETWORKDAYS(J10,J10)*MOD(J10,1),"17:00","9:00")

Is this because excel thinks that the resolve date is before the assigned date?
 
Upvote 0
Is this because excel thinks that the resolve date is before the assigned date?

Yes, that's right 01:48:34 is 1 AM - to be recognised as 1 PM either the PM needs to be included, like 01:48:34 PM or you would use "24 hour time", i.e. 13:48:34. How are those times generated, if it's human input then they probably ought to be input differently. Should all the times be within the 09:00 to 17:00 timeframe?
 
Upvote 0
I am getting negative numbers as well.

It is possible for both my Start_Date and End_Date to fall on weekends so I amended the original formula a bit

=(NETWORKDAYS.INTL(IF(WEEKDAY(D2,2)<=5,D2,WORKDAY(D2,1)),IF(WEEKDAY(E2,2)<=5,E2,WORKDAY(E2,1)))-1)*("16:00"-"08:00")+MOD(E2,1)-MOD(D2,1)

The above works majority of the time but seems not to handle instances where Start_Time exceeds 1 hour defined EOD window (i.e., 4pm)

Example of start and end times that return invalid results (N.B. results cell [h]:mm:ss)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Start_Time[/TD]
[TD]End_Time[/TD]
[TD]Results[/TD]
[/TR]
[TR]
[TD]2/6/2015 7:31:31 PM[/TD]
[TD]2/9/2015 8:47:26 AM[/TD]
[TD][TABLE="width: 123"]
<tbody>[TR]
[TD="width: 123, align: right"]-0.113947106[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Grateful for any help.

Thanks,
Gavin
 
Upvote 0
Hello Gavin, welcome to MrExcel,

For dates/times outside working hours you need to use a formula similar to those posted earlier in this thread. IF you use this version....

=(NETWORKDAYS(D2,E2)-1)*("16:00"-"8:00")+IF(NETWORKDAYS(E2,E2),MEDIAN(MOD(E2,1),"16:00","8:00"),"16:00")-MEDIAN(NETWORKDAYS(D2,D2)*MOD(D2,1),"16:00","8:00")

...you should get a result of 0:47:26 for your example, i.e. it only counts the time from 08:00 on the Monday to the end time at 08:47:26
 
Upvote 0

Forum statistics

Threads
1,223,920
Messages
6,175,377
Members
452,638
Latest member
Oluwabukunmi

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