Hello everyone, I guess (or hope) some of you have used the PMO template that contain these 2 columns, and then it calculates the overall "Health" based on the delay. However, I'm struggling with them and how to update them… Here is the scenario: * Let's say that I just created my project plan, so it is easy to set the Actual from/to dates and then copy values to Planned from/to dates. * When adding lines (from scratch), I add the Actual from/to dates… Most likely, Planned dates will remain blank. * When copying lines, I can update the Actual from/to dates, but (unless updated too) Planned dates will be wrong, as they have the original value (from the cell I copied it from). * Conclusion: the Health calculation is not reliable… Now, you may say "use the Baseline", but as far as I have read, I could not find the option to keep the original Baseline, and set up the baseline for new lines (only)… Also, I understand that you need to manually trigger the Baseline for creation/update. I was wondering if any of you had managed to "automate" any of these steps. Thanks for your feedback.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Planned and Actual dates

MMCH

Hello everyone,

I guess (or hope) some of you have used the PMO template that contain these 2 columns, and then it calculates the overall "Health" based on the delay.
However, I'm struggling with them and how to update them…

Here is the scenario:

Let's say that I just created my project plan, so it is easy to set the Actual from/to dates and then copy values to Planned from/to dates.
When adding lines (from scratch), I add the Actual from/to dates… Most likely, Planned dates will remain blank.
When copying lines, I can update the Actual from/to dates, but (unless updated too) Planned dates will be wrong, as they have the original value (from the cell I copied it from).
Conclusion: the Health calculation is not reliable…

Now, you may say "use the Baseline", but as far as I have read, I could not find the option to keep the original Baseline, and set up the baseline for new lines (only)…

Also, I understand that you need to manually trigger the Baseline for creation/update.

I was wondering if any of you had managed to "automate" any of these steps.

Thanks for your feedback.

Find more posts tagged with

Templates

Formulas

Automations

Project Management

Accepted answers

All comments

Debbie Sawyer

This might be easier to talk through with a screen share. I'm around for 30 mins - are you free? If so, drop me an email (found by clicking on my profile) and I can walk you through what is possible.

Yulichka

Are you using dependencies or predecessors to automate the second date for you?

MMCH

Hi Yulichka,
Dependencies and predecesors are only applicable to one date… not both…
I saw it in another post in the community, and I can confirm.
Thanks for your feedback though.

MMCH

Hi Debbie,
Just saw your message, will contact you directly via email.

Thank you.

Debbie Sawyer

@MMCH I am free now if you are?

Debbie Sawyer

Hi - We are meeting in an hour or so. Which template set did you use, just so that I can see where you are starting from? Thanks

Yulichka

Per AI, there are two options:

Choice A

You have hit on one of the most common friction points in Smartsheet’s native project management templates! The classic PMO template relies on a manual comparison between "Planned" and "Actual" columns to determine health, which breaks down the moment teams copy/paste rows or add new tasks mid-project.

Since Smartsheet’s built-in Baseline feature is an "all-or-nothing" snapshot (you can't selectively baseline just a single new row without overwriting changes on other rows), many Smartsheet Automagicians bypass it or supplement it using automated workflows.

Here are the two best ways to automate this and make your Project Health 100% reliable.

`Solution 1: The "Set a Date" Automation (True Automation)`

Instead of forcing users to manually copy dates from Actual to Planned when a task is created, you can let Smartsheet's automation engine handle it.

You can set up a workflow that says: "When a new row is added, look at the Actual Start/End dates, and automatically stamp them into the Planned Start/End columns."

Here is exactly how to build it:

1Create the TriggerTriggers on new or copied rows

Set the trigger to When rows are added or changed.

Change the dropdown to evaluate When columns change and select your Actual Start column.

2Add a Condition SetEnsures we only fix blank or copied rows

Add a condition: Where Planned Start is blank (handles brand-new rows).

Switch the condition to an OR block and add: OR Where Planned Start is not blank but you want to overwrite it if a specific helper flag is checked (e.g., a "Reset Row" checkbox).

3Add an Action to Record DateStamps the Start Date

Choose the action Change cell value.

Select your Planned Start column.
Set the value to Use a date from another cell and choose Actual Start.

4Add a Parallel Action for End DateStamps the End Date

In the exact same workflow block, add a second Change cell value action for Planned End, setting it to copy from Actual End.

Why this fixes your scenario:
For new lines from scratch: The moment you type the Actual dates, the automation immediately copies them over to the Planned cells.
For copied lines: Since the copied line brings over old, incorrect Planned dates, you can clear out the Planned cells or use a checkbox column called [Reset Baseline] as a trigger condition. When checked, the automation overwrites the old copied data with the new row's actual dates.

`Solution 2: The Formula-Based "Lock" (No-Maintenance Approach)`

If you don't want to rely on background automation queues, you can use a formula inside your Planned columns that defaults to the Actual date unless a specific condition is met.

To do this cleanly without creating a circular reference, you introduce a single column called [Baseline Locked] (Checkbox).

In your Planned Start column, use this Column Formula:

Excel

=IF([Baseline Locked]@row = 1, [Planned Start]@row, [Actual Start]@row)

(Note: Because Smartsheet column formulas can't easily self-reference without throwing an error, the cleanest formula approach uses a helper column like [Approved Baseline Start] where you paste your locked dates, fallback to Actual if empty).

Alternatively, you can skip tracking a manual "Planned" date entirely for health, and use a formula that calculates Health relative to the Current Date vs. Percent Complete.

`Recommendation`

Go with Solution 1 (Set a Date Automation). It keeps your sheet clean, allows you to preserve the structural flexibility of regular date columns (which Smartsheet requires for Gantt dependencies), and completely eliminates the human error of forgetting to update the "Planned" fields when copying rows.

Yulichka

Choice B

This is a classic pain point with the standard Smartsheet PMO templates. The native Baseline feature is an "all-or-nothing" tool for the sheet, and managing the Planned vs. Actual variance manually when adding or copying rows is an absolute recipe for broken Health formulas.

Since Smartsheet automation can't directly copy the value of one cell into another cell on the same row (e.g., you can't natively say "Set Planned Date to equal Actual Date" via a workflow action), we have to use a hybrid approach of flexible formulas and targeted automation to handle both new and copied lines cleanly.

Here is the most reliable way to automate this process so your project health tracking actually stays accurate.

`The Strategy: Formula Defaults with "Clear Cell" Automation`

Instead of typing directly into your main Planned Start and Planned End columns, you split them into a Formula column (which the Health formula reads) and a Manual override column (used only when you explicitly want to change the plan).

`1. Set Up Your Columns`

Create or rename your columns to follow this layout:

Actual Start / Actual End (Your live, shifting project dates)
Planned Start (Override) / Planned End (Override) (Date columns, hidden by default to keep the sheet clean)
Planned Start / Planned End (The main columns your Health formula reads)

`2. The Logic Formulas`

In your main Planned Start and Planned End columns, apply these column formulas:

For Planned Start:

Excel

=IF(ISBLANK([Planned Start (Override)]@row), [Actual Start]@row, [Planned Start (Override)]@row)

For Planned End:

Excel

=IF(ISBLANK([Planned End (Override)]@row), [Actual End]@row, [Planned End (Override)]@row)

Why this solves Scenario 1 (New Lines):When you add a new line from scratch, the Override columns are naturally blank. The formula automatically forces your Planned dates to mirror your Actual dates. Your variance is exactly 0 days, meaning your project Health starts perfectly green and functional right out of the gate.

`Solving Scenario 2: The "Copied Lines" Problem`

When you copy or duplicate a row, the stale dates in the Override columns copy along with it, which breaks the logic. To fix this automatically, we use Smartsheet’s Clear Cell Value automation.

`The Automation Workflow:`

Add a temporary checkbox helper column called [Row Reset] (you can hide this later).
Create an automation workflow triggered When a row is changed.
Set the trigger condition to: When [Actual Start] changes to Any Value.
Add a condition block: Where [Row Reset] is unchecked.
Set the action to: Clear cell value → select both [Planned Start (Override)] and [Planned End (Override)].
Add a second action immediately after: Change cell value → set [Row Reset] to Checked.

`How it plays out in practice:`

When you copy a row: You will naturally go in and update the Actual Start/End dates to fit the new task's timeline.
The Trigger: The moment you change those Actual dates, the automation fires because [Row Reset] isn't checked yet.
The Clean Slate: It instantly wipes out the copied, stale dates inside the hidden Override columns.
The Result: Because those override cells are now blank, your main Planned formulas instantly snap to match your brand-new Actual dates.

If you ever need to lock in a true baseline for a specific task later, you simply type that specific date right into the Planned Start/End (Override) columns, and the formula will prefer your manual date over the actual date.

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.