Snapshot of dates with predecessors

Here's a tricky one for those advanced solution builders that I cant solve with out of box functionality. A project sheet has many tasks with predecessors. I want to capture the number of days an end date changes BUT only on the tasks where a person changed the date. I.E. I dont need the dates that were affected by the predecessors.

It works perfectly by

using copy row automation to a "snapshot" sheet when an end changes
I do my calcs on the snapshot sheet to tell me the changes from the previous change

What I can't figure out is an automatic way to NOT copy the end dates affected by the predecessors. on the affected rows Modified by, modified date, ect also change to the same value on all rows affected.

using baseline functionality or another manual flag / helper column is not an option unfortunately.

Find more posts tagged with

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @markkrebs

Here is my limited solution.

If dependency is enabled, I presume that standard human change will change the top task of the predecessor relationship.

So, I added the Top helper column with the following formula;

[Top] =IF(ISBLANK(Predecessors@row), 1)

This is a sheet to record the human date change.

Then, I added a copy rows automation, triggered by Start change with Top=Checked condition.

Test 1 (Top Start change)

I, a human, changed the Task1 Start date. (Feb 14 to Feb 12)

The changed row was copied as expected.

Test 2 (Remove predecessor relationship change)

Then, I presume that another standard human change will change the Start date of some tasks in the dependency chain for some reason. For example, someone decides a task to start on a specific date regardless of a pre-defined start date.

So, I changed Task 3's Start Date. (Feb 14 to Feb 17)

As the warning image says, predecessor relationships on this row were removed. (As the second image shows, the Predecessors value is empty in the third row.)

The above removal of the Predecessor value changes the Top status, as shown below.

The above change in the Top value of the third row triggered the copy rows automation, as shown below.

Though limited, as it does not count for the change in Finish or by Duration, this method gives me some way to determine the Start change by a human.

markkrebs

that won't work for the scenario. The user is changing end dates only at times. One or more of these rows that they are changing will have a predecessor and they want the predecessors to stay. They could change multiple end dates at one time an hit save.

jmyzk_cloudsmart_jp

I'm not sure about your scenario, but here are my assumptions about your scenario and an updated demo solution:

Assumptions for Project Task Dependency Structure

Project Structure
- The project consists of multiple independent groups of tasks.
- Each group has its own sequence of dependent tasks, meaning tasks within one group do not affect tasks in another group.
- The first task in each group serves as the primary control point for scheduling.
Manual Date Changes
- Only the first task in each group may have its Start Date, End Date, or Duration manually updated by a user.
- All other tasks in the group are automatically updated based on their predecessor relationships.
Predecessor-Driven Updates
- When a predecessor task changes, all dependent tasks update their dates accordingly.
- These automated updates should not be considered manual changes.

Sample project sheet with such assumptions

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

A user changed the End Date of the task "Group B - Task 1" (Top task of the Group)

The Top helper column has a modified column formula;

=IF(COUNT(CHILDREN()) = 0, IF(ISBLANK(Predecessor@row), 1))

With the workflow automation described in the previous comments, only the row with the Top checked is copied to another sheet when either the Start Date or End Date changes.

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

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.