+ Reply to Thread
Results 1 to 20 of 20

calculate time frames total without overlaps

  1. #1
    Registered User
    Join Date
    12-16-2016
    Location
    London, England
    MS-Off Ver
    Excel 2017
    Posts
    11

    calculate time frames total without overlaps

    Hello, can anyone help?

    At work we have time frames when work is blocked by system errors.
    What I want to do is calculate the total blocked time without the overlaps in blocker time frames.

    Please have a look at this example picture:
    excel_question.png

    What I want to calculate is E9.

    Thank you very much!

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: calculate time frames total without overlaps

    Please upload a workbook. It is always easier to advise if we can see your request in its context.

    Untested but have you tried in say D2 copied down

    =Min(G$2,B2)-Max($G1,A1)

    Then just sum D2:D7
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-16-2016
    Location
    London, England
    MS-Off Ver
    Excel 2017
    Posts
    11

    Re: calculate time frames total without overlaps

    thank you Richard, but this solution doesn't work

    I have tried your advice, but column D doesnt show the expected (column E) values..

    I have uploaded the excel file though: http://www.file-upload.net/download-...t_01.xlsx.html
    Last edited by happyhippo79; 12-16-2016 at 06:44 PM.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: calculate time frames total without overlaps

    Please upload the workbook to this forum. Many of us avoid file hosting sites whose provenance is uncertain.

    Choose Go Advanced and look below the post for the 'Manage Attachments' area.

  5. #5
    Registered User
    Join Date
    12-16-2016
    Location
    London, England
    MS-Off Ver
    Excel 2017
    Posts
    11

    Re: calculate time frames total without overlaps

    I have followed your advice and uploaded the excel workbook.
    Hope that helps
    Attached Files Attached Files

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: calculate time frames total without overlaps

    It will if you make G1 & G2 proper time numbers. At the moment they're just text. i.e. 08:00:00 and 16:30:00

    There was a typo in the formula $G1 should of course have been an absolute. i.e. $G$1

  7. #7
    Registered User
    Join Date
    12-16-2016
    Location
    London, England
    MS-Off Ver
    Excel 2017
    Posts
    11

    Re: calculate time frames total without overlaps

    Hello Richard, thank you for your comment. It still doesn't do the trick.
    If I get it right, your formula only calculates the overlaps with "start of day" and "end of day" times.
    But I also wanted to calculate the overlaps of every time frame and subtract it from the total blocker time in order to get the "real" blocked time (without overlaps).
    I have included the File Test_02.xlsx in order to make more obvious what I want to calculate.
    Attached Files Attached Files

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: calculate time frames total without overlaps

    Hi,

    Leave the formula I gave you in D2, (the first data row) and in D3 copied down

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-16-2016
    Location
    London, England
    MS-Off Ver
    Excel 2017
    Posts
    11

    Re: calculate time frames total without overlaps

    WOW Richard. It works! You're officially my new hero!
    Thank you so much for your kind advice and your patience with an excel amateur

  10. #10
    Registered User
    Join Date
    12-16-2016
    Location
    London, England
    MS-Off Ver
    Excel 2017
    Posts
    11

    Re: calculate time frames total without overlaps

    Okay,the two formulas are working under certain conditions.
    But unfortunately they don't work when a) the second timeframe starts earlier than the first one or b) ends earlier than the first one.
    Can anyone help to solve that problem?
    (I have uploaded the file Test_03.xlsx to demonstrate the problem)
    Attached Files Attached Files

  11. #11
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: calculate time frames total without overlaps

    Does this correction in D3 work?
    Or to be strictly correct since I know it works in the example given, does it work with other permutations of data or does it still need tweaking?

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    12-16-2016
    Location
    London, England
    MS-Off Ver
    Excel 2017
    Posts
    11

    Re: calculate time frames total without overlaps

    Hello Richard, thank you so much for you quick reply!
    I have found one more permutation where the formula doesn't work: When the second timeframe starts earlier AND ends later than the first timeframe..
    (I've included the file Test_04.xlsx)

    correction: I have found that the formula doesn't work in cell D2-D6 (sorry for my confusion) Explanation: The correct results (the ones I want to get) are displayed in column E.
    (I've included the file Test_06.xlsx)
    Attached Files Attached Files
    Last edited by happyhippo79; 01-28-2017 at 02:42 PM.

  13. #13
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: calculate time frames total without overlaps

    ..You forgot to attach the file

  14. #14
    Registered User
    Join Date
    12-16-2016
    Location
    London, England
    MS-Off Ver
    Excel 2017
    Posts
    11

    Re: calculate time frames total without overlaps

    sorry for my mistake. I'll try to attach again!
    Attached Files Attached Files

  15. #15
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: calculate time frames total without overlaps

    I'm confused.

    What are the actual results you want? I was under the impression that the results are in column C and that column E is the difference that existed at some previous time.

    Are you saying that the times currently showing in in column E are the results you expect?

  16. #16
    Registered User
    Join Date
    12-16-2016
    Location
    London, England
    MS-Off Ver
    Excel 2017
    Posts
    11

    Re: calculate time frames total without overlaps

    Yes, that's exactly what I am saying
    Column D is supposed to display the contents of column E. That's what I need the automatic correction for. I'm sorry if I didn't express that clear enough. My English is very bad...

  17. #17
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: calculate time frames total without overlaps

    OK,

    Try in D3 copied down

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    12-16-2016
    Location
    London, England
    MS-Off Ver
    Excel 2017
    Posts
    11

    Re: calculate time frames total without overlaps

    WOW. Richard, you are the BEST! I cannot thank you enough for your help and patience!

  19. #19
    Registered User
    Join Date
    12-16-2016
    Location
    London, England
    MS-Off Ver
    Excel 2017
    Posts
    11

    Re: calculate time frames total without overlaps

    Richard, I'm sorry to bother you again. The formula you gave me is almost perfect, but something doesn't work

    It displays the wrong time in D3, when the second timeframe starts earlier and ends later than the first timeframe... Cell D3 is supposed to show the content of E3.
    (I've included the file Test_07.xlsx in order to show what's happening in D3)
    Attached Files Attached Files
    Last edited by happyhippo79; 01-28-2017 at 09:44 PM.

  20. #20
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: calculate time frames total without overlaps

    Presumably that rule about the start time being earlier than, and the end time being later than on any row, is comparing that rows start & end to ANY previous rows start & end and not just the preceding rows start/end?

    In which case use in D3
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    but please check with other permutations

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 11
    Last Post: 10-24-2016, 08:12 PM
  2. How to count multiple time frames and multiple entires in those time frames
    By laurencubed in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-29-2016, 05:03 PM
  3. Checking for time overlaps against given points in the day
    By alex440000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-27-2016, 01:15 PM
  4. Help in checking for overlaps in time across sheets
    By TMartin79 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-15-2013, 03:42 PM
  5. Number of total calls in desired time frames
    By skate1991 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-31-2013, 11:37 AM
  6. Series of help: including if time overlaps
    By freeurmind in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-01-2013, 06:32 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1