Duration Column with Formula/Dependencies
Hello,
Currently, I use a Text/Number column for task duration. I have a workflow set up to change the duration of each task depending on the urgency (either urgent or non-urgent), which is calculated using several weighted questions in the Sheet Summary that populate either a 0 (non-urgent) or 1 (urgent). This setup doesn't account for weekend days, which has been a concern for my team.
To fix this, I changed the duration column from Text/Number to Duration in a test sheet and set the dependency to exclude weekend days. However, it seems that if dependencies are being used in a Duration column, formulas cannot be used in that column. My initial plan was to eliminate the duration workflows and use an IF function to change the duration based on whether 0 or 1 was in the helper column. This doesn't appear to be an option.
Is there a formula or workflow I can use with my current setup to ignore weekend days, OR is it possible to use a Duration column with dependencies in conjunction with a formula or workflow that will change the duration depending on the task's urgency?
Shelby/Bee Lund | they/them/theirs
Oregon Health & Science University
Project Coordinator, Conversions
Best Answer
-
You can use a formula to output the duration into a column and then use a cell reference within the WORKDAY function to pull the duration from each row.
=WORKDAY([Start Date]@row, Duration@row)
Answers
-
What are your various existing formulas?
-
Hi @Paul Newcome,
Here are the workflows that are set up. Financial Benefit, Patient/Staff Safety, and Product Availability are fields in the Sheet Summary. These workflows change the value in the corresponding helper columns.
The Overall Score column contains the following column formula:
=[Financial Benefit (Helper column)]@row + [Patient and/or Staff Safety (Helper Column)]@row + [Product Availability (Helper)]@row
The Urgency column contains this column formula:
=IF(ISBLANK([Overall Score]@row), "", IF([Overall Score]@row >= 1, "High", IF([Overall Score]@row >= -1, "Low")))
Let me know if you need more information. I appreciate your help!
Shelby/Bee Lund | they/them/theirs
Oregon Health & Science University
Project Coordinator, Conversions -
Which formula are you trying to use to drive the duration?
-
@Paul Newcome, in my current set up there is no formula for duration, it's set up as a workflow.
I was hoping to use an IF formula in the duration row but didn't create one yet since I discovered pretty quickly that you can't use an IF formula in a duration column that uses dependencies.
Shelby/Bee Lund | they/them/theirs
Oregon Health & Science University
Project Coordinator, Conversions -
You won't be able to use a change cell automation in a dependency driven column either.
You will need to turn off dependencies and use formulas / automations if you need something more dynamic. You can use the WORKDAY function in formulas to output dates that skip over weekends and a list of holidays.
-
Hi Paul, I'm still unclear. The WORKDAY formula must include the duration/num_days in the formula, but this number will change depending on the Sheet Summary fields. I don't see a workflow option to change a date cell value which would allow me to set the workflow to change the formula to one with the correct num_days when certain cricteria are met. Would this require me to manually update the formula with the correct num_days when it changes? Is there some step or functionality I'm missing?
Shelby/Bee Lund | they/them/theirs
Oregon Health & Science University
Project Coordinator, Conversions -
You can use a formula to output the duration into a column and then use a cell reference within the WORKDAY function to pull the duration from each row.
=WORKDAY([Start Date]@row, Duration@row)
-
Thank you Paul! This was super helpful. I didn't realize the formula would allow me to utilize the Duration column as the num_days. Have a great weekend!
Shelby/Bee Lund | they/them/theirs
Oregon Health & Science University
Project Coordinator, Conversions -
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!