Duration Column with Formula/Dependencies

shelbylund

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?

Paul Newcome

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)

Paul Newcome

What are your various existing formulas?

shelbylund

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!

Paul Newcome

Which formula are you trying to use to drive the duration?

shelbylund

@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.

Paul Newcome

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.

shelbylund

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?

Paul Newcome

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)

shelbylund

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!

Paul Newcome

Happy to help. 👍️