Forum Discussion

JVladev's avatar
JVladev
Copper Contributor
Apr 23, 2024
Solved

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 "B" with start day and in column "C" with end day for the given task.
As an example, I am attaching a photo in which the columns in which I want to extract the information with formulas are marked in yellow, and I have given the manually entered data for the first two tasks as an example.

Thanks!

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)))), "")

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 23, 2024

    JVladev 

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

    and

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

    Confirm the formulas by pressing Ctrl+Shift+Enter to turn them into array formulas.

    • JVladev's avatar
      JVladev
      Copper Contributor
      Apr 24, 2024

      HansVogelaar  Thanks!

      It worked for the final day, but it doesn't display correctly for the initial day. I also tried by changing it to not read from the whole 3rd row, but only from the range, but again it's not right.

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        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