Forum Discussion
HELP with Multiple Work Hours calculations
I am creating a sheet which automatically removes break times when work times of a certain length are completed. I presently can automatically remove 30 mins from a shift of 8 hours with the formula;
=IF(CELL="","",IF(CELL>=1/3,CELL-(1/48),CELL))
However, I would like to build into this formula the following conditions:
- If 13 hours or more are worked, 1 hour is removed
- If 6 hours or less are worked, no time is removed
- if over 6 hours but less than 12 hours are worked, 30 mins are removed.
This all needs to happen in ONE cell. One formula.
I have managed to build the individual formula but cannot merge them together.
I understand that the IF formula is restricted to TRUE and FALSE,
12 Replies
My solution turned out to be the same as Twifoo's . Given that no helper cells are permitted, I packaged the solution within defined names so the final formula was simply
= Duration - Break
'Duration' is just an input to the table and 'Break' is a lookup
= LOOKUP( Shift[@Duration], mandatedBreak )
from the 'MandatedBreak' array {0,0;7,0.5;13,1}/24
Thank you. Apologies if I am being very basic with this, what would be the formula to be entered into the cell?
The formula is already in the Table column 'Hours'. Both 'Break' and 'Duration' are Names that I have given to formulae. 'Duration' is simply a reference to the duration value read from the current record (row). 'Break' uses that value to lookup in the 2D array 'mandatedBreak' the length of the break required for a shift of that length. The formulas are shown in blue on the worksheet but can be examined or changed by using Name Manager.
Note that, as things are, the formula is only correct for round hours. At each threshold value of hours worked there is a step change. To level that out for intermediate working hours requires a more complex formula which tests for such drops.
- Try this:
=A1-LOOKUP(A1,
{0,0.25,0.5},
{0,1/48,1/24})
