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.

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.

Hello.  File Attached.  Status: Done/Finished/Ready!

Edited rest of workbook (JAN-DEC) with your concise formula, much easier to edit/follow.

Cheers again!

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

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

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

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. 

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

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