+ Reply to Thread
Results 1 to 6 of 6

Summarize uninterrupted time periods

  1. #1
    Registered User
    Join Date
    03-12-2021
    Location
    Vienna
    MS-Off Ver
    2016
    Posts
    6

    Lightbulb Summarize uninterrupted time periods

    Dear all,
    I am looking for the soltion for this problem for some time now!
    Any help is greatly appreciated!

    I have a excel file in which time periods are entered as follows (3 columns):

    Date/ Start/ End
    15.04.2021/09:00/10:00
    15.04.2021/10:00/11:00
    15.04.2021/12:00/13:00
    16.04.2021/13:00/14:00
    16.04.2021/14:00/15:00

    I need a way to automatically summarize the uninterrupted time periods so that it looks like this (3 columns):

    15.04.2021/09:00/11:00
    15.04.2021/12:00/13:00
    16.04.2021/13:00/15:00


    Info:

    Windows Version: Microsoft Windows 10 Pro
    Excel Version: Microsoft Office Standard 2016
    Last edited by Enxel; 04-21-2021 at 04:24 AM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Summarize uninterrupted time periods

    Hi

    Are you looking for a macro or a formula?

    Formula for D2 and then fill down.

    =IF(AND(A2=A1,B2=C1),"",IF(AND(A3=A2,B3=C2),C3,C2))

    Then Copy paste values.

    Sort by column D to delete all empty cells

    Then delete column C.

    Repeat if your number of rows is reduced.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 04-16-2021 at 01:36 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    03-12-2021
    Location
    Vienna
    MS-Off Ver
    2016
    Posts
    6

    Re: Summarize uninterrupted time periods

    Thank you very much!!! The macro works perfectly

  4. #4
    Registered User
    Join Date
    03-12-2021
    Location
    Vienna
    MS-Off Ver
    2016
    Posts
    6

    Re: Summarize uninterrupted time periods

    Thanks again! I have one last question that I forgot to include.

    Woud there be a way for the macro to filter out 00:00 - 00:00 time periods, so that they are not displayed after applying the macro?
    For example:

    15.04.2021/00:00/00:00
    15.04.2021/09:00/10:00
    15.04.2021/10:00/11:00
    15.04.2021/12:00/13:00
    16.04.2021/13:00/14:00
    16.04.2021/14:00/15:00
    16.04.2021/00:00/00:00

    ->

    15.04.2021/09:00/11:00
    15.04.2021/12:00/13:00
    16.04.2021/13:00/15:00

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Summarize uninterrupted time periods

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-12-2021
    Location
    Vienna
    MS-Off Ver
    2016
    Posts
    6

    Re: Summarize uninterrupted time periods

    Thank you !

+ 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. detect uninterrupted sequences of 2,3,4,5,6,7,8,9 ... numbers.
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 01-11-2021, 02:21 PM
  2. Finding uninterrupted sub-arrays in Excel - Kadane's algorithm variation?
    By dan_and in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-22-2020, 01:12 PM
  3. Formula for defining time spent in specified time periods
    By SuneLolk in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-21-2019, 08:01 PM
  4. Calculate time by time periods splitting productivity
    By cgfourman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2014, 03:04 PM
  5. finding uninterrupted group of cells containg data - like autoSUM
    By flatflo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-28-2012, 05:34 AM
  6. time durations within time periods (greater than/less than?)
    By rosieb13 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-08-2007, 10:38 AM
  7. [SOLVED] periods of time
    By Ben in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-05-2006, 03:47 PM

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