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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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
 

Gerald Higgins

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

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

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115

ADVERTISEMENT

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:
 

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.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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
 

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

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,417
Messages
5,511,229
Members
408,831
Latest member
heidiussel

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top