Add one until 171 is reached

[Guest]

Hello.
This is my scenario for a time sheet.
If total hours worked are over 168 then each hour over is equal to comp time
until 171 is reached. How can I put this into a function.
Here is the set-up example:
AE6 AJ6 AL6
Actual Hours Total Hours Comp Time
Worked
169 169 1

There is also another caveat. If actual hours worked are less than 168, but
total hours is over 168, than hours over 168 equal comp time.
Scenario #2:
AE6 AJ6 AL6
Actual Hours Total Hours Comp Time
Worked
160 172 4

This may be too difficult or not possible to do, but any help is greatly
appreciated. This is the last thing I have to do to complete the time sheet.
Thanks
JP

 

[joeu2004]

If total hours worked are over 168 then each hour over is equal to comp time
until 171 is reached. How can I put this into a function.
Here is the set-up example:
AE6 AJ6 AL6
Actual Hours Total Hours Comp Time
Worked
169 169 1

There is also another caveat. If actual hours worked are less than 168, but
total hours is over 168, than hours over 168 equal comp time.
Scenario #2:
AE6 AJ6 AL6
Actual Hours Total Hours Comp Time
Worked
160 172 4

In AL6:

=if(AE6 < 168, max(0, AJ6 - 168), max(0, max(171, AJ6) - 168))

The last part could also be written max(0, min(3, AJ6 - 168)).

 

[Richard Buttrey]

Which takes precedence if Total hours are over 168 and Actual hours
are less than 168 as per yout two examples?

Rgds

Hello.
This is my scenario for a time sheet.
If total hours worked are over 168 then each hour over is equal to comp time
until 171 is reached. How can I put this into a function.
Here is the set-up example:
AE6 AJ6 AL6
Actual Hours Total Hours Comp Time
Worked
169 169 1

There is also another caveat. If actual hours worked are less than 168, but
total hours is over 168, than hours over 168 equal comp time.
Scenario #2:
AE6 AJ6 AL6
Actual Hours Total Hours Comp Time
Worked
160 172 4

This may be too difficult or not possible to do, but any help is greatly
appreciated. This is the last thing I have to do to complete the time sheet.
Thanks
JP

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Guest]

I am not sure. They seem to be one in the same

Which takes precedence if Total hours are over 168 and Actual hours
are less than 168 as per yout two examples?

Rgds



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Guest]

This was exactly what I needed. Now I can complete this project.

Thanks a bunch

JP

 

[Richard Buttrey]

If I'm reading your two examples correctly they can give different
answers unless Total Hours are in some way arithmetically related to
Actual hours. Which is why presumably you mentioned the second caveat.

However if the suggested answer is working OK, it's obviously my
interpretation of you Q. that is wrong.

Rgds

I am not sure. They seem to be one in the same

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________