Forum Discussion

JVladev's avatar
JVladev
Copper Contributor
Apr 23, 2024

Display start and end of tasks

Hello, If anyone can help, I have a table of tasks and a row showing days, and below that row are rows with the completion of tasks, which is marked with an "x". How to display information in column...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Apr 24, 2024

    JVladev 

    Sorry, I was careless. Try these (again confirmed by pressing Ctrl+Shift+Enter):

     

    =IF(COUNTIF(D5:AQ5, "x"), INDEX($3:$3, MIN(IF(D5:AQ5="x",COLUMN(D5:AQ5)))), "")

     

    =IF(COUNTIF(D5:AQ5, "x"), INDEX($3:$3, MAX(IF(D5:AQ5="x",COLUMN(D5:AQ5)))), "")

MAngosto's avatar
MAngosto
Iron Contributor
Apr 23, 2024

JVladev 

 

Hello!

 

Use this on B5 (Start date):

=XLOOKUP("X",D5:AQ5,D3:AQ3,,,1)

 

On C5 (Finish date) use this:

=XLOOKUP("X",D5:AQ5,D3:AQ3,,,-1)

 

Change cell (absolute) references accordingly and drag down each of these. Also change "X" based on if it is in lower or upper case.

Hope it works for you.

 

Martin

 

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Apr 23, 2024

    MAngosto 

    If your formula doesn't cut it with the OP, this can only be worse :sad:

    = LET(
    MINIFXλ, LAMBDA(task, MINIFS(day, task, "x")),
    MAXIFXλ, LAMBDA(task, MAXIFS(day, task, "x")),
    HSTACK(
      BYROW(active, MINIFXλ),
      BYROW(active, MAXIFXλ)
    )
  )
  • JVladev's avatar
    JVladev
    Copper Contributor
    Apr 23, 2024

    MAngosto Thanks, but I'll have to look for another function because I don't have XLOOKUP. I tried with LOOKUP it displays the end day, but if I do more than one break and it doesn't display the end day correctly.

    If you have any idea how to adjust the function or other function or combination of functions to output the correct start and end days.

Resources