WHY doesn't formula work???

T

TSuraco

I'm trying to track straight time vs. Overtime. I want H16 to add columns
H16:L16 (where time totals are) & I then want G16 to subtract 40 if H16 is
greater than 40. I've tried everything I can think of. Help!!!!

=(SUM(H16:L16)-40)
=IF(SUM(H16:L16)>40 -40) (I dunno)
 
C

Cortez

I'm trying to track straight time vs. Overtime. I want H16 to add columns
H16:L16 (where time totals are) & I then want G16 to subtract 40 if H16 is
greater than 40. I've tried everything I can think of. Help!!!!

=(SUM(H16:L16)-40)
=IF(SUM(H16:L16)>40 -40) (I dunno)
Click to expand...


Try:

=if(sum(H16:L16)>40,Sum(H16:L16)-40)

Or, if your time values are formatted as Time (ex: HH:MM:SS) then try
subtracting time(40,0,0)

=if(sum(H16:L16)>40,Sum(H16:L16)-TIME(40,0,0)

Ctk
 
C

Conan Kelly

TSuraco,

What do you want if the sum is less than/equal to 40?

This formula will return 0 if <= 40:

=if(sum(h16:l16)>40,sum(h16:l16)-40,0)

Change the last 0 after the comma to what ever you need:

"" (empty quotes) will make the cell appear to be blank
"No overtime this pay period" (be sure to include the quotes) will
return that phrase in the cell.

HTH,

Conan
 
B

Bill Kuunders

You cannot have H16 to add cells H16 to L16

H16 is part of the range you want to add

your first formula will work if you add the cells H16 to L16 in a different
cell first and then
have another cell deduct 40
I'e'
M16 ....=sum(H16:L16)
N16........=sum(H16:L16)-40
or
N16........=M16-40
 
T

TSuraco

ThankYou! Thank You! Thank You!!

Cortez said:
Try:

=if(sum(H16:L16)>40,Sum(H16:L16)-40)

Or, if your time values are formatted as Time (ex: HH:MM:SS) then try
subtracting time(40,0,0)

=if(sum(H16:L16)>40,Sum(H16:L16)-TIME(40,0,0)

Ctk
Click to expand...
 
T

TSuraco

OMG!!! Even BETTER!!! Ty, Ty, Ty!!!!!!!

Conan Kelly said:
TSuraco,

What do you want if the sum is less than/equal to 40?

This formula will return 0 if <= 40:

=if(sum(h16:l16)>40,sum(h16:l16)-40,0)

Change the last 0 after the comma to what ever you need:

"" (empty quotes) will make the cell appear to be blank
"No overtime this pay period" (be sure to include the quotes) will
return that phrase in the cell.

HTH,

Conan
Click to expand...
 
Top