I need a little help. I have a spreadsheet I have created to keep track of hours worked for two of my crews on a large construction project. It totals their hours on a weekly basis, and then combines their weekly totals at the bottom for a monthly total. I want it break down the overtime hours, and the regular hours, automatically. I am pretty sure this can be done using an if/then statement, but I cannot figure out the correct format. A snapshot of a portion of the sheet is below.
For example, cell B11 shows the weekly total for Field Crew 1. I would like cell B12 to show the overtime hours, in this case. 10.5 hours. I would like B12 to populate automatically as I enter the daily time once the total climbs over 40 hours per week.
Can this easily be done?
Thanks in advance,
Jimmy
Yes, put this in cell B12: =IF(B11>40,B11-40,0)
If(logic test,result if true,result if false)
Thanks Stephen. I was missing the last (,0)
That fixed it.
Jimmy Cleveland, post: 359522, member: 91 wrote: Thanks Stephen. I was missing the last (,0)
That fixed it.
Depending on what version of Excel you're using you can use "insert function" (look for the fx icon) and it will walk you through the expected arguments.
That is if, then, else.
Jimmy Cleveland, post: 359522, member: 91 wrote: Thanks Stephen. I was missing the last (,0)
That fixed it.
If you want the overtime cell to remain blank unless the crew goes over 40 rather than displaying a 0, you can replace the 0 in the formula with "".
Um... = B11 - 40?
Why doesn't that work?
Ah, then you'd have to conditionally format the cell to ignore negative numbers.
Oh well six of one and half a dozen of another...
