Dates and Circular References

charish

I have a list of tasks and a common start date for all tasks. Now, if there is a resource or an engineering dependency , then the subsequent tasks will have to start after the current task ends. I am trying to build this in smartsheets with seven columns

1. ID of the task
2. Name of the task
3. Effort
4. Common Start Date
5. Depends On (ID): This column will reference any ID in column 1
6. Start Date: Calculated
7. End Date: Calculated (Start Date + Effort)

For each task/line item, If the "Depends On (ID)" column is empty, then I want to pick the "Common Start Date" for Column 5; else I want to pick the "End Date" of the task that the current task/line item depends on.

I am running into circular reference issues because the End Date is also calculated by adding the start date and effort. Trying to understand if there are better ways to achieving this.

Thanks in advance.

Paul.Woodward

Had some free time, and I wanted to see what ChatGPT had to say. They explain it better than I.

1. Your Column Layout (Restated)

Here’s your setup:

ID Name Effort (days) Common Start Date Depends On (ID) Start Date (Calculated) End Date (Calculated)

✅ Perfect. This is exactly what we want.

2. Why You're Hitting Circular References

• You want Start Date to pull either the Common Start Date or the End Date of the dependency.
• End Date is calculated as Start Date + Effort.
• But if Start Date depends on End Date, and End Date depends on Start Date, Smartsheet gets confused.
  ("Which comes first?" — classic chicken-and-egg problem!)

3. Solution: Split the Calculations More Carefully

We need to ensure that:

• Start Date only depends on known End Dates of other tasks (already calculated above).
• End Date is a simple formula — no dependency lookup inside it.

Key Rules

• Start Date should look up the End Date from Depends On.
• End Date should just be Start Date + Effort, without checking anything else.

4. How to Write the Formulas (Correct Way)

Start Date (Calculated Column)

plaintextCopyEdit=IF(   ISBLANK([Depends On (ID)]@row),   [Common Start Date]@row,   INDEX(      [End Date]:[End Date],      MATCH([Depends On (ID)]@row, [ID]:[ID], 0)   ))

Explanation:

• If "Depends On (ID)" is blank → use "Common Start Date".
• Otherwise → find the End Date of the task ID you depend on (using INDEX(MATCH())).

✅ NO recursion or circular logic here — clean lookup only.

End Date (Calculated Column)

plaintextCopyEdit=[Start Date]@row + [Effort]@row

Explanation:

• End Date = Start Date + Effort (simple addition).
• No dependency checks here.

✅ Straightforward math, no circular references.

5. Full Example Walkthrough

ID	Name	Effort	Common Start Date	Depends On (ID)	Start Date	End Date
1	Task A	2	5/1/2025	(blank)	5/1/2025	5/3/2025
2	Task B	3	5/1/2025	1	5/3/2025	5/6/2025
3	Task C	1	5/1/2025	2	5/6/2025	5/7/2025
4	Task D	2	5/1/2025	3	5/7/2025	5/9/2025

Behavior:

• Task A starts on Common Start Date.
• Task B starts when Task A ends.
• Task C starts when Task B ends.
• Task D starts when Task C ends.

🎯 NO circular references.
🎯 All dates calculate automatically when you fill in Effort and Depends On.

charish

Thanks for your help. I tried this, but still ran into the circular reference issue with the end date column. For now I resorted to Smartsheet's in-built dependency management set up and using the row numbers as dependencies. The only caveat is that I have to rely on manually entering the start date of the project manually and the rest is automated.