SOLVED

# Display start and end of tasks

Copper Contributor

# 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!

7 Replies

# Re: Display start and end of tasks

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

# Re: Display start and end of tasks

@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.

# Re: Display start and end of tasks

=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.

# Re: Display start and end of tasks

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

``````= LET(
HSTACK(
BYROW(active, MINIFXλ),
BYROW(active, MAXIFXλ)
)
)``````

# Re: Display start and end of tasks

@Hans Vogelaar  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.

best response confirmed by JVladev (Copper Contributor)
Solution

# Re: Display start and end of tasks

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

# Re: Display start and end of tasks

@Hans Vogelaar  Thanks! Excellent

1 best response

Accepted Solutions
best response confirmed by JVladev (Copper Contributor)
Solution

# Re: Display start and end of tasks

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