Add one until 171 is reached

J

JP

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
 
J

joeu2004

JP said:
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
Click to expand...

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)).
 
R

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
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
J

JP

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

Richard Buttrey said:
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
__________________________
Click to expand...
 
R

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
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
Top