Returning Start and End Dates

Griffin Fujii

Hi All,

I am trying to get the MIN or start date of a range that only grabs from the row with the same phase. Keep in mind these Start and End dates in the project plan below have dependencies enabled.

I would like to return the MIN Start date in the Start Date Metrics column row 3 in image 1. The range is the Actual / Forecast Start Date Column in image 2. I only want the rows that contain the same phase in image 1 "Requirements" in the column Project Phase (FCM Standard) in image 2. I can't get a complete formula to return without an error. I also believe there is a nuance with the dependency date column, I read on another article about using DATEONLY.

If someone could help me with the full formula that would be amazing.

Apologies am a Smartsheet newbie.

Paul Newcome

You cannot use formulas in columns that are referenced in the Dependency Settings. You would need to pull this date into another column to used by the Dependency Settings. The formula to pull the date into another column would be...

=MIN(COLLECT({Other Sheet Date Column}, {Other Sheet Phase Column}, "Requirements"))

Griffin Fujii

@Paul Newcome Thank you so much. If I wanted to only count the ERP Integration (Intacct) child milestones what is the right way to do that?

Paul Newcome

If you are pulling your counts onto another sheet, you are going to need to add a helper column onto the source sheet and enter something along the lines of

=PARENT([Project/Milestone Name]@row)

Then in the metrics sheet you would use...

=COUNTIFS({Other Sheet Helper Column}, "ERP Integration (Intacct)", {Other Sheet Project or Milestone Column}, "Milestone")

Griffin Fujii

Apologies @Paul Newcome

I meant adding the ERP Integration (Intacct) milestones as a criteria in =MIN(COLLECT({Other Sheet Date Column}, {Other Sheet Phase Column}, "Requirements"))

So that it would only use dates that are "ERP Integration (Intacct)" and "Requirements". Hope that is more clear.

Paul Newcome

You would still need to create the helper column as above and then follow the pattern of range, criteria, range, criteria in the COLLECT function.