Tracking Time a Project Stays at Different Stages of Development

aschneiderheinze1025

I have the following three sheets:

SHEET 1 (with proposal form): Used to collect form submissions. Once reviewed, row is automatically copied to SHEET 3.

SHEET 2 (with approval form): Used to collect form submissions from the management team. The form includes the following fields: Program Code, Stage, and Decision (drop down with Approved, Not Approved, Request Revisions).

SHEET 3: Used to track progress of a program development through Stages 1, 2, and 3 to launch. There are two columns [Stage 1 (Decision)]@row and [Stage 2 (Decision)]@row, both of which have formulas to reference SHEET 2, matching the Program Code and Stage, then displaying the Decision. There is also a Status@row column that shows the current stage of the project. Initially it will change to "Stage 1" once it is copied from SHEET 1. Then, it changes, using an automated workflow, based on what appears in [Stage 1 (Decision)]@row and [Stage 2 (Decision)]@row (e.g., if currently "Stage 1" but then [Stage 1 (Decision)]@row equals "Approved", Status@row changes to "Stage 2", or equals "Request Revisions", Status@row stays at "Stage 1", etc.).

Status@row can equal "Stage 1", "Stage 2", "Stage 3", "Active", or "Archived Idea", if at any point the management team chooses "Not Approved" in Stage 1 or 2. It changes to "Active" once the program officially launces.

I want to be able to track the number of days a program stays in each of these phases:

• Stage 1 (and changes to "Stage 2" at some point)
• Stage 1 (and changes to "Archived Idea" at some point, that is management decided at some point in Stage 1 that the program wasn't feasible)
• Stage 2 (and changes to "Stage 3" at some point)
• Stage 2 (and changes to "Archived Idea" at some point)
• Stage 3 (and changes to "Active" at some point; that is the program officially launched)
• Stage 3 (and changes to "Archived Idea" at some point)

Down the road, we may use this sheet to track "Changes in Scope" that could occur once a program becomes "Active". But, I'll deal with that later. :)

I assume I will need to use Start and End date columns, and formulas that somehow calculate the time between when it started as Stage [x] to when it changed to [x]. I am not quite sure how to do this without using TODAY(), but then I want it to stop calculating days and "freeze" that number so I can report how much time this program (and other programs) spend in each of phases.

Anyone have an idea of an approach to accomplish this? I don't know if having [Stage 1 (Decision)]@row and [Stage 2 (Decision)]@row update via a cell reference (rather than through a manual change) would make a difference.

Thanks!

jmyzk_cloudsmart_jp

Hi @aschneiderheinze1025

There are two possible methods I can think of to track the duration a project stays in each stage:

Method 1: Copy Row Automation & Record Sheet Approach

1. Set up an automation to copy a row to a separate Record Sheet whenever the Status column changes.
2. In the Record Sheet, use the Created System Column to capture the timestamp when the row is copied.
3. Ensure each row has a unique ID (e.g., Program Code) that is also copied to the Record Sheet.
4. For example, based on the unique ID, you can use the INDEX/MATCH or VLOOKUP formulas to retrieve the Created timestamp's date value for Stages 1 and 2.
5. Calculate the duration between Stage 1 and Stage 2 by comparing these values.
   • Example: Created(Stage 2) - Created(Stage 1)
6. Important Limitation: If you try to use the Created System Column in the source sheet, this method will not work because the Record Sheet can not have its own Created value. (Copy row automation's limitation)

I use a similar method to determine the time between the start and end of a task, converting Created to minute values for billing purposes.

Merits of Method 1:

• Provides a historical record of all status changes in the Record Sheet.
• Allows for audit tracking of when and how often status changes occurred.
• Works well if multiple status changes need to be logged over time.

Limitations of Method 1:

• Requires an additional Record Sheet for tracking.
• More complex setup involving formulas and lookups to calculate durations.
• The Record Sheet can grow large over time if there are many status changes, which slows the calculation.

Method 2: Workflow Automation to Record Stage Change Dates

1. Create workflow automations to record the date each stage changes in separate columns.
2. When the status changes to a specific stage (e.g., Stage 1 to Stage 2), trigger an automation to set a date field (e.g., Stage 1 Start Date, Stage 2 Start Date, etc.).
3. Compare these recorded dates to calculate the duration between stages.
   • Example: Stage 2 Start Date - Stage 1 Start Date
4. This method ensures that it remains fixed once a date is recorded, unlike formulas that use TODAY().

Merits of Method 2:

• No need for an extra sheet—all tracking happens in the same sheet.
• More straightforward—automations set fixed values instead of relying on formulas.
• Efficient for near real-time monitoring of project progress.

Limitations of Method 2:

• No historical record of past status changes—only the latest stage change dates are stored.
• Requires careful setup of automation rules to prevent overwriting or missing updates.
• You have to deal with cases where a user changes the recorded dates and updates the Status value in an unexpected order, which may cause incorrect timestamps or gaps in the recorded data. (You can not use lock automation to a specific cell)

Which Method to Choose?

• If you need to track all status changes over time, Method 1 (Record Sheet) is better.
• Method 2 (workflow automation) is more straightforward and efficient if you only need to track the duration of each stage and do not need a complete history.
• If users could change recorded dates or update statuses out of sequence, Method 2 may need extra safeguards (such as validation rules or alerts).

Both methods have advantages, and the best choice depends on your reporting needs and data management preferences.