Formula to surface specified data and date from project task

Vivien Chong

I am trying to to surface to latest EOT from the task to row 1 on EOT Stage, and respectively the corresponding End Date to EOT End Date row 1.

We may state EOT 1 - 5 on the project sheet, but it will only take effect the date is filled in. Therefore, the latest EOT need not necessary be 5. But in the following example, I am taking EOT 5 as the latest.

Would appreciate assistance.

Gia Thinh

Viviven, try these formulas :

EOT No column:

=IF([EOT End Date]@row <> "", INDEX(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""), MATCH([EOT End Date]@row, COLLECT([End Date]:[End Date], Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""), 0)))

EOT End Date:

=MAX(COLLECT([End Date]:[End Date], Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""))

Gia Thinh

Hi @Vivien Chong

Try the formulas below. Hope it works for you.

EOT No column:

=INDEX(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""), COUNT(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> "")))

EOT End Date:

=INDEX(COLLECT([End Date]:[End Date], Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""), COUNT(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> "")))

Vivien Chong

Hi @Gia Thinh thanks for your prompt response. I copied your formula but there seem to be an error:

I have verified that the column names are correct and I am wondering why it doesn't reflect the correct data as what you have shown.

Gia Thinh

In my formula has 4 referenced columns. Ensure you have the same column names as in the formula or you can change the formula to match with your existing column names.

Vivien Chong

@Gia Thinh I am having the same column name as yours

Gia Thinh

Just shared my demo sheet with you. Check it out.

Gia Thinh

I have looked into your issue and there are 2 things :

1- You need to enter date into End Date to make the formula works

2- Modify the formulas as below:

EOT No column:

=IF(COUNT(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> "")) <> 0, INDEX(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""), COUNT(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""))))

EOT End Date:

=IF(COUNT(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> "")) <> 0, INDEX(COLLECT([End Date]:[End Date], Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""), COUNT(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""))))

Vivien Chong

Thanks @Gia Thinh , thanks for pointing out the error. Yes, indeed one important information is that the EOT may be blank if there is no need for EOT.

I am thinking are we able to simplify the formula to the following?

1. EOT No: to surface the EOT No corresponding to the maximum End Date
2. EOT End Date: to surface the maximum end date with task containing "EOT No."

Gia Thinh

Viviven, try these formulas :

EOT No column:

=IF([EOT End Date]@row <> "", INDEX(COLLECT(Task:Task, Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""), MATCH([EOT End Date]@row, COLLECT([End Date]:[End Date], Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""), 0)))

EOT End Date:

=MAX(COLLECT([End Date]:[End Date], Task:Task, CONTAINS("EOT No.", @cell), [End Date]:[End Date], @cell <> ""))