Calculate only working hours between two dates excluding weekends

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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
If start date and time is in A1, start date and time in A2 . . .
Code:
=((NETWORKDAYS(A1,A2)-2)*15/24)+23/24-MOD(A1,1)+MOD(A2,1)-8/24
returns 245:20:00 for your given dates/times.
Format the cell containing the formula as [h]:mm:ss
 
Upvote 0

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
By the way, my suggestion does not account for holidays. But the NETWORKDAYS function can be made to account for them.
 
Upvote 0

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
You could simplify that a little Gerald, i.e.

=(NETWORKDAYS(A1,A2)-1)*15/24+MOD(A2,1)-MOD(A1,1)

Note: this only works assuming start and end time/dates will always be within working hours, if not you'll need a more complex formula....
 
Upvote 0

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
You could simplify that a little Gerald, i.e.

=(NETWORKDAYS(A1,A2)-1)*15/24+MOD(A2,1)-MOD(A1,1)
Yes you're right. I did think of that, but decided it would be better to show the formula in that longer format to help explain what it does.

Note: this only works assuming start and end time/dates will always be within working hours, if not you'll need a more complex formula....
LOL you're right :biggrin:
 
Upvote 0

Spoorthi

New Member
Joined
Oct 27, 2009
Messages
21
Gerald,

If the start time and end time are 8 Am and 6:30 PM, then wht changes should I make in the formula? Also, please let me know how I can exclude the holiday list too.
 
Upvote 0

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Here's a generic formula

=(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
 
Upvote 0

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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.......
 
Upvote 0

avoodi

New Member
Joined
Nov 6, 2009
Messages
2
I am somehow not able to get it to work
I think something wrong in the formating of my cells

I am trying to use the formula given below
=(NETWORKDAYS(H6,N6)-1)*($F$2-$E$2)+MOD(N6,1)-MOD(H6,1)
here H6, N6 are in format of date time (like 3/5/2009 2:11:27 PM)
and F2, E2 are in time format (9:00:00 AM and 6:00:00 PM)

So when i calculate for start date <TABLE style="WIDTH: 81pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=108 border=0 x:str><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_2106812 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=108 height=17 x:num="39874.312037037038">3/2/09 7:29</TD></TR></TBODY></TABLE>
and end date <TABLE style="WIDTH: 147pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=196 border=0 x:str><COLGROUP><COL style="WIDTH: 147pt; mso-width-source: userset; mso-width-alt: 7168" width=196><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_2106812 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 147pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=196 height=17 x:num="39903.761805555558">3/31/09 18:17</TD></TR></TBODY></TABLE>
Its giving me wrong result as 7:29 ... (this result column i have formated as h:mm:ss )
please help
 
Upvote 0

Forum statistics

Threads
1,186,674
Messages
5,959,126
Members
438,398
Latest member
Ashwin Masurkar

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
Top