Forum Discussion

KONG84's avatar
KONG84
Copper Contributor
Jun 24, 2022
Solved

FIND OUT START AND END DATE

    Hi, I have been stuck at this part for a while, anyone have any idea to find out the start and end date for item B when it is in Japan? Was thinking minifs+hlookup .....  
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Jun 24, 2022

    KONG84 

     

    With Excel 2021 you can get both dates spilled with a single formula
    - Data formated as Table1
    - Named M3 & M4 respectively CritItem & CritCountry (not mandatory)

     

    in M5:

    =LET(
    ItemCol, INDEX(Table1,,XMATCH(CritIem,Table1[#Headers])),
    IF(COUNTA(CritIem, CritCountry) < 2, "",
        CHOOSE(SEQUENCE(2),
            MINIFS(Table1[Date], ItemCol, CritCountry),
            MAXIFS(Table1[Date], ItemCol, CritCountry)
        )
    )
)

     

Lorenzo's avatar
Lorenzo
Silver Contributor
Jun 24, 2022

Hi KONG84 

(Your sheet setup isn't ideal. Would be better if formatted as Table)

Thanks

  • KONG84's avatar
    KONG84
    Copper Contributor
    Jun 24, 2022

    Lorenzo 
    Hi, I’m using latest 2021 version excel.

    This is a draft data as my company data is confidential but my actual data format is the same as what I posted on the forum, just that the name is changed. I will take your useful advice to set up in table form, but there are 100 items to track so I still need the function to help me work on it . To clarify, start date is earliest date and end date is latest date. 

     

     

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Jun 24, 2022

      KONG84 

       

      Good point from Patrick2788. If Dynamic Arrays not available, the following work as regular formulas (Enter only) with Excel >/= 2010

       

      Start date:

      =AGGREGATE(15,6,
    Table1[Date] / ((Table1[[#Headers],[A]:[I]]=CritIem) * (Table1[[A]:[I]]=CritCountry)),
    1
)

      End date:

      =AGGREGATE(16,6,
    Table1[Date] / ((Table1[[#Headers],[A]:[I]]=CritIem) * (Table1[[A]:[I]]=CritCountry)),
    1
)
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Jun 24, 2022

      KONG84 

       

      With Excel 2021 you can get both dates spilled with a single formula
      - Data formated as Table1
      - Named M3 & M4 respectively CritItem & CritCountry (not mandatory)

       

      in M5:

      =LET(
    ItemCol, INDEX(Table1,,XMATCH(CritIem,Table1[#Headers])),
    IF(COUNTA(CritIem, CritCountry) < 2, "",
        CHOOSE(SEQUENCE(2),
            MINIFS(Table1[Date], ItemCol, CritCountry),
            MAXIFS(Table1[Date], ItemCol, CritCountry)
        )
    )
)

       

      • KONG84's avatar
        KONG84
        Copper Contributor
        Jun 26, 2022
        Can you explain ItemCol refer to which cell? Thanks

Resources