 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008need 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? 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  SignatureHth Kassie Kasselman Change xxx to hotmail > 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 [quoted text clipped - 7 lines] > What formulas can I use in these two cells to get the info that I > need? > 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? [quoted text clipped - 29 lines] > > - 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. Which means you can now reduce it to 7 columns? Glad I could help! SignatureHth Kassie Kasselman Change xxx to hotmail > > 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? [quoted text clipped - 33 lines] > 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 to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.