Formula for Turnaround Time

kellysjostrom

Hello! I'm trying to come up with a formula to determine how long an initiative takes from Intake Date to Task Completed Date. Intake Date is a parent row field and Task Completed Dates are in subsequent child rows. I'd love to be able to drag the formula from row 1 all the way down to row 100 with each new Parent row recalculating the formula to reconsider the turnaround time for it's child rows only.

Thanks!

Lauren Dominique

No worries- I am happy to continue helping, though I do feel like I've hit a roadblock in how helpful I can be...

1) How strange that the IFERROR formula is not accurately addressing your error value. I really have no idea why that is, because I'm using the same formula above and mine is appropriately blanking out the error.

2) Unfortunately, it would be manual to enter in the formulas I noted about since they aren't set up to be column formulas and Smartsheet's current conditional formatting feature is not able to automatically assign specific cell formulas to specific cells based on their parent/child status. (I imagine that might be a hard enhancement for them to accomplish). I also want to note that the colors I used in my previous screenshot are not set-up via conditional formatting - I included that formatting myself for the sake of making it easier to note which formula I have where.

Eric Law

@kellysjostrom Try this, and you don't need to drag the formula in Smartsheet, you just convert it into a column formula.

=IFERROR([Task Completed Dates]@row - PARENT([Intake Date]@row), "")

kellysjostrom

I'm now getting #Invalid Column Value error. I'm guessing the formula above needs to be tied with another formula? Any chance you can provide the full formula?

Lauren Dominique

Hi @kellysjostrom - a formula error of “Invalid Column Value” means that the format of the value the formula wants to produce is not consistent with the format of the value the column needs to have. I believe the formula that Eric shared will produce a number value, so the column type would need to be set to “Text/Number”. Do you have it set to that, or are your column properties set to something else?

kellysjostrom

@Lauren Dominique thanks for the insight but I do have it set to text/number.

Lauren Dominique

@kellysjostrom

I was able to recreate a similar scenario to give a visual:

The column formula I applied to the "Days to Completion" column is =[Task Completed Date]@row - PARENT([Intake Date]@row)

"Intake Date" and "Task Completed Date" are both Date fields. "Days to Completion" is a Text/Number field.

kellysjostrom

@Lauren Dominique Thanks! Ok So what I figured out is going wrong is the error is showing up in the parent rows, 1) any way to adjust the formula to not provide an error in parent rows 2) have the output number as weeks instead of days and 3) reference the highest level parent in the formula so if I have three levels it accounts for level one , not level two or three as a parent?

Lauren Dominique

1. Yes! You would modify the formula in the "Days to Completion" column to be: =IFERROR([Task Completed Date]@row - PARENT([Intake Date]@row), "") This formula is basically saying, if there IS NOT an error, show me the number of days between completed date and the intake date, and if there IS an error, leave the cell blank.
2. Also yes! You will still need the "Days to Completion" because that field will be necessary to calculate the number of weeks. You'd create a "Weeks to Completion" column and enter in this formula: =[Days to Completion]@row / 7
3. Also yes - BUT you won't be able to use Column Formulas in the "Task Completed" or "Days to Completion" columns. They'll have to be cell-level formulas only, because not all the cells in each column have the same need.

With all of the above considered, here's what I've built out with some color-coding to note what formulas go where:

The formula in the blue cells would be: =MAX(DESCENDANTS([Task Completed Date]@row))

The formula in the green cells would be: =[Task Completed Date]@row - [Intake Date]@row

The formula in the yellow cells would be: =IFERROR([Task Completed Date]@row - PARENT([Intake Date]@row), "")

And the column formula in the "Weeks to Completion" column would be: =[Days to Completion]@row / 7

In the example above, I removed all decimals from the "Weeks to Completion" column, so it's rounding up to number of weeks. But, if you wanted a very specific view, you could add in decimals so you could track partial weeks instead of rounding 5 days up to 1 week.

And one more thing - if you also wanted to be able to see # of Days & Weeks it took to complete each sub-task (i.e., the "Parts" in my example) you could do so by adding the same blue formula to all of the "Parts" lines too:

kellysjostrom

@Lauren Dominique The last time I'll bug you today regarding this :) 1) So I'm still getting that original error you'll see it in my screenshot below. I thought the error was coming in because I had the formula in a parent row but with the error formula I'm still seeing it, it's actually in all parent rows that are salmon in color. 2) Are the different formulas that you have above a part of conditional formatting rules or do you have to do it manually? We have projects come and go in this sheet so it might be too much if I have to change the formula for individual rows each time. I wanted to be sure I wasn't missing something. Thank you!

Lauren Dominique

No worries- I am happy to continue helping, though I do feel like I've hit a roadblock in how helpful I can be...

1) How strange that the IFERROR formula is not accurately addressing your error value. I really have no idea why that is, because I'm using the same formula above and mine is appropriately blanking out the error.

2) Unfortunately, it would be manual to enter in the formulas I noted about since they aren't set up to be column formulas and Smartsheet's current conditional formatting feature is not able to automatically assign specific cell formulas to specific cells based on their parent/child status. (I imagine that might be a hard enhancement for them to accomplish). I also want to note that the colors I used in my previous screenshot are not set-up via conditional formatting - I included that formatting myself for the sake of making it easier to note which formula I have where.