Best practice for reporting on a date range when sheet summary will not work

I need to report on the latest date in a specific column, with a specific criteria.

I tried using a sheet summary which would be easy to enter a formula, but i need to report on start and end dates in a gantt view and the sheet summary reporting doesn't display the items in a row but rather a column. There is no way that I know of to view this in a Gantt view similar to a regular report?? NOT HELPFUL!

So, I am trying to use a summary at the top of my sheet to then create a report from. Or if there is a better way of doing this please let me know. I could create another sheet but that seems like overkill?

Below is my sheet. I want to find both the earliest & latest date from column "end date" where the criteria equals the task name "ADA Site scope" and then I want it to return that value in another column that I created (a separate formula column in this sheet). These would separate columns for start and end.

Not sure which formula to use?

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Are there multiple instances of "ADA Site Scope" in the lower rows that you are wanting to pull from?

JennS_

Yes, there are many in the task name column. And I want to find the latest date that meets that task.

Paul Newcome

Try a checkbox column with this in it:

=IF(AND(COUNT(ANCESTORS([Task Name]@row)) = 1, OR([End Date]@row = MIN(COLLECT([End Date]:[End Date], [Task Name]:[Task Name], @cell = [Task Name]@row)), [End Date]@row = MAX(COLLECT([End Date]:[End Date], [Task Name]:[Task Name], @cell = [Task Name]@row)))), 1)

This should check the box on those parent rows where the [End Date] is either the earliest or the latest for that particular task name. Is that basically the data you are wanting to pull in to your separate Gantt?

JennS_

I ended up with a blank cell, so it didn't return any value?

=IF(AND(COUNT(ANCESTORS([Task Name]@row)) = 1, OR([End Date]@row = MIN(COLLECT([End Date]:[End Date], [Task Name]:[Task Name], @cell = [Task Name]@row)))), 1)

Screen shot below to show you which column i want to put the formula in called "start dates - report".
and i also expanded the children tasks in case.

JennS_

I could also use the identifier as the "project name" column if that makes it easier?

JennS_

and also, I don't want it to automatically check the box. The boxes are checked when a task is completed. I just need to find a way to look for the earliest and latest date of the "project name" column and return that value in a cell. Then I will make a gantt.

Paul Newcome

I meant to put the formula in a separate checkbox. This is a little more on the complex side, so I am taking it one step at a time. This checkbox is simply to let us know if we have the logic right. If we can check the correct rows, we can surface the data, but surfacing the data adds more to the formula.

But based on your most recent screenshot, the [Task Name] doesn't have the repeating values. It is the [Project Name] column.

If you are using the dependency settings, the parent rows should already have the min/max dates. If you want to show only these rows in a Gantt, you can collapse them in the sheet to only show those rows, or you can create a helper checkbox column with a very basic formula to flag those rows and then create your report to show the rows flagged.

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.