Multiple Conditional summing for time sheet.
 

I am creating a timesheet template and have it all figured out except this
one column. Here is what I would like to do:
If column AE4(Actual hours worked) is greater than 168 than add one until
171 is reached. Or if column AE4(Actual hours worked) is less than 168 but
cloumn
AJ4(Total time) is greater than 168 than AJ4-168.

This is hard to come up with a function that fits. Here is the case in
simple terms. The first three hours over 168 go to comp time than anything
exceeding 171 hours go to overtime. Also, if actual hours worked is less
then 168 but total time exceeds 168 than anything over 168 goes to comp time.
It hurts my head to even write this let alone understand it. Thanks for anyt

Multiple Conditional summing for time sheet.
 

where does this formula go?What scenario end in act time < 168 but total
time168?.Would the new formula be actually two columns? ie overtime and comp
time?Are these two times smehow added back into the total time so leading to
the scenario above?
--
paul

remove nospam for email addy!



"JP" wrote:

I am creating a timesheet template and have it all figured out except this
one column. Here is what I would like to do:
If column AE4(Actual hours worked) is greater than 168 than add one until
171 is reached. Or if column AE4(Actual hours worked) is less than 168 but
cloumn
AJ4(Total time) is greater than 168 than AJ4-168.

This is hard to come up with a function that fits. Here is the case in
simple terms. The first three hours over 168 go to comp time than anything
exceeding 171 hours go to overtime. Also, if actual hours worked is less
then 168 but total time exceeds 168 than anything over 168 goes to comp time.
It hurts my head to even write this let alone understand it. Thanks for anyt

Multiple Conditional summing for time sheet.
 

JP,
This is what I came up with:
AE AF AG
AH
Actual
Hours
Worked Comp Time OverTime Hours Total Time
4| 172 3 1 173.5

Cell AE4 =172
CellAF4=IF(AE4168,IF(AE4-168,3,AE4-168),0)
CellAG4=IF(AE4171,AE4-171,0)
CellAH4=SUM(AE4,(AG4*1.5))

Hope thisa helps, good luck!
:-)
"paul" wrote:

where does this formula go?What scenario end in act time < 168 but total
time168?.Would the new formula be actually two columns? ie overtime and comp
time?Are these two times smehow added back into the total time so leading to
the scenario above?
--
paul

remove nospam for email addy!



"JP" wrote:

I am creating a timesheet template and have it all figured out except this
one column. Here is what I would like to do:
If column AE4(Actual hours worked) is greater than 168 than add one until
171 is reached. Or if column AE4(Actual hours worked) is less than 168 but
cloumn
AJ4(Total time) is greater than 168 than AJ4-168.

This is hard to come up with a function that fits. Here is the case in
simple terms. The first three hours over 168 go to comp time than anything
exceeding 171 hours go to overtime. Also, if actual hours worked is less
then 168 but total time exceeds 168 than anything over 168 goes to comp time.
It hurts my head to even write this let alone understand it. Thanks for anyt