Add one until 171 is reached
 

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

Add one until 171 is reached
 

JP wrote:
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)).

Add one until 171 is reached
 

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

Rgds

On Sun, 15 Oct 2006 15:46:01 -0700, JP
wrote:

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
__________________________

Add one until 171 is reached
 

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

"Richard Buttrey" wrote:

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

Rgds

On Sun, 15 Oct 2006 15:46:01 -0700, JP
wrote:

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
__________________________

Add one until 171 is reached
 

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

Thanks a bunch

JP

" wrote:

JP wrote:
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)).

Add one until 171 is reached
 

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


On Sun, 15 Oct 2006 18:13:01 -0700, JP
wrote:

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

"Richard Buttrey" wrote:

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

Rgds

On Sun, 15 Oct 2006 15:46:01 -0700, JP
wrote:

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
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________