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.

 

[barry houdini]

Hello DHT, welcome to MrExcel,

Which version of Excel are you using? If you have Excel 2010 or later try using NETWORKDAYS.INTL function like this

=(NETWORKDAYS.INTL(A2,B2,11,H$1:H$10)-1)*("20:00"-"8:00")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$10),MEDIAN(MOD(B2,1),"8:00","20:00"),"20:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$10)*MOD(A2,1),"8:00","20:00")

where A2 is start date/time, B2 is end/date time and H1:H10 contains holiday dates - that formula will work with any start or end dates/times

 

[DHT]

Hello DHT, welcome to MrExcel,

Which version of Excel are you using? If you have Excel 2010 or later try using NETWORKDAYS.INTL function like this

=(NETWORKDAYS.INTL(A2,B2,11,H$1:H$10)-1)*("20:00"-"8:00")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$10),MEDIAN(MOD(B2,1),"8:00","20:00"),"20:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$10)*MOD(A2,1),"8:00","20:00")

where A2 is start date/time, B2 is end/date time and H1:H10 contains holiday dates - that formula will work with any start or end dates/times

Thank you.. you made my day..
I am using Excel 2010 and its working..

Can we have a generic formula that has option to configure daily work hours , as mentioned in your post dated Nov 7th, 2009, 03:00 AM.. since I supervise multiple account SLA's that would be a great help.. Thanking you again..

 

[pedrowave]

Hello DHT, it may be a little late but here’s my solution to configure daily work hours with several time ranges.

In Excel 2010, sum this formula once for each range:
=IF(A10>B10,0,+$D$2*(NETWORKDAYS.INTL(A10,B10,Range1,Holidays)-1)
+IF(NETWORKDAYS.INTL(B10,B10,Range1,Holidays)=0,MAX($B$2,$C$2),MAX($B$2,MIN(MOD(B10,1),$C$2)))
-IF(NETWORKDAYS.INTL(A10,A10,Range1,Holidays)=0,MIN($B$2,$C$2),MIN($C$2,MAX(MOD(A10,1),$B$2))))
By example for 3 ranges:
Range1 = “0000011″ (Monday to Friday)
Range2 = “1111101″ (Saturday)
Range3 = “1111110″ (Sunday)

Better download an Excel template from my blog: Hours between dates | Pedro Wave for Excel Guys
Excel 2003 and 2007: Column D, using the auxiliary columns E:K
Excel 2010 and later: Column C, using the new WORKDAY.INTL function

 

[MRBarnes]

WOW.. lots going on in this thread.
I am trying to calculate the following:
Column D contains start time
Column E contains end time

Business hours are Monday - Friday 8:30am - 5:00pm. Need to exclude weekends (Not worried about holidays).
Only want to see the hours elapsed that are inside the business hours.
This is driving me nuts.

Example of dates:

Task assignment <colgroup><col width="147"></colgroup><tbody> </tbody>	Task Completion <colgroup><col width="147"></colgroup><tbody> </tbody>
Jul 2, 2013 11:54:10 PM <colgroup><col width="147"></colgroup><tbody> </tbody>	Jul 4, 2013 3:30:53 PM <colgroup><col width="147"></colgroup><tbody> </tbody>
Jul 12, 2013 9:34:12 PM <colgroup><col width="147"></colgroup><tbody> </tbody>	Jul 12, 2013 9:34:30 PM <colgroup><col width="147"></colgroup><tbody> </tbody>
Jul 8, 2013 7:51:05 PM <colgroup><col width="147"></colgroup><tbody> </tbody>	Jul 8, 2013 8:12:04 PM <colgroup><col width="147"></colgroup><tbody> </tbody>
Jul 8, 2013 7:51:44 PM <colgroup><col width="147"></colgroup><tbody> </tbody>	Jul 9, 2013 8:24:50 PM <colgroup><col width="147"></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[barry houdini]

You should be able to use a version of the formula I suggested in post #11 - try this

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

custom format result cell as [h]:mm

 

[MRBarnes]

You should be able to use a version of the formula I suggested in post #11 - try this

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

custom format result cell as [h]:mm

That did it! Thanks a bunch.

 

[pistachio nuts]

Hi,

can you help me with this?

Start Date	Age (current date and time)	Here's what i used to get 34	But i need the AGE to look like this
10-06-2013 4:58:06 PM	34	=NETWORKDAYS.INTL(A2,TODAY())-1	34 days xxxx hours

<tbody>
</tbody>

​

appreciate your help!

 

[STACZALA]

You should be able to use a version of the formula I suggested in post #11 - try this

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

custom format result cell as [h]:mm

When I use this formula, and my business hours are 6:00am to 6:00pm, it's adding 2 hours to the overall time. What could be adding the two hours?

Dates using:

5/13/2013 2:56 PM
5/28/2013 1:00 PM

This should give me 128 hours and 4 minutes. The formula is giving me 130 hours and 4 minutes.

Thanks.

 

[andredupreez]

whant to split overtime from working hours

WOW.. lots going on in this thread.
I am trying to calculate the following:
Column D contains start time
Column E contains end time

Business hours are Monday - Friday 8:30am - 5:00pm. Need to exclude weekends (Not worried about holidays).
Only want to see the hours elapsed that are inside the business hours.
This is driving me nuts.

Example of dates:

Task assignment <tbody> </tbody>	Task Completion <tbody> </tbody>
Jul 2, 2013 11:54:10 PM <tbody> </tbody>	Jul 4, 2013 3:30:53 PM <tbody> </tbody>
Jul 12, 2013 9:34:12 PM <tbody> </tbody>	Jul 12, 2013 9:34:30 PM <tbody> </tbody>
Jul 8, 2013 7:51:05 PM <tbody> </tbody>	Jul 8, 2013 8:12:04 PM <tbody> </tbody>
Jul 8, 2013 7:51:44 PM <tbody> </tbody>	Jul 9, 2013 8:24:50 PM <tbody> </tbody>

<tbody>
</tbody>

i want to work out the reg hours and the overtime separate, looking for the formulas in reg bolt to show

Range	Start hour	End hour	Hours by range	Monday		Tuesday		Wednesday
1	08:00	17:00	09:00	0		0		0
2	00:00	07:59	07:59	0		0		0
3	17:00	23:59	06:59	0		0		0
4				1		1		1
		Total hours	23:58	23:58		23:58		23:58
Start date	End date	Excel 2010	Total hours	Monday	ovetime	Tuesday	ovetime	Wednesday	ovetime
Monday 04/07/2014 06:00	Monday 04/07/2014 18:00	11:59	12:00	09:00	03:00
Tuesday 04/08/2014 07:00	Tuesday 04/08/2014 20:00	12:59	13:00			09:00	04:00
Wednesday 04/09/2014 08:00	Wednesday 04/09/2014 16:00	08:00	08:00					08:00	0

<tbody>
</tbody>

 

[andredupreez]

Re: whant to split overtime from working hours

Hi I need help with this

A1 B1
Start time 2014/4/12 7:00am end time 2014/4/12 9m Regular Time 9:00 Overtime 5:00


If Regular working hours in a week day is 9hour from 8:00 am to 5:00 pm and the rest must count for overtime work