Calculate working hours between two dates excluding weekends

[micky666]

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

 

[Stokers]

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

 

[barry houdini]

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?

 

[Stokers]

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?

 

[barry houdini]

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?

 

[thecrucible]

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)

Start_Time	End_Time	Results
2/6/2015 7:31:31 PM	2/9/2015 8:47:26 AM	-0.113947106 <tbody> </tbody>

<tbody>
</tbody>

Grateful for any help.

Thanks,
Gavin

 

[barry houdini]

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

 

[thecrucible]

Thank you very Barry!!!!! That did the trick!