Forum Discussion
HELP with Multiple Work Hours calculations
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.
Hi Peter,
Thank you. Apologies, I had opened the sheet but not downloaded to see the formula.
I am sure you will be extremely frustrated with my next question, but...
How do I transfer this to my sheet? I have copied the 'Name & Refers To' table onto the sheet I am working on with the formula {= Duration - Break} into the cells where breaks need to be removed but I am sure I am missing a huge step. The calculation doesn't occur.
Again, apologies, my excel knowledge is basic and all self or forum taught.
Actually, I think I have done it . but using:
=A1-IFERROR(1/LOOKUP(A1,{0,0.25,0.5},{0,48,24}),0)
I have literally copy and pasted that into the cells (changing A1 to the cell it needs to come from) and it works.
So that I am actually learning what I am doing, and in case I need to change the break times and worked hours times, what does the formula mean in how it is constructed?