Results 1 to 4 of 4

Thread: Date Lookup
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2015
    Location
    Ohio
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Date Lookup

    Hello,

    [Sheet 1] as two columns. Column A [ID] and Column [B] TIME
    [Sheet 2] as two columns. Column A [ID] and Column [B] TIME

    [Sheet 2] holds all historical date for every ID and every occurrence in time that the ID performed a specific action.

    1. What I am trying to create in [Sheet 1].[Column C] is to enter a formula to ask what is the closest date/time in Sheet 2 without being greater than date/time in Sheet 1 for each ID / TIME combination found on Sheet 1.

    2. What I am trying to create in [Sheet 1].[Column D] is to enter a formula to ask what is the closest date/time in Sheet 2 with is greater than date/time in Sheet 1 for each ID / TIME combination found on Sheet 1.


    [Sheet 1]
    ID TIME Closest Prior to Column B
    Closest After Column B
    123
    12/13/2017 8:15 AM
    10/15/2017 3:33 PM 12/13/2017 8:20 AM
    123
    12/25/2017 11:45 PM
    12/13/2017 8:20 AM 12/26/2017 6:45 AM
    789
    12/1/2017 9:30 AM
    12/1/2017 8:30 AM 12/1/2017 10:30 AM

    [Sheet 2]
    ID TIME
    123
    12/13/2017 8:20 AM
    123
    12/26/2017 6:45 AM
    123
    8/20/2017 10:25 PM
    123
    7/15/2017 10:50 AM
    123
    10/15/2017 3:33 PM
    789
    12/1/2017 10:30 AM
    789
    12/1/2017 8:30 AM
    789
    12/2/2017 10:45 PM

  2. #2
    Board Regular
    Join Date
    Jun 2015
    Location
    Tillamook, OR
    Posts
    2,118
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Date Lookup

    Perhaps these:

    Col C
    =MAXIFS(Sheet2!$B$2:$B$9,Sheet2!$A$2:$A$9,A2,Sheet2!$B$2:$B$9,"<"&B2)

    Col D
    =MINIFS(Sheet2!$B$2:$B$9,Sheet2!$A$2:$A$9,A2,Sheet2!$B$2:$B$9,">"&B2)

    If you have Excel 2016. May need some alteration for older versions.

    For older versions:

    Col C
    =MAX(IF(Sheet2!$A$2:$A$9=A2,IF(Sheet2!$B$2:$B$9<B2,Sheet2!$B$2:$B$9)))
    Col D
    =MIN(IF(Sheet2!$A$2:$A$9=A2,IF(Sheet2!$B$2:$B$9>B2,Sheet2!$B$2:$B$9)))

    These are entered as array formulas with CTRL+SHIFT+ENTER, not just ENTER.
    Last edited by dreid1011; Feb 27th, 2018 at 11:37 AM.
    Win 7:MS Excel 2010

    Excel Worksheet Functions (TechOnTheNet)
    Excel Worksheet Functions (ExcelJet) Excel Formulas (ExcelJet)

    Post a copy of your data with one of the following methods:
    Excel Jeanie, MrExcel HTML Maker, Tableizer!

  3. #3
    Board Regular
    Join Date
    Aug 2015
    Location
    Ohio
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date Lookup

    Second one works good! thank you. wow...so easy.

  4. #4
    Board Regular
    Join Date
    Jun 2015
    Location
    Tillamook, OR
    Posts
    2,118
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Date Lookup

    Glad to be of service. You're welcome.
    Win 7:MS Excel 2010

    Excel Worksheet Functions (TechOnTheNet)
    Excel Worksheet Functions (ExcelJet) Excel Formulas (ExcelJet)

    Post a copy of your data with one of the following methods:
    Excel Jeanie, MrExcel HTML Maker, Tableizer!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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