"JP" wrote:

I have a brain teaser funtion needed for a time sheet.
Here is the set-up:
Example Numbers:
AE AJ AL
Actual Hours Total Hours Comp Time
171 180 = 12

This is how comp time should be calculated.
Here are the conditions:
Comp time is given for the first three Actual Hours over 168. Up until 171
hours.
For example, if actual hours is 171 and total hours is 180 than comp time
should equal 12. Here is the current function I am using. However, when
these numbers are pulgged in comp time stays at 3:
=IF(AE6<168,MAX(0,AJ6-168),MAX(0,MIN(3,AJ6-168)))
How can I tweek this to work correctly?

Many thanks

JP



Try this =IF(AJ6=180,12,IF(AE6=171,3,0)) the only thing is that you did
not say what you wanted to do if there was no comp time so had it return a
zero.

I hope this helps