Forum Discussion

spinaz007's avatar
spinaz007
Copper Contributor
Dec 28, 2023

How to check which date range an entered date falls between

Hi, 

I have the following table below. 
As shown below, I would like to user to enter the Part Number "P/N" (in B2) and Date Built (in B3) on Sheet 1.  Based on this data, I want it to look up and populate the correct revision (Rev) number in B4.  Currently, I am using the following formula (in B4) which returns Rev 10 as shown.  =IF(AND(B3>=D8,B3<=E8),C8,IF(AND(B3>=D9,B3<=E9),C9,IF(AND(B3>=D10,B3<=E10),C10,IF(AND(B3>=D11,B3<=E11),C11,""))))
This formula is currently not dependent on the P/N and just checks the date range for P/N 12345678, as called out in the formula.  Since there is overlap between the date ranges for the different part numbers, I need for it to only check the dates listed for the P/N entered, and then return the Revision according to which date range for that part number the entered Date Built matches. 
For this example, Since P/N 12345678 was entered, it would only check the date ranges D8:E11.  Then, it would need to compare the Date Built that was entered (12/28/2023 in B3) with the dates in that range (D8:E11) to see which it fell between.  In this case, that would be the range D8:E8.  Therefore, it should populate Rev 10.  If however the P/N entered was 12345679, then it should populate Rev 6.
Thanks for any help on this!

 

 

 

 

excel

11 Replies

Sort By
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Dec 28, 2023

    spinaz007 

    As suggested by OliverScheurich but written in a way that merges with my general spreadsheet presentation style

    = LET(
    inDate?,     BYROW(dateRange<built, XORλ),
    filled,      SCAN("", partNo, FILLDOWNλ),
    validParts,  IF(inDate?, filled),
    XLOOKUP(part, validParts, revision, "Not available")
  )

    where

    FILLDOWNλ(p, q)
= IF(q <> "", q, p)

XORλ(v)
= XOR(v)
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Dec 28, 2023

    spinaz007 

    =LET(filldown,

    SCAN("",A8:A21,LAMBDA(ini,arr,IF(arr<>"",arr,ini))),

    revstartend,

    FILTER(C8:E21,filldown=B2),

    FILTER(CHOOSECOLS(revstartend,1),(CHOOSECOLS(revstartend,2)<=B3)*(CHOOSECOLS(revstartend,3)>=B3))

    )

     

    This is an alternative with Office 365 or Excel for the web.

    • spinaz0070's avatar
      spinaz0070
      Copper Contributor
      Dec 29, 2023

      OliverScheurich 

      Thanks so much for this fix.  It works great!
      Can you walk me through how this works? 
      Also, is there a way to modify this formula so that if the Date Built isn't within a date range, or no part number is entered, the Rev field is blank instead of the current #Calc?

      Really appreciate the help!

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Dec 29, 2023

        spinaz0070 

        An advantage of the formula that I used involving XLOOKUP is that most combinations of missing or invalid data return "Not available" rather than diverse errors.  The formula itself is probably somewhat heavy going for anyone versed in more traditional methods though.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Dec 28, 2023

    spinaz007 

    =INDEX($C$8:$C$21,MATCH(1,($D$8:$D$21<=$B$3)*(E8:$E$21>=B3)*($F$8:$F$21=$B$2),0))

     

    You can apply this formula along with a helper column. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

     

    =IF(A8<>"",A8,F7)

     

    This is the formula in cell F8 for the helper column.

     

    If you work with Office 365 or Excel for the web you can apply SCAN and LAMBDA within one formula and a helper column isn't required.

     

Resources