Returning Start and End Dates
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.
Answers
-
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"))
-
@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?
-
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")
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!