• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

CONCISE NIGHT HOURS TOTAL FORMULA of BREAKS START&FINISH X3 within THRESHOLD? Median/Max lengthy edit|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / CONCISE NIGHT HOURS TOTAL FORMULA of BREAKS START&FINISH X3 within THRESHOLD? Median/Max lengthy edit|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…CONCISE NIGHT HOURS TOTAL FORMULA o…
sp_PrintTopic sp_TopicIcon
CONCISE NIGHT HOURS TOTAL FORMULA of BREAKS START&FINISH X3 within THRESHOLD? Median/Max lengthy edit
Avatar
stephanrs
England/Humberside/Hull
Member
Members
Level 0
Forum Posts: 41
Member Since:
February 28, 2017
sp_UserOfflineSmall Offline
1
November 10, 2021 - 7:16 am
sp_Permalink sp_Print sp_EditHistory

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.

sp_AnswersTopicSeeAnswer See Answer
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
November 11, 2021 - 5:35 am
sp_Permalink sp_Print

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.

sp_AnswersTopicAnswer
Answers Post
Avatar
stephanrs
England/Humberside/Hull
Member
Members
Level 0
Forum Posts: 41
Member Since:
February 28, 2017
sp_UserOfflineSmall Offline
3
November 11, 2021 - 7:28 am
sp_Permalink sp_Print

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.

Avatar
stephanrs
England/Humberside/Hull
Member
Members
Level 0
Forum Posts: 41
Member Since:
February 28, 2017
sp_UserOfflineSmall Offline
4
November 11, 2021 - 1:44 pm
sp_Permalink sp_Print sp_EditHistory

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

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

Cheers again!

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
5
November 12, 2021 - 4:42 am
sp_Permalink sp_Print

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

Avatar
stephanrs
England/Humberside/Hull
Member
Members
Level 0
Forum Posts: 41
Member Since:
February 28, 2017
sp_UserOfflineSmall Offline
6
November 12, 2021 - 8:49 pm
sp_Permalink sp_Print

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

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
7
November 13, 2021 - 3:17 am
sp_Permalink sp_Print

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

Avatar
stephanrs
England/Humberside/Hull
Member
Members
Level 0
Forum Posts: 41
Member Since:
February 28, 2017
sp_UserOfflineSmall Offline
8
November 14, 2021 - 1:49 am
sp_Permalink sp_Print

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. 

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
9
November 14, 2021 - 10:23 pm
sp_Permalink sp_Print

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

Avatar
stephanrs
England/Humberside/Hull
Member
Members
Level 0
Forum Posts: 41
Member Since:
February 28, 2017
sp_UserOfflineSmall Offline
10
November 17, 2021 - 2:02 am
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Chris Warren, Riny van Eekelen
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6214
Posts: 27246

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.