Calculating Duration

Juley Mathew

Hello,

How can i set up a column to calculate how long it took to complete a task from the date it was assigned to date it got completed? I don't understand the dependencies in the project settings.

Genevieve P.

Hi Juley,

If you don't want to use the dependency setting in project settings, this is actually fairly easy to do with your own formulas. To find the duration, minus the Start Date from the Completed Date and this will give you the total days.

For example:

=[End Date]@row - [Start Date]@row

This formula should be entered into a Text/Number column. Here are some Help Center articles that explain this further:

• Dates in Formulas
• @row function

Let me know if you have any questions about this!

Cheers,

Genevieve

Alon Shachar

But i want only the working days excluding Saturday and sunday

Genevieve P.

https://community.smartsheet.com/discussion/comment/325824#Comment_325824

Hi @Alon Shachar

If you want to find the working days between two dates, you can use the NETWORKDAYS Function!

Ex:

=NETWORKDAYS([Start Date]@row, [End Date]@row)

Cheers,

Genevieve

AH206

Hi there.

What if the Start Date and End Data are populated with a formula? Is there a way to have a column displays the duration between the Start/End columns?

Example:

Thanks,

Alli

Genevieve P.

Hi @AH206

That specific formula is writing the date out as text, using quotes. In this instance, no, there isn't a way to calculate the duration.

However if the Start and End columns are date type of columns, you could use the DATE function to output a date instead of text and then we can find the duration!

=IF(Color@row = "Red", DATE(2024, 01, 10)... and so on

Here's more information: DATE Function

Cheers,

Genevieve

delberto

Hi @Genevieve P. , what if I do want to use dependencies? In this case it seems to lock me to the system duration column rather than my calculated one?

Eg I have Start Date, Hours Required for Task, % Allocation of Resource, Calculated Duration, End Date.

I enter my start date, the amount of time the task needs, set my team member to be working at 50% of his/her time - my calc'd tells me that it takes 10 hours because I had 1 person working at 50% on my 5 hour task.

I can do this with formulas - as described above but as soon as I enable dependencies (because I need this), the Gannt will only accept my system Duration column? (The system duration won't accept formulas.)

Thanks.

Genevieve P.

Hi @delberto

Yes, you're correct - if you want to use dependencies in your Gantt chart then the Dependency column cannot contain formulas. You could use a formula column next to it to determine what to input, if that helps.

Cheers,

Genevieve

delberto

Thanks @Genevieve P.