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?

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

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.

jmyzk_cloudsmart_jp

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.

https://help.smartsheet.com/articles/2482093-baselines

https://app.smartsheet.com/b/publish?EQBCT=f60e320684ca45e3a144635b04538fb2

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.

https://app.smartsheet.com/b/publish?EQBCT=8e0eec3c8d0b46eda5b8595c38fffe15

[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.

https://app.smartsheet.com/b/publish?EQBCT=e57ddf3fad1e4c748da5fe1fba6d4656

[Oldest] =IF(MIN(COLLECT(Created:Created, [Row ID]:[Row ID], [Row ID]@row)) = Created@row, 1)

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.