+ Reply to Thread
Results 1 to 7 of 7

Display start time and end time overlapping

  1. #1
    Registered User
    Join Date
    04-11-2022
    Location
    Switzerland
    MS-Off Ver
    2016-2021
    Posts
    3

    Display start time and end time overlapping

    Hi Everybody,

    I'm new on the board.
    I have a problem with ovelapping time and impossible to find a good solution.

    I need to display the start hour and end hour of overlapping.

    Bellow a sample:

    The "What I need" column, is what I need
    The row 2 is the reference time for overlapping.

    The first sample, columns A:E is when the time doesn't exceed midnight and the second sample is when the end time exceed midnight.

    Image 1.png


    I'm started to resolve this problem from this relfexion:
    IF(OR(AND($A$2<=B6;$A$2>=A6);AND(A6<=$B$2;A6>=$A$2)); "Overlapping"; "No overlapping")

    Sorry for my english, it's not my first language.

    Thank you very much for your help.

    JBL

    P.S. This question has been posted on www[.]mrexcel[.]com too.
    Last edited by JBL0703; 04-11-2022 at 11:34 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Display start time and end time overlapping

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    04-11-2022
    Location
    Switzerland
    MS-Off Ver
    2016-2021
    Posts
    3

    Re: Display start time and end time overlapping

    Sorry. Sample attached
    Attached Files Attached Files
    Last edited by JBL0703; 04-11-2022 at 11:33 AM.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Display start time and end time overlapping

    Not very elegant, perhaps someone will improve upon it.
    For columns D:E =IF(OR($A6>=$B$2,$B6<=$A$2),"no overlap",IF(D$5="Start overlap time",MAX(A$2,A6),MIN(A$2,A6)))
    For column J: =IF(AND(G6<H6,G6>=H$2,H6<=G$2),"no overlap",IF(OR(G6>G$2,G6<H$2),G6,IF(OR(H6>G$2,H6<H$2,AND(G6<=G$2,H6>=H$2)),G$2)))
    For column K: =IF(J6="no overlap","no overlap",IF(OR(H6>G$2,H6<H$2),H6,IF(AND(G6<=G$2,H6>=H$2),H$2,H$2)))
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    04-11-2022
    Location
    Switzerland
    MS-Off Ver
    2016-2021
    Posts
    3

    Re: Display start time and end time overlapping

    Thank you very much for your help and your proposition.

    I wrote finally my own solution wich use the same formula for hour that passes midnight and the hour that doesn't passes midnight.

    Image 2.png

    For columns G or P
    Image 4.png

    h1Start = D24 (for the other sample, M24 is nammed h2Start)
    h1End = E24 (and N24 is nammed h2End)

    For columns H or Q
    Image 5.png

    SI() = IF()
    ET() = AND()

    I updated the file with my own solution


    I'm sorry about the formula image, but I can't copy/paste my formulas.
    I have this error when I submit my post
    6.png
    How can I post a formula ?
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Display start time and end time overlapping

    Here is a single formula that will work for either scenario when placed in columns A:B
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Display start time and end time overlapping

    Withdrawn by FR. Didn't have the concept.
    Last edited by FlameRetired; 04-14-2022 at 11:48 AM.
    Dave

+ 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. [SOLVED] Overlapping Time Intervals - Formula to Show Start Overlaps
    By ARec3 in forum Excel General
    Replies: 3
    Last Post: 10-16-2017, 09:13 AM
  2. Autofilter to display results between start time and stop time
    By jpullen88 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-22-2016, 03:35 AM
  3. Replies: 0
    Last Post: 03-04-2016, 06:38 PM
  4. Replies: 4
    Last Post: 03-02-2016, 02:46 AM
  5. Sum overlapping start end time intervals
    By Jack-of-Trades in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 10-15-2014, 12:56 PM
  6. Replies: 3
    Last Post: 03-27-2012, 01:07 PM
  7. display end time automatically upon entering start time and time usage
    By stoey in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-19-2008, 06:16 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