Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
Sum up columns in different sheet with error check | Excel Discussion (Misc queries) | |||
Summary of multiple spreadsheets: | Excel Worksheet Functions | |||
Identify repeated cell entries in multiple sheet workbook as you . | Excel Discussion (Misc queries) | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) |