+ Reply to Thread
Results 1 to 9 of 9

Identify overlapping events by time/date

  1. #1
    Registered User
    Join Date
    03-10-2021
    Location
    England
    MS-Off Ver
    2013
    Posts
    26

    Identify overlapping events by time/date

    Hi Experts,

    Thanks in advance for any help.

    I have 3 small sets of data. These data refer to 3 different pumps. The data contains a start date/time and an end date/time.

    I need to work out, for each time that pump 3 was running ('event'), if pump 1 and/or pump 2 was running. And if possible, the time period P3 was overlapping for. I.e. if pump 1 ran for and hour at 1pm, and pump 3 ran for 20 minutes at 1:50pm, a value of 10 minutes could be returned.

    Ultimately, the overall pump run times will end up in a chart to show the overlap.

    If you can read between the lines, as I probably am not explaining very well, please feel free to suggest a better way I might find this info. This may be easier with VBA, but I have 0 experience.

    Thanks for reading.
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,582

    Re: Identify overlapping events by time/date

    I suspect there are some flaws in the overlapping logic, but to get things moving...and assuming XL2013

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    edit: the above columns should be format as time, e.g. [hh]:mm:ss or [mm]:ss
    Last edited by XLent; 03-10-2021 at 12:51 PM.

  3. #3
    Registered User
    Join Date
    03-10-2021
    Location
    England
    MS-Off Ver
    2013
    Posts
    26
    Quote Originally Posted by XLent View Post
    I suspect there are some flaws in the overlapping logic, but to get things moving...and assuming XL2013

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    edit: the above columns should be format as time, e.g. [hh]:mm:ss or [mm]:ss

    There could very well be! Thank you so much. I will try this out shortly.

  4. #4
    Registered User
    Join Date
    03-10-2021
    Location
    England
    MS-Off Ver
    2013
    Posts
    26

    Re: Identify overlapping events by time/date

    You're a genius. Thank you so much! Lots of time saved.

  5. #5
    Registered User
    Join Date
    03-10-2021
    Location
    England
    MS-Off Ver
    2013
    Posts
    26

    Re: Identify overlapping events by time/date

    You're a genius. Thank you so much. Lots of time saved.

  6. #6
    Registered User
    Join Date
    03-10-2021
    Location
    England
    MS-Off Ver
    2013
    Posts
    26
    Quote Originally Posted by Excel_NoneTheWiser View Post
    There could very well be! Thank you so much. I will try this out shortly.
    My boss has come back to say hes very impressed, and it was a great solution. Now I can either fairly credit you, or you could explain exactly what that formula is doing and I can get a pay rise?!

  7. #7
    Registered User
    Join Date
    03-10-2021
    Location
    England
    MS-Off Ver
    2013
    Posts
    26
    Quote Originally Posted by XLent View Post
    I suspect there are some flaws in the overlapping logic, but to get things moving...and assuming XL2013

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    edit: the above columns should be format as time, e.g. [hh]:mm:ss or [mm]:ss
    If you have the time, could you possibly explain what the end part of the formula is doing? For my own clarity. "[<0]\0;General")+0)

    I can make sense of the rest, mainly.

    Many thanks.

  8. #8
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,582

    Re: Identify overlapping events by time/date

    the TEXT format is a 'shortcut' approach of removing / altering numbers within an array of values, without doing potentially expensive re-evaluations
    in your case, only those rows with a genuine overlap will generate a +ve result in the array. all other rows should return a negative result to the array

    so, rather than performing another test on the resulting array of values to establish which are positive etc, which could be expensive, we apply a single custom format to the array to get rid of the negatives - i.e.

    [<0]\0;General

    will change any value <0 to a literal 0, and keep the +ve values as general

    at this point (given use of TEXT) the array is now full of numeric strings rather than true numbers, e.g. "0.5" rather than 0.5, so the outer +0 is used to coerce these back to a numeric type, prior to the outer SUM aggregating them

    p.s. good luck with the raise!

  9. #9
    Registered User
    Join Date
    03-10-2021
    Location
    England
    MS-Off Ver
    2013
    Posts
    26
    Quote Originally Posted by XLent View Post
    the TEXT format is a 'shortcut' approach of removing / altering numbers within an array of values, without doing potentially expensive re-evaluations
    in your case, only those rows with a genuine overlap will generate a +ve result in the array. all other rows should return a negative result to the array

    so, rather than performing another test on the resulting array of values to establish which are positive etc, which could be expensive, we apply a single custom format to the array to get rid of the negatives - i.e.

    [<0]\0;General

    will change any value <0 to a literal 0, and keep the +ve values as general

    at this point (given use of TEXT) the array is now full of numeric strings rather than true numbers, e.g. "0.5" rather than 0.5, so the outer +0 is used to coerce these back to a numeric type, prior to the outer SUM aggregating them

    p.s. good luck with the raise!
    Hero. Thanks again. Now I can afford more beer! Hurrah! Top man.

+ 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. How do I identify overlapping date/times
    By jaar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-28-2020, 04:05 PM
  2. Identify and count overlapping date/time entries on a call log
    By Andrew989 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-23-2012, 12:24 PM
  3. charting overlapping events over time
    By joell in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-29-2010, 12:58 PM
  4. Overlapping or Duplicate Date & Time
    By Mike in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  5. Overlapping or Duplicate Date & Time
    By Mike in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  6. [SOLVED] Overlapping or Duplicate Date & Time
    By Mike in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 PM
  7. [SOLVED] Overlapping or Duplicate Date & Time
    By Mike in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM

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