IF and or Function

[bpxmas]

I'm trying to write a function to do the following:

if total hours are >=40 then
only total = 40

but
if there are 38 then total = 38

=IF(SUM(J338:J365)>=40,40),(SUM(J338:J365))

Hours
10 10 10 10
Total 40

Hours
5 5 5 5
Total 20

I can get it to work for one or the other, not both.

Thanks.

Brenda

 

[bluedragon2]

=IF(SUM(J338:J365)>=40,40,SUM(J338:J365))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bpxmas]

Ok, that works....Now another

I have to Hour Columns:

Reg Hours Overtime Hours

45 5

I only want the 40 hours to total in Reg Hous &
the 5 hours to go into the Overtime Hours

What do I need to do?

Thanks

 

[bpxmas]

I figured it out, let me know if this is correct?

=IF(SUM(J292:J319)>=40,SUM(J292:J319))-40


Thanks

 

[bluedragon2]

In the reg hours cell:

=IF(SUM(J292:J319)>=40,40,SUM(J292:J319))

In the overtime cell:

=IF(SUM(J292:J319)>=40,SUM(J292:J319)-40,0)

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bpxmas]

I more question?

There there are NO Overtime Hours then I want a zero in that column?

Thanks.

 

[bpxmas]

If I use your last function:

=IF(SUM(J292:J319)>=40,SUM(J292:J319)-40,0)

I get an error

Thanks.

 

[bluedragon2]

Works for me, what kind of error are you getting?

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bpxmas]

The formula I typed contains an error

 

[bpxmas]

The formula you typed contains an error.

Thanks.

 

[bpxmas]

It works now....I had an extra (.

Thanks so much for your help.

 

[SkipVought]

bpxmas,

Don't forget to thank Blue with a little purple star.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}