Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with formula
We have a spreadsheet that shows each day worked and OT worked as the
columns (14 Columns, Sun-Sat + OT) and employees as the rows. We have a Reg Total (P) that will show regular hours worked up to 40 and OT hours worked (Q) that will show any hours worked over 40. Example, if employee A worked 45 hours during the week (added up from B-0) then I want P to only show 40 and Q to show 5. Also if employee B only works 37 hours, I want P to show 37 and Q to show zero. What formulas can I use in these two cells to get the info that I need? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with formula
Well, first of all, working 7 days a week, over a two week period, should
give more than 80 hours, never mind more than 40? Do you want to calculate per fortnight, or do you want to calculate per week? If the first, then use (in P) something like =IF(SUM(B2:O2)=0,"",IF(SUM(B2:O2)40,40,SUM(B2:O2) )) Just make sure about the 40/80 story. In Q2, enter the following formula =IF(SUM(B2:O2)=0,"",IF(SUM(B2:O2)40,SUM(B2:O2)-40,0)) I take it you have quantities in B:O, formatted as General, or as Number -- Hth Kassie Kasselman Change xxx to hotmail " wrote: We have a spreadsheet that shows each day worked and OT worked as the columns (14 Columns, Sun-Sat + OT) and employees as the rows. We have a Reg Total (P) that will show regular hours worked up to 40 and OT hours worked (Q) that will show any hours worked over 40. Example, if employee A worked 45 hours during the week (added up from B-0) then I want P to only show 40 and Q to show 5. Also if employee B only works 37 hours, I want P to show 37 and Q to show zero. What formulas can I use in these two cells to get the info that I need? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with formula
On Mar 10, 11:23*am, Kassie wrote:
Well, first of all, working 7 days a week, over a two week period, should give more than 80 hours, never mind more than 40? Do you want to calculate per fortnight, or do you want to calculate per week? If the first, then use (in P) something like =IF(SUM(B2:O2)=0,"",IF(SUM(B2:O2)40,40,SUM(B2:O2) )) Just make sure about the 40/80 story. In Q2, enter the following formula =IF(SUM(B2:O2)=0,"",IF(SUM(B2:O2)40,SUM(B2:O2)-40,0)) I take it you have quantities in B:O, formatted as General, or as Number -- Hth Kassie Kasselman Change xxx to hotmail " wrote: We have a spreadsheet that shows each day worked and OT worked as the columns (14 Columns, Sun-Sat + OT) and employees as the rows. *We have a Reg Total (P) that will show regular hours worked up to 40 and OT hours worked (Q) that will show any hours worked over 40. Example, if employee A worked 45 hours during the week (added up from B-0) then I want P to only show 40 and Q to show 5. *Also if employee B only works 37 hours, I want P to show 37 and Q to show zero. What formulas can I use in these two cells to get the info that I need?- Hide quoted text - - Show quoted text - Thank you! That worked like a charm. It is actually only one week, but there were 14 columns because we had one column for reg hours and one column for OT hours for each day of the week. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with formula
Which means you can now reduce it to 7 columns? Glad I could help!
-- Hth Kassie Kasselman Change xxx to hotmail " wrote: On Mar 10, 11:23 am, Kassie wrote: Well, first of all, working 7 days a week, over a two week period, should give more than 80 hours, never mind more than 40? Do you want to calculate per fortnight, or do you want to calculate per week? If the first, then use (in P) something like =IF(SUM(B2:O2)=0,"",IF(SUM(B2:O2)40,40,SUM(B2:O2) )) Just make sure about the 40/80 story. In Q2, enter the following formula =IF(SUM(B2:O2)=0,"",IF(SUM(B2:O2)40,SUM(B2:O2)-40,0)) I take it you have quantities in B:O, formatted as General, or as Number -- Hth Kassie Kasselman Change xxx to hotmail " wrote: We have a spreadsheet that shows each day worked and OT worked as the columns (14 Columns, Sun-Sat + OT) and employees as the rows. We have a Reg Total (P) that will show regular hours worked up to 40 and OT hours worked (Q) that will show any hours worked over 40. Example, if employee A worked 45 hours during the week (added up from B-0) then I want P to only show 40 and Q to show 5. Also if employee B only works 37 hours, I want P to show 37 and Q to show zero. What formulas can I use in these two cells to get the info that I need?- Hide quoted text - - Show quoted text - Thank you! That worked like a charm. It is actually only one week, but there were 14 columns because we had one column for reg hours and one column for OT hours for each day of the week. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|