Calculating elapsed hours

[Yulez3]

Hello fellow Excel users.. I need assistance; I am sure this has been brought up before but I am having the hardest time calculating business hours worked on the following example:

C1: Start Date
C2: 7/15/2011 11:05am
E1: End Date
E2: 7/25/2011 9:42am

Please help! I need to make sure this counts Mon-Friday workweek 8-5pm.

Thank you in advance

 

[Trevor G]

Welcome to the forum.

Take a look at this example. There are a few different types of calculations working things out, but Cell I2 is the correct formula. You would need to set the format.

Look at this thread as an example

http://www.mrexcel.com/forum/showthread.php?t=488433

http://www.mrexcel.com/forum/showpost.php?p=2410253&postcount=2

Sheet4

<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: 139px"><COL style="WIDTH: 131px"><COL style="WIDTH: 97px"><COL style="WIDTH: 64px"><COL style="WIDTH: 124px"><COL style="WIDTH: 64px"><COL style="WIDTH: 134px"><COL style="WIDTH: 70px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Start Time</TD><TD>End Time</TD><TD>Calculate Days</TD><TD>Time</TD><TD>Total Days & Hours</TD><TD> </TD><TD>Total Working Hours</TD><TD>Start Time</TD><TD>End Time</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">15/07/2011 11:05</TD><TD style="TEXT-ALIGN: right">25/07/2011 09:42</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">238</TD><TD>10:238</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">52:37</TD><TD style="TEXT-ALIGN: right">08:00</TD><TD style="TEXT-ALIGN: right">17:00</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; 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>E2</TD><TD>=INT(D2)-INT(C2)</TD></TR><TR><TD>F2</TD><TD>=TEXT(D2-C2,"[h]")+0</TD></TR><TR><TD>G2</TD><TD>=INT(D2)-INT(C2)&":"&TEXT(D2-C2,"[h]")</TD></TR><TR><TD>I2</TD><TD>=(NETWORKDAYS(C2,D2)-1)*(K$2-J$2)+IF(NETWORKDAYS(D2,D2),MEDIAN(MOD(D2,1),K$2,J$2),J$2)-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1),K$2,J$2)</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