two part function to output into separate cells?
Hi,
Im using Numbers 4 to fill in my time sheet. I've got formulas set up to do most of my calculations, but one is still giving me trouble.
I have three totals, Total Hours (all hours I was at work), Regular hours (all hours minus breaks), Overtime hours (all hours over 40 per week).
I have it set up where total hours and total break time is already added up for me. I now want formulas to do the rest.
Im looking for a formula that will subtract break hours from total hours, and output the result only if less than 40. if greater than 40, I want it to output 40, and output the excess into another cell.
I have figured out how to have the regular hours cell output anything less than 40, and if greater to output 40, but I do not know how to get the amount over the limit to output to another cell.
I am doing that first part like this:
IF((B2−C2)<40, B2−C2, 40)
so, if:
B2=43 and C2=4, RESULT=39
B2=43 and C2=2, RESULT=40
In that second example, how would I get that extra 1 to output to another cell?
Thanks in advance!
MacBook Pro (15-inch, Late 2016), macOS High Sierra (10.13.3)