+ Reply to Thread
Results 1 to 4 of 4

Find a date time range within start and stop date time?

Hybrid View

  1. 10-30-2017, 08:48 PM #1
    Rodney Fernandes
    Rodney Fernandes is offline
    Registered User
    Join Date
    10-30-2017
    Location
    PAKISTAN
    MS-Off Ver
    MS office 365
    Posts
    1

    Find a date time range within start and stop date time?

    Hi there...Looking for a formula on how can we match data time range within a start and stop date time

    After running the test, I've come to the concluding that the formula works on the last row as the time given is well within the range set but not in case of the others
    Please suggest any other way of doing it

    Start Time Stop Time Range set from Range set to Expected result MRExcel Formula
    9/14/2017 19:07 9/14/2017 20:55 0:00 4:00 No No
    9/15/2017 3:30 9/15/2017 5:36 0:00 4:00 Yes No
    9/15/2017 0:30 9/15/2017 1:09 0:00 4:00 Yes No
    9/15/2017 0:00 9/15/2017 3:59 0:00 4:00 Yes No
    9/14/2017 11:20 9/15/2017 6:36 0:00 4:00 Yes Yes

    =IF(AND(TRUNC(A6)+C6+IF(TRUNC(A6)=TRUNC(B6),0,1)>=A6,TRUNC(A6)+D6+IF(TRUNC(A6)=TRUNC(B6),0,1)<=B6),"Yes", "No")
    Capture.PNG
    Reply With QuoteRegister To Reply
  2. 10-31-2017, 02:03 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,703

    Re: Find a date time range within start and stop date time?

    What do the range from and to numbers represent? Are thay days, hours, what?
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 10-31-2017, 02:06 AM #3
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,725

    Re: Find a date time range within start and stop date time?

    Hi Rodney. Welcome to the forum.

    The approach I used in the attached uses 4 helper columns. They reduce the dates/times to start at day 1 and then express those in integer minutes.

    They are in A2, B2, C2 and D2 the following formulas filled down.
    Formula: copy to clipboard 
    =(INT(E2/MAX($E$2:$G$6))+1+F2)*1440
    Formula: copy to clipboard 
    =(INT(G2/MAX($E$2:$G$6))+1+H2)*1440
    Formula: copy to clipboard 
    =(INT(E2/MAX($E$2:$G$6))+1+I2)*1440
    Formula: copy to clipboard 
    =(INT(G2/MAX($E$2:$G$6))+1+J2)*1440


    Then in K2 and filled down this formula generates two contiguous time arrays (start/end and criteria start/end) and matches them to each other. If there are any matches then "Yes", otherwise "No".
    Formula: copy to clipboard 
    =IFERROR(IF(LOOKUP(1E+306,MATCH(ROW(INDIRECT(A2&":"&B2)),ROW(INDIRECT(C2&":"&D2)),0)),"Yes"),"No")
    Attached Files Attached Files
    Dave
    Reply With QuoteRegister To Reply
  4. 10-31-2017, 02:19 AM #4
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,725

    Re: Find a date time range within start and stop date time?

    This final formula in K2 is simpler and calculates faster.
    Formula: copy to clipboard 
    =IF(SUMIFS(A2:B2,A2:B2,">="&C2,A2:B2,"<="&D2),"Yes","No")
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Given an array with start and stop date and time, allocate total cost proportional to hrs
    By himani25gupta in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2016, 11:05 AM
  2. Given an array with start and stop date and time, allocate total cost proportional to hrs
    By himani25gupta in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2016, 11:02 AM
  3. Given an array with start and stop date and time, allocate total cost proportional to hrs
    By himani25gupta in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2016, 11:02 AM
  4. Calculating total time between start date\time and End date\time excluding non business
    By jonashlock in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-15-2015, 07:32 PM
  5. Formula to Calculate Total time spent between Start Date & Time and End Date & Time
    By Manish_Gupta in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-19-2013, 06:49 AM
  6. Indicate Duplicate with Small Time Span? (ID#, Start Date, Stop Date columns)
    By MacroPolo in forum Excel General
    Replies: 3
    Last Post: 09-26-2012, 09:43 AM
  7. [SOLVED] Calculating days & time left from start date/time to end date/time
    By marie in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-07-2005, 10:40 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1