How to calculate number of days a project is at a certain status?

Kingsley Wong

Hi all,

I am looking for a way to calculate the number of days a project is on hold based on a change of status.

In my theoretical planner, I set a project start date and input an estimated project duration. This calculates a predicted end date. I have a column where I can define the project status, these are "In Progress", "On Hold" and "Complete".

I would like to track the cumulative number of days the project has been placed "On Hold" over the lifetime of the project. This will be defined as the delays. The projects are likely to change status a number of times over the life time of the project.

A potential solution I can think of is to create a workflow to records the date every time the status is changed to "On Hold" which will copy the row of data into a separate grid. Then from that count the number of days the project has been changed into that status.

Would anyone be able to advice whether a formula could be devised to calculate this "Delays" value?

Kind regards,

Kingsley

Smartsheet Query.JPG

Paul Newcome

I would suggest a copy row automation to start things off based on any time the status changes. Including a Created (date) type column in the destination sheet will allow us to record the date that it was changed. I also suggest two more additional columns in the destination sheet to be able to utilize the row number with column formulas and avoiding any accidental circular references.

First column would be an auto-number column. Formatting doesn't matter.

Second column will be a text number column with a column formula of:

=MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)

Finally we can add yet another column on the destination sheet (checkbox type) to track if that particular row is the most recent entry for that project (column formula):

=IF([Row #]@row = MAX(COLLECT([Row #]:[Row #], [Project Name]:[Project Name], [Project Name]@row)), 1)

So now we have every row numbered on the destination sheet, and the most recent entry for each project is flagged.

Now we can start piecing together the number of days each project is on hold in a text/number column.

If the row is "On Hold", we want to look at a few things and generate a number.

If the [Most Recent Entry] column is checked meaning the project is currently on hold, then we want to calculate how many days have elapsed from the date of on hold until today.

TODAY() - DATEONLY(Created@row)

If the [Most Recent Entry] box is not checked meaning that the project was taken off hold, then we need to find the next entry, pull the date, and subtract from that the date it was placed on hold.

DATEONLY(MIN(COLLECT(Created:Created, [Row #]:[Row #], @cell > [Row #]@row, [Project Name]:[Project Name], [Project Name]@row))) - DATEONLY(Created@row)

So now that we have our two formulas for the two possible scenarios for On Hold duration, we can use an IF statement to say that if the [Most Recent Entry] box is checked, run the first one, otherwise run the second.

=IF([Most Recent Entry]@row = 1, TODAY() - DATEONLY(Created@row), DATEONLY(MIN(COLLECT(Created:Created, [Row #]:[Row #], @cell > [Row #]@row, [Project Name]:[Project Name], [Project Name]@row))) - DATEONLY(Created@row))

Now we have the duration calculation running, we can use another IF statement to say that if the status is On Hold, the run the duration calculation.

=IF(Status@row = "On Hold", duration_formula)

=IF(Status@row = "On Hold", IF([Most Recent Entry]@row = 1, TODAY() - DATEONLY(Created@row), DATEONLY(MIN(COLLECT(Created:Created, [Row #]:[Row #], @cell > [Row #]@row, [Project Name]:[Project Name], [Project Name]@row))) - DATEONLY(Created@row)))

Now we have the duration on each row containing "On Hold".

From here you can use a SUMIFS to calculate the total duration in days for how long a project was on hold regardless of how many times it was on hold.

=SUMIFS({Destination Sheet Final Column}, {Destination Sheet Project Name Column}, [Project Name]@row)

Andrée Starå

Hi @Kingsley Wong

Please have a look at my post below with a method I developed. That, combined with the new Record a date feature, could be used to develop a solution.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Another option could be to use the Record a date feature multiple times, but then you would need to use multiple so-called helper columns and multiple Hold instances (can be a dropdown list for each).

What do you think?

Would any of those options work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Kingsley Wong

Hi Andrée,

Thank you for the prompt response, so I've tried creating the workflow to create record multiple dates. However, I am struggling to devise a way to separate the different projects and calculate the delay times.

For example, how would I be able to calculate the cumulative delays of only Project 1?

Thank you for your time.

Regards,

Kingsley

Paul Newcome

I would suggest a copy row automation to start things off based on any time the status changes. Including a Created (date) type column in the destination sheet will allow us to record the date that it was changed. I also suggest two more additional columns in the destination sheet to be able to utilize the row number with column formulas and avoiding any accidental circular references.

First column would be an auto-number column. Formatting doesn't matter.

Second column will be a text number column with a column formula of:

=MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)

Finally we can add yet another column on the destination sheet (checkbox type) to track if that particular row is the most recent entry for that project (column formula):

=IF([Row #]@row = MAX(COLLECT([Row #]:[Row #], [Project Name]:[Project Name], [Project Name]@row)), 1)

So now we have every row numbered on the destination sheet, and the most recent entry for each project is flagged.

Now we can start piecing together the number of days each project is on hold in a text/number column.

If the row is "On Hold", we want to look at a few things and generate a number.

If the [Most Recent Entry] column is checked meaning the project is currently on hold, then we want to calculate how many days have elapsed from the date of on hold until today.

TODAY() - DATEONLY(Created@row)

If the [Most Recent Entry] box is not checked meaning that the project was taken off hold, then we need to find the next entry, pull the date, and subtract from that the date it was placed on hold.

DATEONLY(MIN(COLLECT(Created:Created, [Row #]:[Row #], @cell > [Row #]@row, [Project Name]:[Project Name], [Project Name]@row))) - DATEONLY(Created@row)

So now that we have our two formulas for the two possible scenarios for On Hold duration, we can use an IF statement to say that if the [Most Recent Entry] box is checked, run the first one, otherwise run the second.

=IF([Most Recent Entry]@row = 1, TODAY() - DATEONLY(Created@row), DATEONLY(MIN(COLLECT(Created:Created, [Row #]:[Row #], @cell > [Row #]@row, [Project Name]:[Project Name], [Project Name]@row))) - DATEONLY(Created@row))

Now we have the duration calculation running, we can use another IF statement to say that if the status is On Hold, the run the duration calculation.

=IF(Status@row = "On Hold", duration_formula)

=IF(Status@row = "On Hold", IF([Most Recent Entry]@row = 1, TODAY() - DATEONLY(Created@row), DATEONLY(MIN(COLLECT(Created:Created, [Row #]:[Row #], @cell > [Row #]@row, [Project Name]:[Project Name], [Project Name]@row))) - DATEONLY(Created@row)))

Now we have the duration on each row containing "On Hold".

From here you can use a SUMIFS to calculate the total duration in days for how long a project was on hold regardless of how many times it was on hold.

=SUMIFS({Destination Sheet Final Column}, {Destination Sheet Project Name Column}, [Project Name]@row)

Kingsley Wong

Hi Paul,

Thank you for very much with your response, apologies for the delay in my reply.

I'm just slowly working through your suggestion to adapt it to our current planner!

Thank you for your time.

Regards,

Kingsley

Andrée Starå

@Kingsley Wong

You're more than welcome!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

Paul Newcome

Happy to help. 👍️

Chad Kalous

@Paul Newcome, I was able to leverage this solution in order to totalize the amount of time each Project Name spends in a particular phase. The issue I have run into is that we want to change from tracking by Project Name to a unique StatusKey because Project Name can change an that breaks the tracking. When I make StatusKey an system/auto number column it maps to the Auto-Number column on the StatusLog which breaks the tracking. I tried to map the StatusKey auto-number on the mainsheet to a text column on the StatusLog sheet but Smartsheets does not allow this.

Is there a way to keep seperate Auto-Number columns between the two sheets?

Or is there a way to create a unique ID without using the System Auto-Number type in the Main Sheet?

Thanks!

Margarethe Javellana

@Paul Newcome Is there a way to utilize your solution if the source sheet already uses the Auto Number?

Thank you!

Paul Newcome

@Margarethe Javellana The formatting on the auto-number column doesn't make a difference because each row will have unique data on it. You are fine to reference it in the above solution.

Margarethe Javellana

@Paul Newcome Thank you! Unfortunately, the Auto-Number in the Source sheet will not have a unique data in the Destination sheet because it will be duplicate copies of the same lines - the change is in the Status column. Since the Source sheet auto-number does not change, it will be the same value in the Destination sheet; therefore, the position of the entry will be exactly the same instead of number 1 for the original entry and increasing positions with each subsequent copy. Hope this makes sense?

Since the auto number is being utilized in the Source sheet, is there a formula that increases the position of each line copy in the Destination sheet?

Thank you so much!

Michael Farley

@Paul Newcome Thank you so much for this! still relevant, and still functional!