Calculating the number of hours/min between two date times excluding after hours and weekends.

[harmanadam]

I'm currently using Excell 2003 SP3 Professional Edition.

My overall goal is to get a formula that calculates the hours and minutes between to time frames. I've tried finding formulas via this website and others and none of them work. I consider my level of Excel knowledge to be average, so I maybe missing something very easy here.

Example:
AH= 5/2/2011 16:51
AI= 5/5/2011 11:23

I need to only calculate hours and minutes for business hours and exclude weekend/after-hours.

Our business hours are from 7am-6pm, so the hours/minutes should only be calculating between those times.

Any help would be greatly appreciated. I can also send spreadsheet if need be.

 

[AnAnalyst]

Hi there,

For this formula to work in versions of Excel prior to 2007 you will need the Analysis Toolpack loaded.

Try this formula:

=(NETWORKDAYS(AH2,AI2)-1)*(18/24-7/24)+MOD(AI2,1)-MOD(AH2,1)

Custom format the cell that you put this formula in as:

[h]:mm

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 128px"><COL style="WIDTH: 128px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>AH</TD><TD>AI</TD><TD>AJ</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">02/05/2011 16:51</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">05/05/2011 11:23</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">27:32</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>AJ2</TD><TD>=(NETWORKDAYS(AH2,AI2)-1)*(18/24-7/24)+MOD(AI2,1)-MOD(AH2,1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[harmanadam]

So I either have to download the Expansion pack or utilize Excel 2007 or higher to get this to work?

Currently when I plus that formula in, I get #NAME? as my value.

Thanks for the reply.

Adam

 

[harmanadam]

Hi Ananalyst,
I did the add on for Analysis Toolpack and it works great, but there are some rows I'm seeing that aren't calculating properly. I made need to tweek the formula even further.

The first example should be a value of 0 because the start time 18:50 is after the calculated time of 18:00.

The second example doesn't even show a value because it's saying the value is a negative date or time.

Can you tell me if I may need to tweek the formula or will it just not work?

Example 1:
<TABLE style="WIDTH: 301pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=401 border=0 x:str><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 131pt; mso-width-source: userset; mso-width-alt: 6363" width=174><TBODY><TR style="HEIGHT: 12pt" height=16><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" width=103 height=16 x:num="40689.784930555557">5/26/2011 18:50</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 93pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=124 x:num="40689.801944444444">5/26/2011 19:14</TD><TD class=xl66 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 131pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=174 x:num="1.7013888886140194E-2" x:fmla="=(NETWORKDAYS(A1,B1)-1)*(18/24-7/24)+MOD(B1,1)-MOD(A1,1)">0:24</TD></TR></TBODY></TABLE>

Example 2:
<TABLE style="WIDTH: 301pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=401 border=0 x:str><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 131pt; mso-width-source: userset; mso-width-alt: 6363" width=174><TBODY><TR style="HEIGHT: 12pt" height=16><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" width=103 height=16 x:num="40693.951087962967">5/30/2011 22:49</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 93pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=124 x:num="40694.386354166665">5/31/2011 9:16</TD><TD class=xl66 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 131pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" align=middle width=174 x:num="-0.10640046296854666" x:fmla="=(NETWORKDAYS(A1,B1)-1)*(18/24-7/24)+MOD(B1,1)-MOD(A1,1)">########################</TD></TR></TBODY></TABLE>

 

[kpark91]

Check the format of the cell, and try increasing the widths of the cell.

 

[barry houdini]

Analyst's suggestion is good for situations where the start and end times/dates are witthin working hours.....but as that isn't the case you need a more complex version. see this link

http://www.mrexcel.com/forum/showpost.php?p=2113046&postcount=11

 

[harmanadam]

Kpark, I thought the same thing but the formatting is the same as the other cells that did work.

Can anyone get this formula http://www.mrexcel.com/forum/showpos...6&postcount=11
to work if my cells are as follows:
AH= 5/30/2011 22:49
AI= 5/31/2011 9:16
I'm trying to figure out how to show the hours in between the times of 7am and 18pm.
Any advise on what the formula would like like then when using the NETWORKDAYS format?
Do I need another cell to show the hours I wanted calculated?

http://www.mrexcel.com/forum/showpos...6&postcount=11

 

[harmanadam]

I just realized the original formula AnAlyst provided to me work.

Here's an example:
<TABLE style="WIDTH: 301pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=401 border=0 x:str><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 131pt; mso-width-source: userset; mso-width-alt: 6363" width=174><TBODY><TR style="HEIGHT: 12pt" height=16><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" width=103 height=16 x:num="40687.690127314811">5/24/2011 16:33</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 93pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=124 x:num="40688.577118055553">5/25/2011 13:51</TD><TD class=xl66 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 131pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=174 x:num="0.34532407407580945" x:fmla="=(NETWORKDAYS(A1,B1)-1)*(18/24-7/24)+MOD(B1,1)-MOD(A1,1)">8:17</TD></TR></TBODY></TABLE>

So it start the clock at 16:33, stopped it at 18:00 and picked it back up at 7:00 for a total of 8 hours, 17 minutes.

So really my only problem now is finding out why these 9 didn't work out of 1124 didn't work. Any thoughts?

<TABLE style="WIDTH: 301pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=401 border=0 x:str><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 131pt; mso-width-source: userset; mso-width-alt: 6363" width=174><TBODY><TR style="HEIGHT: 12pt" height=16><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" width=103 height=16 x:num="40693.951087962967">5/30/2011 22:49</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 93pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" width=124 x:num="40694.386354166665">5/31/2011 9:16</TD><TD class=xl66 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 131pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" align=middle width=174 x:num="-0.10640046296854666">########################</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="40664.962465277778">5/1/2011 23:05</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" x:num="40665.434293981481">5/2/2011 10:25</TD><TD class=xl66 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:num="-0.52817129629693227">########################</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="40685.900474537033">5/22/2011 21:36</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" x:num="40685.930821759262">5/22/2011 22:20</TD><TD class=xl66 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:num="-0.42798611110386747">########################</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="40693.937337962961">5/30/2011 22:29</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" x:num="40694.398958333331">5/31/2011 9:34</TD><TD class=xl66 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:num="-8.0046296296738317E-2">########################</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="40678.865185185183">5/15/2011 20:45</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" x:num="40679.341886574075">5/16/2011 8:12</TD><TD class=xl66 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:num="-0.523298611107748">########################</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="40692.29587962963">5/29/2011 7:06</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" x:num="40692.317928240736">5/29/2011 7:37</TD><TD class=xl66 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:num="-0.43628472222674947">########################</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="40692.462164351848">5/29/2011 11:05</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" x:num="40692.509988425925">5/29/2011 12:14</TD><TD class=xl66 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:num="-0.41050925925568055">########################</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="40685.767418981479">5/22/2011 18:25</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" x:num="40686.08320601852">5/23/2011 1:59</TD><TD class=xl66 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:num="-0.68421296295855427">########################</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="40672.993310185186">5/9/2011 23:50</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" x:num="40673.349097222221">5/10/2011 8:22</TD><TD class=xl66 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:num="-0.18587962963162385">########################</TD></TR></TBODY></TABLE>

 

[barry houdini]

As I said in my previous post, AnAnalyst's formula will work when the start and end times are within the working hours. All those examples have start and/or end times that are at evenings or weekends etc.

If you use the formula I suggested then that will handle any start or end time. The formula I linked to was as follows:

=(NETWORKDAYS(H6,N6)-1)*($F$2-$E$2)+IF(NETWORKDAYS(N6,N6),MEDIAN(MOD(N6,1),$F$2,$E$2),$F$2)-MEDIAN(NETWORKDAYS(H6,H6)*MOD(H6,1),$F$2,$E$2)

In that example you need to replace all instances of H6 (the start time) with AH2 and all instances of N6 (the end time) with AI2. E2 and F2 should contain the start and end times of the working day, i.e. in your case 07:00 and 18:00 respectively (of course you can use any cells and change accordingly in the formula). Alternatively you can just use 7/24 and 18/24 in the formula in place of E2 and F2.....

The above formula can also be adapted to avoid use of NETWORKDAYS if you want (using only "native" Excel 2003 functions), but that wouldn't allow you to exclude holidays (as NETWORKDAYS does), do you need to exclude holidays at all?

 

[barry houdini]

So really my only problem now is finding out why these 9 didn't work out of 1124 didn't work. Any thoughts?

I'd suggest, actually, that there were more than 9 that "didn't work". Only 9 may give you an error (that's caused by excel trying to display a negative time value) but probably out of the other 1115 there will be some where the formula returns a time value - but an incorrect one

Take your original example, i.e.

Example:
AH= 5/2/2011 16:51
AI= 5/5/2011 11:23

....and change the end time only to 19:23 (8 hours later). Your original formula will give a result of 35:32....but it should be 34:09, the original formula incorrectly counts through to 1923 on the end date when it should stop at 18:00, giving you an overcount of 1:23 (not that there's anything wrong with AnAnalyst's suggestion...it just isn't designed to work in that situation)