Log In
Register
Home
February 28, 2017
Offline
Hello. File attached, see 2nd tab JAN.
Created HOURS XL tracker per MTH, since in UK paid breaks isn't obligatory, added Break Times per threshold hours to be subtracted from Worked Hours.
Night Hours Threshold is defined to Start: JAN: AH17 & Finish JAN: AH20.
for specific example, COLUMN Q (NIGHT HOURS BREAK TOTAL) & COLUMN R (DAY HOURS BREAK TOTAL) from Breaks: Columns K / L / M / N / P
CELL Q2:
=IF(K2>0,(K2>L2)*MEDIAN(0,L2-$AH$17,$AH$20-$AH$17)+MAX(0,MIN($AH$20,L2+(K2>L2))-MAX($AH$17,K2))+(M2>N2)*MEDIAN(0,N2-$AH$17,$AH$20-$AH$17)+MAX(0,MIN($AH$20,N2+(M2>N2))-MAX($AH$17,M2))+(O2>P2)*MEDIAN(0,P2-$AH$17,$AH$20-$AH$17)+MAX(0,MIN($AH$20,P2+(O2>P2))-MAX($AH$17,O2)),"")
CELL R2:
=IF(K2>0,MOD(L2-K2,1)-((K2>L2)*MEDIAN(0,L2-$AH$17,$AH$20-$AH$17)+MAX(0,MIN($AH$20,L2+(K2>L2))-MAX($AH$17,K2)))+MOD(N2-M2,1)-((M2>N2)*MEDIAN(0,N2-$AH$17,$AH$20-$AH$17)+MAX(0,MIN($AH$20,N2+(M2>N2))-MAX($AH$17,M2)))+MOD(P2-O2,1)-((O2>P2)*MEDIAN(0,P2-$AH$17,$AH$20-$AH$17)+MAX(0,MIN($AH$20,P2+(O2>P2))-MAX($AH$17,O2))),"")
QUESTION: If there a more concise formula then MOD/MEDIAN to calc breaks total? Threshold to be also considered AK17 & AK20.
So far spreadsheet is 99% done, majority of cells are COMBO BOX or FORMULA CALC, but before apply style seen in tab JAN to rest of mths FEB-DEC, intrigued if more efficient user friendly formula applicable for total of breaks.
See Answer
VIP
Trusted Members
December 7, 2016
Offline
Hello,
If you use 00:00 instead of 24:00 you can simplify the formulas, see row 42 for such an example. The cells with blue background colour are the ones containing formulas.
Br,
Anders
P.S. I have just focused on the time calculations, so thus skipping most of the other conditional rules such as checking if J has a value or not.
Answers Post
February 28, 2017
Offline
Hello Anders, exactly brilliant, thanks much more concise! Guess only refers end threshold because after midnight is night hours?!
When applied to rest of sheet 1ST TAB (JAN), corresponds with previous, great cheers.
Q2: NIGHT BREAK TOTAL
=SUMPRODUCT((L2-K2)*(L2<=$AH$20)+(N2-M2)*(M2<=$AH$20)+(P2-O2)*(P2<=$AH$20))
R2: DAY BREAK TOTAL
=SUMPRODUCT((L2-K2)*(L2>$AH$20)+(N2-M2)*(M2>$AH$20)+(P2-O2)*(P2>$AH$20))
Many thanks, much appreciated. File attached for others with similar enquiry to peruse.
February 28, 2017
Offline
Hello. File Attached. Status: Done/Finished/Ready!
Edited rest of workbook (JAN-DEC) with your concise formula, much easier to edit/follow.
Cheers again!
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Great that it works as you want. Yes, as your night hours start from midnight and you only have time values it’s enough to check if time is greater or less than end threshold.
Br,
Anders
February 28, 2017
Offline
Hi Anders
2 more questions, your short-hand formula edit if was just :
Q1: BREAK1 START & END, for Row2?
Q2 BREAK1&2 START & END, for Row3?
Tried to edit from Break 1 2 3 but couldn't construct it right!
Cheers
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Not sure I really understand what you are asking, when checking your latest uploaded file from post no:4 I don't see any errors/issues. Would be great if you can examplify your question using some data.
Br,
Anders
February 28, 2017
Offline
Hello Anders
Your right there are no errors or issues with formula you provided or the worksheet.
Just was asking for a variation of Formula construct with threshold into day/night hours.
So instead of 3 Breaks, wanted example for:
2 Breaks
1 Break.
Tried to edit your formula down from 3 instances (x3 breaks) to:
2 instances
1 instance
But without success!
Hence the question.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
I did notice an error in the formula I provided. Change the M in red below to N, and you have a correct formula.
Night: =SUMPRODUCT((L2-K2)*(L2<=$AH$20)+(N2-M2)*(M2<=$AH$20)+(P2-O2)*(P2<=$AH$20))
Day: =SUMPRODUCT((L2-K2)*(L2>$AH$20)+(N2-M2)*(M2>$AH$20)+(P2-O2)*(P2>$AH$20))
The above formula simply assumes that if end time is greater than 6 AM then so is the start time. Likewise, if the end time is less than 6 AM, then so is the start time. In the simplest form. See bottom part of this post for an extended scenario.
---------------------------------------
If you still have the same data structure and want to remove the first break then remove the first part of the formula.
Day breaks, 3 instances.
=SUMPRODUCT((L2-K2)*(L2>$AH$20)+(N2-M2)*(N2>$AH$20)+(P2-O2)*(P2>$AH$20))
Day breaks, instance 2 and 3.
=SUMPRODUCT((L2-K2)*(L2>$AH$20)+(N2-M2)*(N2>$AH$20)+(P2-O2)*(P2>$AH$20))
Day breaks, instance 1 and 3.
=SUMPRODUCT((L2-K2)*(L2>$AH$20)+(N2-M2)*(N2>$AH$20)+(P2-O2)*(P2>$AH$20))
The asterisk (*) in this formula is equivalent to AND statement, the plus (+) is equivalent to OR.
So if we translate the formula to English it would be
If the value in L2 is greater than the value in AH20, then subtract the value in L2 (end time) with the value in K2 (start time) so we get the time difference.
And so forth.
---------------------------------------
What this formula will not do is to detect if the break for example starts at 5:50 AM, that is within the night hours interval, and ends 6:20 AM, which is in the day hours interval. You would in such scenario want 10 minutes to be shown in the night hours column (Q) and 20 minutes in day hours column (R). To catch such scenarios you need to extend the formula as below (below formulas only checks break 1 times).
Night: =SUMPRODUCT(
(L42-K42)*(L42<=$AH$20)*(K42<$AH$20)+ <-- Start and End time is less than or equal to 6 AM. Ex. start = 05:45, end = 06:00
(L42-$AH$17)*(L42<$AH$20)*(K42>$AH$20)+ <-- Start time is greater than and End time is less than 6 AM. Ex. start = 23:50, end = 00:20
($AH$20-K42)*(L42>$AH$20)*(K42<$AH$20) <-- Start time is less than and End time is greater than 6 AM. Ex. start = 05:50, end = 06:20
)
Day: =SUMPRODUCT(
(L42-K42)*(L42>$AH$20)*(K42>=$AH$20)+ <-- Start and End time is greater than or equal to 6 AM. Ex. start = 06:00, end: 06:15
(MOD($AH$17-K42;1))*(K42>$AH$20)*(L42<$AH$20)+ <-- Start time is greater than and End time is less than 6 AM. Ex. start = 23:50, end = 00:20
(L42-$AH$20)*(K42<$AH$20)*(L42>$AH$20) <-- Start time is less than and End time is greater than 6 AM. Ex. start = 05:50, end = 06:20
)
As we can see, the more scenarios you want to cover, the more complex the formulas will be. And as stated, these example formulas only cover the start and end times for the first break interval, you need to add rows for the remaining two intervals.
I hope this covers what you had questions about.
Br,
Anders
February 28, 2017
Offline
Hi Anders
I understand your theory and did try previous to deconstruct it to the following for the purposes of 1xbreak, but as mentioned before, the equations were wrong:
NIGHT=SUMPRODUCT((end-start)*(end<=night threshold)
DAY=SUMPRODUCT((end-start)*(end>night threshold)
Appreciate your previous answer explains for 1xbreak version various edits are needed, which wouldn't be practical, but useful to know.
Just think it is odd your 1st suggestion for all 3xbreaks work:
Q2: NIGHT BREAK TOTAL
=SUMPRODUCT((L2-K2)*(L2<=$AH$20)+(N2-M2)*(M2<=$AH$20)+(P2-O2)*(P2<=$AH$20))
R2: DAY BREAK TOTAL
=SUMPRODUCT((L2-K2)*(L2>$AH$20)+(N2-M2)*(M2>$AH$20)+(P2-O2)*(P2>$AH$20))
I wonder why the simpler 1xbreak simple version doesn't? Or is this due to Excel 2003 / 2010?
Cheers
Stephan
1 Guest(s)
