Calculate only working hours between two dates excluding weekends

If start date and time is in A1, start date and time in A2 . . .
returns 245:20:00 for your given dates/times.
Format the cell containing the formula as [h]:mm:ss

By the way, my suggestion does not account for holidays. But the NETWORKDAYS function can be made to account for them.

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....

barry houdini said:

You could simplify that a little Gerald, i.e.

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

Click to expand...

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....

Click to expand...

LOL you're right

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.

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

Hey Barry does this formula exclude weekends too?

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.......

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