Calculating Time work plus

[adamkl]

I often put extra time in outside of the office and I need to be able to calculate this time into my time worked at the end of the day but I'm not sure how to do this.

This is how my current spreadsheet works, the formula does not include the "Extra" column.


ROUND(IF((OR(B33="",C33="")),0,IF((C33<B33),((C33-B33)*24)+24,(C33-B33)*24))+IF((OR(D33="",E33="")),0,IF((E33<D33),((E33-D33)*24)+24,(E33-D33)*24)),2)
<TABLE style="WIDTH: 712pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=950 border=0><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 372pt; mso-width-source: userset; mso-width-alt: 18139" width=496><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=83 height=20>Date</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>time in</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>time out</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>time in</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>time out</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>Extra</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 372pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=496>formula used</TD></TR><TR style="HEIGHT: 45pt" height=60><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 45pt; BACKGROUND-COLOR: transparent" height=60>8/22/2011</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">7:20 AM</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">12:00 PM</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">12:30 PM</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">4:15 PM</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0.5</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8.42</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 372pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=496>=ROUND(IF((OR(B33="",C33="")),0,IF((C33<B33),((C33-B33)*24)+24,(C33-B33)*24))+IF((OR(D33="",E33="")),0,IF((E33<D33),((E33-D33)*24)+24,(E33-D33)*24)),2)< font>
<B33),((C33-B33)*24)+24,(C33-B33)*24))+IF((OR(D33="",E33="")),0,IF((E33<D33),((E33-D33)*24)+24,(E33-D33)*24)),2)< font>


</TD></TR></TBODY></TABLE>

So, if I try to add this time it, it does not calculate the right time because it should be 9.12 (not 8.92) so I know I need to multiple the "Extra" field but not sure how to do this.
ROUND(IF((OR(B33="",C33="")),0,IF((C33<B33),((C33-B33)*24)+24,(C33-B33)*24))+IF((OR(D33="",E33="")),0,IF((E33<D33),((E33-D33)*24)+24,(E33-D33)*24)),2)+F33

<TABLE style="WIDTH: 712pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=950 border=0><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 372pt; mso-width-source: userset; mso-width-alt: 18139" width=496><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=83 height=20>Date</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>time in</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>time out</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>time in</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>time out</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=51>Extra</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 372pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=496>formula used</TD></TR><TR style="HEIGHT: 45pt" height=60><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 45pt; BACKGROUND-COLOR: transparent" height=60>8/22/2011</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">7:20 AM</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">12:00 PM</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">12:30 PM</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">4:15 PM</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0.5</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8.92</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 372pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=496>'=ROUND(IF((OR(B33="",C33="")),0,IF((C33<B33),((C33-B33)*24)+24,(C33-B33)*24))+IF((OR(D33="",E33="")),0,IF((E33<D33),((E33-D33)*24)+24,(E33-D33)*24)),2)+F33< font></TD></TR></TBODY></TABLE>

 

[AlphaFrog]

8.42 is not 8 hours and 42 minutes. It is 8.42 hours (or 8 hours and 25 minutes).

If you then add .5 hours to 8.42 hours, you get 8.92 hours (or 8 hours and 55 minutes) and not 9 hours and 12 minutes. So 8.92 seems to me to be the correct result.

If you want to convert the decimal hours back to to hours:minutes, then divide the decimal hours by 24 and format as time.

 

[seenfresh]

Looks like your formulas got cut off in the post...

Try this url which details how to use time functions:

http://office.microsoft.com/en-us/e...difference-between-two-times-HP003056108.aspx

How about the following formula in cell G2 (give that is the correct cell address where your formula resides and the columns are in order as shown below)

=((C2-B2)*1440)+((E2-D2)*1440)+F2

Note* F2 value is integer representing total extra minutes

This formula will calculate the total minutes and then you can convert the total minutes into hours and minutes if needed....

Example below:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">In</td><td style="text-align: center;;">Out</td><td style="text-align: center;;">In</td><td style="text-align: center;;">Out</td><td style="text-align: center;;">Extra</td><td style="text-align: center;;">Total
Minutes</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">8/22/2011</td><td style="text-align: right;;">7:20 AM</td><td style="text-align: right;;">12:00 PM</td><td style="text-align: right;;">12:30 PM</td><td style="text-align: right;;">4:15 PM</td><td style="text-align: right;;">30</td><td style="text-align: right;;">535</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet7</p><br /><br />

 

[blbat]

#VALUE!

Excel 2007

Cell Formulas
Range	Formula
F8	=((E8-B8+(E8))-(D8-C8+(D8)))*24
G8	=MIN($O$1,F8)
H8	=MAX(0,F8-$O$1)