If blank, copy another field
I have the following columns: Start Date, End Date, Baseline Start Date, Baseline End Date, all of which start out as blank, are date type fields.
Once a Start Date is populated, if the Baseline Start Date is blank, it should update with the Start Date
Once the Baseline Start Date is populated, it should not change
I would do the same thing with End Date and Baseline End Date
I tried these 3 formulas, but I get errors.
- =IF(ISBLANK([Baseline Start Date]@row), [ Start Date]@row, [Baseline Start Date]@row) - returns #unparseable
- =IF(NOT(ISBLANK([Baseline Start Date]@row), [ Start Date]@row, [Baseline Start Date]@row) - returns #unparseable
- =IF([Baseline Start Date]@row="", [Start Date]@row, [Baseline Start Date]@row) - returns invalid column value
Any suggestions on how to get this to work?
Answers
-
Your first two have an extra space between the opening square bracket and the beginning of the "Start Date" column name.
[ Start Date]
should be
[Start Date]
The third issue, I would start with making sure all four columns are set as date type columns.
-
Hi @MelM18
You can achieve those two with the standard baseline features.
- Once a Start Date is populated, if the Baseline Start Date is blank, it should update with the Start Date.
- Once the Baseline Start Date is populated, it should not change.
The below image shows how I set the baselines.
But if you want to avoid using the standard baseline features, here is a solution that uses copy rows automation to fix the baseline dates.
I use copy-row automation because we do not have fix-column automation, so we can not fix the baseline dates defined by formulas referencing changing values like the Start Date. There is fix-row automation, but if we use it, your project manager or assigned people can not change the duration, Start Date, etc.
So, the solution copies rows when the Start Date changes to another sheet, Copied Rows. (We usually want to use the Smartsheet's Dependencies features, like duration and predecessor. A trigger for the Start Date is enough, as when the Start Date changes, the End Date also changes.
[Baseline Start Date] =IF(ISDATE([Start Date]@row), [Start Date]@row)
[Baseline End Date] =IF(ISDATE([End Date]@row), [End Date]@row)
[Baseline Start Date Fixed] =INDEX(COLLECT({Copied Rows Range : Baseline Start Date}, {Copied Rows Range : Row ID}, [Row ID]@row, {Copied Rows Range : Oldest}, 1), 1)
[Baseline End Date Fixed] =INDEX(COLLECT({Copied Rows Range : Baseline End Date}, {Copied Rows Range : Row ID}, [Row ID]@row, {Copied Rows Range : Oldest}, 1), 1)As soon as the Start Date is populated, the End Date is populated by the dependencies features, and the two formulas above change the value of baseline dates. (By the way, since [Baseline Start Date] is always the same as [Start Date], we do not even have to create the column and formula; instead, we can reference the copied row's [Start Date].)
Then, the automation copies rows to the Copied Rows sheet.
We look up the oldest row of each task with the 3rd and 4th formulas. Since the copied rows' values do not change unless we edit them, we effectively fixed the baseline dates.
In the Copied Rows sheet, we can use the Row ID and Created system column's value to determine which row is the oldest for each task.
[Oldest] =IF(MIN(COLLECT(Created:Created, [Row ID]:[Row ID], [Row ID]@row)) = Created@row, 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!