Formula to surface specified data and date from project task

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.

Find more posts tagged with

Accepted answers

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

All comments

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:

Screenshot 2024-05-06 at 11.25.12 AM.png

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

Screenshot 2024-05-06 at 12.24.02 PM.png

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?

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

Gia Thinh

Viviven, try these formulas :

EOT No column:

EOT End Date:

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.