Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)). |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 __________________________ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 __________________________ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)). |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create a pop-up alert when a condition is reached in excel | Excel Discussion (Misc queries) | |||
How do I run a macro when a pre-determined time has been reached . | Excel Discussion (Misc queries) | |||
Counting blank cells until value is reached | Excel Worksheet Functions | |||
Autofill until blank cell is reached | Excel Discussion (Misc queries) | |||
Run recalc until a specified vaule is reached. | Excel Discussion (Misc queries) |