Calculate only working hours between two dates excluding weekends

S

Spoorthi

New Member
Joined
Oct 27, 2009
Messages
21
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.
 
Sort by date Sort by votes
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
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
barry houdini said:
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
Click to expand...

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..
 
Upvote 0
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
 
Upvote 0
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:[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 147"]
<colgroup><col width="147"></colgroup><tbody>[TR]
[TD="class: xl68, width: 147"]Task assignment
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<colgroup><col width="147"></colgroup><tbody>[TR]
[TD="class: xl68, width: 147"]Task Completion
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 147"]
<colgroup><col width="147"></colgroup><tbody>[TR]
[TD="class: xl68, width: 147"]Jul 2, 2013 11:54:10 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<colgroup><col width="147"></colgroup><tbody>[TR]
[TD="class: xl68, width: 147"]Jul 4, 2013 3:30:53 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 147"]
<colgroup><col width="147"></colgroup><tbody>[TR]
[TD="class: xl68, width: 147"]Jul 12, 2013 9:34:12 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<colgroup><col width="147"></colgroup><tbody>[TR]
[TD="class: xl68, width: 147, align: right"]Jul 12, 2013 9:34:30 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 147"]
<colgroup><col width="147"></colgroup><tbody>[TR]
[TD="class: xl68, width: 147"]Jul 8, 2013 7:51:05 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<colgroup><col width="147"></colgroup><tbody>[TR]
[TD="class: xl68, width: 147"]Jul 8, 2013 8:12:04 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 147"]
<colgroup><col width="147"></colgroup><tbody>[TR]
[TD="class: xl68, width: 147"]Jul 8, 2013 7:51:44 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<colgroup><col width="147"></colgroup><tbody>[TR]
[TD="class: xl68, width: 147"]Jul 9, 2013 8:24:50 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
barry houdini said:
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
Click to expand...

That did it! Thanks a bunch.
 
Upvote 0
Hi,

can you help me with this?

[TABLE="class: grid, width: 650, align: center"]
<tbody>[TR]
[TD="align: center"]Start Date[/TD]
[TD="align: center"]Age
(current date and time)[/TD]
[TD="align: center"]Here's what i used to get 34[/TD]
[TD="align: center"]But i need the AGE to look like this[/TD]
[/TR]
[TR]
[TD="align: center"]10-06-2013 4:58:06 PM[/TD]
[TD="align: center"]34 [/TD]
[TD="align: center"]=NETWORKDAYS.INTL(A2,TODAY())-1[/TD]
[TD="align: center"]34 days xxxx hours[/TD]
[/TR]
</tbody>[/TABLE]

​
appreciate your help!
 
Upvote 0
barry houdini said:
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
Click to expand...


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.
 
Upvote 0
whant to split overtime from working hours

MRBarnes said:
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:[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl68, width: 147"]Task assignment[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl68, width: 147"]Task Completion[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl68, width: 147"]Jul 2, 2013 11:54:10 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl68, width: 147"]Jul 4, 2013 3:30:53 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl68, width: 147"]Jul 12, 2013 9:34:12 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl68, width: 147, align: right"]Jul 12, 2013 9:34:30 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl68, width: 147"]Jul 8, 2013 7:51:05 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl68, width: 147"]Jul 8, 2013 8:12:04 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl68, width: 147"]Jul 8, 2013 7:51:44 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl68, width: 147"]Jul 9, 2013 8:24:50 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...







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

[TABLE="width: 951"]
<tbody>[TR]
[TD]Range[/TD]
[TD]Start hour[/TD]
[TD]End hour[/TD]
[TD]Hours by range[/TD]
[TD]Monday[/TD]
[TD][/TD]
[TD]Tuesday[/TD]
[TD][/TD]
[TD]Wednesday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]08:00[/TD]
[TD]17:00[/TD]
[TD]09:00[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]00:00[/TD]
[TD]07:59[/TD]
[TD]07:59[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]17:00[/TD]
[TD]23:59[/TD]
[TD]06:59[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total hours[/TD]
[TD]23:58[/TD]
[TD]23:58[/TD]
[TD][/TD]
[TD]23:58[/TD]
[TD][/TD]
[TD]23:58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Start date[/TD]
[TD]End date[/TD]
[TD]Excel 2010[/TD]
[TD]Total hours[/TD]
[TD]Monday[/TD]
[TD]ovetime[/TD]
[TD]Tuesday[/TD]
[TD]ovetime[/TD]
[TD]Wednesday[/TD]
[TD]ovetime[/TD]
[/TR]
[TR]
[TD]Monday 04/07/2014 06:00[/TD]
[TD]Monday 04/07/2014 18:00[/TD]
[TD]11:59[/TD]
[TD]12:00[/TD]
[TD]09:00
[/TD]
[TD]03:00[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Tuesday 04/08/2014 07:00[/TD]
[TD]Tuesday 04/08/2014 20:00[/TD]
[TD]12:59[/TD]
[TD]13:00[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]09:00[/TD]
[TD]04:00[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Wednesday 04/09/2014 08:00[/TD]
[TD]Wednesday 04/09/2014 16:00[/TD]
[TD]08:00[/TD]
[TD]08:00[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]08:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
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 9:pm 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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Hours Between 2 Dates Excluding Non-Business Hours ONLY
Replies
7
Views
732
felixstraube
F
P
  • Question Question
How to subtract 2 dates and times from each other excl weekends, holidays and only considering working hours during the day?
Replies
1
Views
200
Micron
M
C
  • Question Question
Business Hours Mon-Fri 8AM - 5PM between two dates and times
Replies
5
Views
3K
Ganesh1986
G
B
  • Solved
Issue with finding time difference taking account of weekends and workdays.
2
Replies
15
Views
458
Joe4
Joe4
J
  • Solved
Date Calculation
Replies
2
Views
42
jedilefty
J

Forum statistics

Threads
1,224,818
Messages
6,181,150
Members
453,021
Latest member
Justyna P

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