Whats wrong with my formula?

S

siasarma

New Member
Joined
Sep 20, 2008
Messages
46
I cant for the life of my figure out why my formula won't recognize my 40:00 hours. I am trying to have it add up my timecard and put the regular hours in one cell and the overtime hours in another cell. I almost have it except for in the normal hour cell it wont recognize the 40:00 hours and it goes with the if it does not equal to function. Here's my function in the normal hour cell:
=IF(SUM(F2:F9)="40:00","40:00",SUM(F2:F9))

In the overtime cell it is:
=IF(F10<40, SUM(F2:F9)-"40:00", )

And that one is working fine. It recognize the normal one as being 40 hours and gives me the SUM of F2:F9. Why?

Here's my timecard if this helps!!!

Thanks for the help!

<TABLE style="WIDTH: 443pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=590 border=0 x:str><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" span=3 width=96><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD class=xl42 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 84pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #ffff99" width=112 height=22>WEEK 1</TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 72pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=96>IN</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=94>OUT</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=96>IN</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=96>OUT</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 72pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=96>TOTAL:</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl44 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Monday</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333331">8:00</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.5">12:00</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.54166666666666663">13:00</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.70833333333333337">17:00</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333343" x:fmla="=(C2-B2)+(E2-D2)">8:00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl45 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #ffff99" height=21>Tuesday</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.33333333333333331">8:00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.5">12:00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.54166666666666663">13:00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.70833333333333337">17:00</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.33333333333333343" x:fmla="=(C3-B3)+(E3-D3)">8:00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl44 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Wednesday</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333331">8:00</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.5">12:00</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.54166666666666663">13:00</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.70833333333333337">17:00</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333343" x:fmla="=(C4-B4)+(E4-D4)">8:00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl45 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #ffff99" height=21>Thursday</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.33333333333333331">8:00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.5">12:00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.54166666666666663">13:00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.70833333333333337">17:00</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.33333333333333343" x:fmla="=(C5-B5)+(E5-D5)">8:00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl44 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Friday</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333331">8:00</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.5">12:00</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.54166666666666663">13:00</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.70833333333333337">17:00</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333343" x:fmla="=(C6-B6)+(E6-D6)">8:00</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl46 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #ffff99" height=22>Saturday</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.33333333333333331">8:00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.5">12:00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.54166666666666663">13:00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.70833333333333337">17:00</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.33333333333333343" x:fmla="=(C7-B7)+(E7-D7)">8:00</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl47 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>Sunday</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333331">8:00</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.5">12:00</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.54166666666666663">13:00</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.70833333333333337">17:00</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333343" x:fmla="=(C8-B8)+(E8-D8)">8:00</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> </TD><TD class=xl48 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ff99cc">No Break:</TD><TD class=xl49 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ff99cc">Quantity</TD><TD class=xl50 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ff99cc"> </TD><TD class=xl53 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ff99cc" align=right x:num="0" x:fmla='=E9*"0:20"'>0:00</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl35 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> </TD><TD class=xl36 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">Total:</TD><TD class=xl51 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; WIDTH: 72pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" width=96 x:num="2.3333333333333339" x:fmla='=IF(SUM(F2:F9)=40,"40:00",SUM(F2:F9))'>56:00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> </TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">Overtime:</TD><TD class=xl52 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" x:num="0.66666666666666718" x:fmla='=IF(F10<40, SUM(F2:F9)-"40:00", )'>16:00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD class=xl28 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl23 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> </TD><TD class=xl38 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">Holiday</TD><TD class=xl39 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff"> </TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl23 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> </TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffff">Vacation</TD><TD class=xl41 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffff"> </TD></TR></TBODY></TABLE>
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Hi siasarma

"40:00" is a text. You need a number. Try:

=IF(SUM(F2:F9)="40:00"+0,"40:00"+0,SUM(F2:F9))

although I don't understand the formula. Isn't it equivalent to:

=SUM(F2:F9)

??
 
Upvote 0
I tried that and it didnt work either. It is still showing the 56:00.

It is the same as =SUM(F2:F9) but with the IF Function, because I need it to display something if it is equal to and something different if it is not equal to...I dont know if that makes sense or not.
 
Upvote 0
It is the same as =SUM(F2:F9) but with the IF Function, because I need it to display something if it is equal to and something different if it is not equal to...I dont know if that makes sense or not
Click to expand...

=IF(SUM(F2:F9)="40:00"+0,"40:00"+0,SUM(F2:F9))

Since SUM(F2:F9) is equal to 56:00 it goes to the False part of the If where you tell it to display SUM(F2:F9) which is 56:00.
The formula is working OK, it doesn't do what you wish, it does what you tell it to do.


I don't understand. If you want to display something else when it's not equal to 40:00, write it in the False part of the If.

Ex.:

=IF(SUM(F2:F9)="40:00"+0,1,2)

You'll see that if SUM(F2:F9) is different from 40:00 then you'll see 2.

Perhaps you can explain what you want. Then it will be easier to suggest a formula.
 
Upvote 0
I guess I dont know why I am using this formula. I just found it on an earlier post and they said it worked, so I just copied it.

Ok, I will try and explain better what I am trying to do:

In the cell with the "Total" at the bottom, I want it to add the hours from the "total" list from each day of the week. I want it to display whatever the sum is UNLESS it is over 40 hours. Which it is right now. THEN I want it to ONLY display the 40 hours, and carry over whatever the remaining hours would be and put those in the "Overtime." Example: This total comes to 56 hours. I want it to display 40 in the "Total" and 16 in the overtime.

Thanks for your help! Sorry I am being a pain!


<TABLE style="WIDTH: 443pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=590 border=0 x:str><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" span=3 width=96><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD class=xl44 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 84pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #ffff99" width=112 height=22>WEEK 1</TD><TD class=xl45 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 72pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=96>IN</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=94>OUT</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=96>IN</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=96>OUT</TD><TD class=xl36 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 72pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99" width=96>TOTAL:</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl46 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Monday</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333331">8:00</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.5">12:00</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.54166666666666663">13:00</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.70833333333333337">17:00</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333343" x:fmla="=(C2-B2)+(E2-D2)">8:00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl47 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #ffff99" height=21>Tuesday</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.33333333333333331">8:00</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.5">12:00</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.54166666666666663">13:00</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.70833333333333337">17:00</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.33333333333333343" x:fmla="=(C3-B3)+(E3-D3)">8:00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl46 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Wednesday</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333331">8:00</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.5">12:00</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.54166666666666663">13:00</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.70833333333333337">17:00</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333343" x:fmla="=(C4-B4)+(E4-D4)">8:00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl47 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #ffff99" height=21>Thursday</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.33333333333333331">8:00</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.5">12:00</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.54166666666666663">13:00</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.70833333333333337">17:00</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.33333333333333343" x:fmla="=(C5-B5)+(E5-D5)">8:00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl46 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Friday</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333331">8:00</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.5">12:00</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.54166666666666663">13:00</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.70833333333333337">17:00</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333343" x:fmla="=(C6-B6)+(E6-D6)">8:00</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl48 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #ffff99" height=22>Saturday</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.33333333333333331">8:00</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.5">12:00</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.54166666666666663">13:00</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.70833333333333337">17:00</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num="0.33333333333333343" x:fmla="=(C7-B7)+(E7-D7)">8:00</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl49 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>Sunday</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333331">8:00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.5">12:00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.54166666666666663">13:00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.70833333333333337">17:00</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333343" x:fmla="=(C8-B8)+(E8-D8)">8:00</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22></TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> </TD><TD class=xl50 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ff99cc">No Break:</TD><TD class=xl51 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ff99cc">Quantity</TD><TD class=xl52 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ff99cc"> </TD><TD class=xl55 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ff99cc" align=right x:num="0" x:fmla='=E9*"0:20"'>0:00</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl37 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> </TD><TD class=xl38 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">Total:</TD><TD class=xl53 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; WIDTH: 72pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" width=96 x:num="2.3333333333333339" x:fmla='=IF(SUM(F2:F9)=40,"40:00",SUM(F2:F9))'>56:00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> </TD><TD class=xl39 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">Overtime:</TD><TD class=xl54 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff" x:num="0.66666666666666718" x:fmla='=IF(F10<40, SUM(F2:F9)-"40:00", )'>16:00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD class=xl30 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> </TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">Holiday</TD><TD class=xl41 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff"> </TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> </TD><TD class=xl42 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffff">Vacation</TD><TD class=xl43 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffff"> </TD></TR></TBODY></TABLE>
 
Upvote 0
format your cells to custom 0.00 and use following formula:
for total: =IF(SUM(F2:F9)>40,40,SUM(F2:F9))
for overtime =SUM(F2:F9)-F11
(F11 is the result for total)
 
Upvote 0
In the cell with the "Total" at the bottom, I want it to add the hours from the "total" list from each day of the week. I want it to display whatever the sum is UNLESS it is over 40 hours. Which it is right now. THEN I want it to ONLY display the 40 hours, and carry over whatever the remaining hours would be and put those in the "Overtime." Example: This total comes to 56 hours. I want it to display 40 in the "Total" and 16 in the overtime.
Click to expand...

Ok. For the total you can use:

=MIN(SUM(F2:F9),"40:00"+0)

and for the overtime:

=max(SUM(F2:F9)-"40:00",0)
 
Upvote 0
Great!!! Thanks pgc01!!! That did it! Thank you for your help! One more thing...can you explain that formula to me? I am not familiar with how the MIN and MAX works...and I dont know why you had to +0 and -"40:00"....THANKS FOR YOUR HELP AND TIME!!!
 
Upvote 0
I'm glad it's working now.

I dont know why you had to +0 and -"40:00"
Click to expand...

"40:00" is not a time, it's a text. You have to convert it to a time. You can do it using the Timevalue() function

=MIN(SUM(F2:F9),TIMEVALUE("40:00"))

A quicker way to do it is to perform an arithmetic operation. Excel knows that an arithmetic operation requires 2 numbers and so it will automatically try to convert any text to a number. These are equivalent:

=MIN(SUM(F2:F9),"40:00"+0)

=MIN(SUM(F2:F9),"40:00"*1)

Adding 0 or multiplying by 1 will not change a number, but forces excel to automatically convert the "40:00" text into 40:00 time.

...can you explain that formula to me? I am not familiar with how the MIN and MAX works...
Click to expand...

=MIN(SUM(F2:F9),"40:00"+0)

The function Min() chooses the smallest of the parameters, in this case it will choose between Sum(f2:f9) and 40:00, whichever is the smallest, which is what you want.

The Max() function is similar. Also check the help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
813
gvsumich
G
V
  • Question
Issue with scraping with VBA
Replies
1
Views
488
John_w
J
E
  • Question
VBA for Web Scraping help
Replies
0
Views
576
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,214,429
Messages
6,119,435
Members
448,898
Latest member
dukenia71

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top