I am looking to create a sheet that moves through our 5 stages of work and how much time is spent in each phase. What is the best way to go about this?
The most straight forward would be to set up a date column for when you start each phase and then when you end the last phase (or when you start the first phase and then when you end each phase). From there it is subtracting one date form the other to determine how much time was spent in each.
Slightly more complex would be using a dropdown for the different phases and then using Record A Date automations to automatically populate the date columns when the status changes.
Both are relatively straight forward. It just depends on your structure and personal preference.
CERTIFIED SMARTSHEET PLATINUM PARTNER
Efficiency Live: The Ultimate PM Toolkit Workshop
For the second option, would I still be able to see how long was spent in each phase? I'm assuming that when you use the dropdown to change it to the next phase, the previous phase's date would no longer be visible, meaning I would not be able to pull that past data. Does that make sense?
You would have separate date columns for each phase.
Phase.....Phase 1 Date.....Phase 2 Date.....Phase 3 Date
Thanks, this was really helpful! So, now that I know to calculate how long was spent in each stage, I just can't figure out how to set it up so that it grabs those calculations on its own. For example: let's say tomorrow another stage is completed. I don't want to have to go in the sheet every time a stage is completed to calculate how long was spent in that stage, and the NETWORKDAYS function won't work unless it's grabbing from two dates, meaning I can't set it up ahead of time.
What is the workaround for this?
Were you wanting the calculations in separate columns for each phase? If so, you can go ahead and use the NETWORKDAYS function and wrap it in an IFERROR to output a blank if there is no phase completion date.
Is there a way to get an average of number of networkdays for each phase. For example, I have over 300 rows and I want to average the number of days between each review phase. I don't need to know average per row, just overall average per phase.
@Becky Wilson You would have to first calculate it on a row by row basis. You could then get the average from the helper column.
Thanks. Was hoping to avoid that
Create and edit formulas in Smartsheet
Formula combinations for cross sheet references
Smartsheet functions list
I created a timesheet and a form to go along with it. When someone uses the form and flags that someone has called off, I want the flag to return zeros in the ST Hrs, OT Hrs, and DT Hrs cells. Using AI to generate the formula below, it returns a Circular Reference error when the formula is placed in the ST Hrs cell.
Hi everyone. Some help please. I've been through index match but I don't think it's what I want. What I want to do is... Someone submits a form with one of the fields being "sub job", selected from a dropdown. Once I go into the spreadsheet after the form is submitted, I want the adjacent "WD no" column to be automatically…
Hi there, I'm building out a new sheet where I utilizing the system's "Last Modified" date column. I tried to trigger a workflow off of this column that sends an update reminder if it hasn't been modified in 2 days, but the column is not showing up as an option (presumably because it's system generated). With that in mind,…
©2024. All Rights Reserved Smartsheet Inc.