Calculate only working hours between two dates excluding weekends

[Spoorthi]

How should I calculate working hours between two dates? Say if start at 9/25/2009 7:26:13 PM and finish at 10/20/2009 9:46:13 AM, the function should return 245:20:00 because the working hours are from 8am to 11 PM (8 - 23), and there are weekends between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates. And also how can I exclude holidays if there are any.

 

[Analyst1982]

You need to format the result cell as [h]:mm:ss (note the square brackets) otherwise totals of 24 hours or more won't display correctly. I assume E2 is 9:00:00 AM and F2 is 6:00:00 PM

In your example you show start and end times which are not within the 9 - 6 period. If those are real examples then to get a correct calculation you need a different formula, i.e.

=(NETWORKDAYS(H6,N6)-1)*($F$2-$E$2)+IF(NETWORKDAYS(N6,N6),MEDIAN(MOD(N6,1),$F$2,$E$2),$F$2)-MEDIAN(NETWORKDAYS(H6,H6)*MOD(H6,1),$F$2,$E$2)

Dear Friends,

I would like to know 2 things in this formula:
1. why there is a minus sign in beginning i.e. (NETWORKDAYS(H6,N6)-1)
2. Why we are using Median and MOD functions

please help me to understand the formula, I am not that much expert user

 

[jord0013]

It's difficult to use NETWORKDAYS because that won't count Saturdays.

If you have start time/date in A2 and end time/date in B2.....and holiday dates listed in H1:H10 then this formula will total all the working hours between those 2, given MF hours of 07:00 until 16:00 and Saturday hours of 08:00 until 12:00

=(SUMPRODUCT(INT((WEEKDAY(A2-{2,3,4,5,6,7})+INT(B2)-INT(A2))/7),{9,9,9,9,9,4})-SUMPRODUCT((H$1:H$10>A2)*(H$1:H$10< B2),LOOKUP(WEEKDAY(H$1:H$10),{1,7},{9,4}))+(MOD(B2,1)-MOD(A2,1))*24+IF(WEEKDAY(A2)=7,8,7)-IF(WEEKDAY(B2)=7,12,16))/24
<!--?XML:NAMESPACE PREFIX = B2),LOOKUP(WEEKDAY(H$1 /--><b2),lookup(weekday(h$1:h$10),{1,7},{9,4}))+(mod(b2,1)-mod(a2,1))*24+if(weekday(a2)=7,8,7)-if(weekday(b2)=7,12,16)) p="" 24<="">
format result cell as [h]:mm

If you want to make it more generic then you could set up a table which lists respective start/end times for Sunday to Saturday and then reference that....so if W2:W8 contains the numbers 1 to 7, X2:X8 shows start times Sun to Sat, Y2:Y8 shows the end times (if it's a non working day then start and end should be blank or zero) and Z2 has the formula

=Y2-X2

copied down to show the daily hours.....then the formula would become

=SUMPRODUCT(INT((WEEKDAY(A2-W$2:W$8)+INT(B2)-INT(A2))/7),Z$2:Z$8)-SUMPRODUCT((H$1:H$10>A2)*(H$1:H$10< B2),LOOKUP(WEEKDAY(H$1:H$10),W$2:Z$8))+MOD(B2,1)-MOD(A2,1)+LOOKUP(WEEKDAY(A2),W$2:X$8)-LOOKUP(WEEKDAY(B2),W$2:Y$8)
</b2),lookup(weekday(h$1:h$10),{1,7},{9,4}))+(mod(b2,1)-mod(a2,1))*24+if(weekday(a2)=7,8,7)-if(weekday(b2)=7,12,16))>

Barry this formula is fantastic. I am trying to tweak it so it fits my business requirement.

I don not need the holiday element and just need it working so that it counts within these parameters:

Monday - Friday 8am - 8pm
Saturday and Sunday 9am - 5:30pm

I have tried and failed to tweak your sumproduct formula, but if you could come up with a solution for me then I would forever in your debt.

Many thanks in advanced,

Jordan

 

[James006]

Hi,

If you were just to replicate Barry's formula ... without the holiday element ... for which you need to drop the H1:H10 portion ... you could adapt the following :

=SUMPRODUCT(INT((WEEKDAY(A2-W$2:W$8)+INT(B2)-INT(A2))/7),Z$2:Z$8)+MOD(B2,1)-MOD(A2,1)+LOOKUP(WEEKDAY(A2),W$2:X$8)-LOOKUP(WEEKDAY(B2),W$2:Y$8)

HTH

 

[greyfort]

Hello, I am using this formula:

=NETWORKDAYS(H2,M2)-1*("20:00"-"7:00")+MOD(M2,1)-MOD(H2,1) and it seems to be working well except its off:

H2 = 2/8/2016 9:14:38 AM
M2 =[TABLE="width: 140"]
<tbody>[TR]
[TD="class: xl63, width: 140, align: right"]02/08/16 11:03:37[/TD]
[/TR]
</tbody>[/TABLE]
AM

And the result I get is = [TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108, align: right"]12:48:59


[/TD]
[/TR]
</tbody>[/TABLE]
Everything is 11 hours off. What have I misunderstood from the formula?

 

[khagag]

Re: whant to split overtime from working hours

using Excel 2013 i want a formula that can calculate the SLA timing between two dates and show the result with hours & minutes
please note that SLA Timing is 4 hours , and working hours is 8 hours ( from 8 AM to 4 PM ) also ( Friday & Saturday ) is a holiday not be counted
here is an EX of two dates

[TABLE="width: 240"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Start date [/TD]
[TD]End date [/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2017 18:38[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2017 18:52[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2017 19:10[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2017 19:23[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2017 19:40[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2017 20:06[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2017 20:06[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2017 20:06[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2017 20:06[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2017 20:07[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2017 20:07[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]06/01/2017 1:58[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]06/01/2017 1:58[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]06/01/2017 1:58[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]06/01/2017 1:59[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]06/01/2017 11:37[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]06/01/2017 12:33[/TD]
[TD="align: right"]07/01/2017 17:43[/TD]
[/TR]
[TR]
[TD="align: right"]06/01/2017 12:42[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]06/01/2017 12:43[/TD]
[TD="align: right"]08/01/2017 11:43[/TD]
[/TR]
[TR]
[TD="align: right"]06/01/2017 13:08[/TD]
[TD="align: right"]07/01/2017 11:48[/TD]
[/TR]
</tbody>[/TABLE]

 

[romeo0320]

I am searching for a formula to calculate time for workdays M-F with work hours 8am-8pm. Please assist if at all possible.

thanks

 

[James006]

Hi,

You can use Barry's generic formula (post # 7)


=(NETWORKDAYS(A2,B2,holidays)-1)*(J$3-J$2)+MOD(B2,1)-MOD(A2,1)

A2 = start time/date
B2 = end time/date
J2 = MF start time, e.g. 08:00
J3 = MF end time e.g. 18:30
holidays = named range containing holiday dates

HTH

 

[Le_Me30]

Hello barry and everyone!

i was wondering if anyone is around and could help me out on small question regarding this formula provided by Barry
So i have been using this formula
=SUMPRODUCT(INT((WEEKDAY(B6-W$2:W$8)+INT(C6)-INT(B6))/7),Z$2:Z$8)-SUMPRODUCT((J$2:J$10>B6)*(J$2:J$10< C6),LOOKUP(WEEKDAY(J$2:J$10),W$2:Z$8))+MOD(C6,1)-MOD(B6,1)+LOOKUP(WEEKDAY(B6),W$2:X$8)-LOOKUP(WEEKDAY(C6),W$2:Y$8)

It works fine and everything but i was wondering how should i change it so it excludes outside working hours?
For example i have Start time 9am and end time 9pm and for example the following set up now counts this like that [TABLE="width: 393"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Start Date / Time[/TD]
[TD] End Date / Time[/TD]
[TD] Total Time[/TD]
[/TR]
[TR]
[TD="align: right"]03-09-18 6:43[/TD]
[TD="align: right"]03-09-18 7:45[/TD]
[TD="align: right"]1:01:38[/TD]
[/TR]
</tbody>[/TABLE]
Initially i would expect this time to show as 00:00:00 because it is outside working hours.

Here is example formula i'm using for Monday to Friday with start time 9am and end time 5pm.
=(NETWORKDAYS(OpenDate_time,ClosedDate_time,Holidays)-1)*(END_TIME-START_TIME)+IF(NETWORKDAYS(ClosedDate_time, ClosedDate_time),MEDIAN(MOD(ClosedDate_time,1),END_TIME,START_TIME),END_TIME)-MEDIAN(NETWORKDAYS(OpenDate_time, OpenDate_time)*MOD(OpenDate_time,1),END_TIME,START_TIME)

Maybe someone could advise me how should i adjust either formula with end criteria to be as follows:
Monday to Friday - 9pm - 9am
Saturday 9am -2pm
Sunday day off
Holiday list
And no outside working hours counted.

Thank you in advance.

 

[drewjacobs83]

Barry and others! I need your help.

I'm trying to create a formula that will allow me to track turn around time in business days. I have entered in date and time received. I receive and complete items both during and after business hours and on weekends and holidays but only want the formula to reflect working hour time. Our working hour time is from 8:00am to 5:00pm. I have seen a number of formulas, but for some reason they do not seem to calculate correctly when I receive something after hours and/or on a holiday. Any help would be much appreciated.

 

[gbyron]

Yes, it will count hours between the two "timestamps" but including only the defined period on Mondays to Fridays, but excluding weekends and/or any holidays defined.

As previously stated it's only guaranteed to give correct results if the start/end times/dates are within working hours.

If the start or end could be at weekends or evenings, for instance, then you can still calculate the hours but you need a more complicated version of that formula.......

Hello how do I get this complicated formula, I would like to add Saturday as a working day
I would also like to get two different time for a team that works in shifts,