February 28, 2017

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.

VIP

Trusted Members

December 7, 2016

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

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

VIP

Trusted Members

December 7, 2016

February 28, 2017

VIP

Trusted Members

December 7, 2016

February 28, 2017

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

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

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)