% Complete with no projected due date

Patrice Cohill

Hi there ~ Looking for some help on the best way to proceed here. It seems duration and predecessors are not applicable for what I am looking to accomplish.

I have a Creation Date, Resolution Date and Days Spent columns along with a % Complete column. I would like to show 100% complete or simply complete when the resolution date is populated. If no resolution date is there a way to calculate the % complete based on days spent? I am thinking it doe snot seem possible.

This sheet is to be utilized to set up a Dashboard view of complete vs open and days spent to completion. These are basically open ticket items by month showing days spent to resolution/completion. The tickets do not have a due date. I could also use an average of days spent per month per ticket.

Help set my mind straight here as to what is possible and how. The tickets are attached to the Smartsheet via JIRA connector.

Any and all assistance is greatly appreciated. Thank you!

Paul Newcome

DO you have a target resolution date or a target number of days spent?

Heather Duff

Hi @Patrice Cohill

It would be easy to set up the complete part:

=IF([resolution date]@row <>"","100%","")

This reads "If the resolution date is not blank, show 100%, otherwise leave blank.

If you wanted to do a % to completion based solely on date, there would need to be a target end date, or something like that, to compare the start date to. In one scenario, I used this:

=IF([Actual End]@row <> "", 1, ((TODAY() - [Start Date]@row) / ([Target End]@row - [Start Date]@row)))

In your case, you would use the following, with [Target Resolution] a newly-created column:

=IF([Resolution Date]@row <> "", 1, ((TODAY() - [Creation Date]@row) / ([Target Resolution]@row - [Creation Date]@row)))

This translates to: If the resolution date is not blank, enter 100%. Otherwise, divide the number of days that have lapsed since creation by the number of days the task was expected to take.

Let me know if this works for you!

Best,

Heather

Patrice Cohill

Hi Paul ~

No only actual days spent.

Patrice Cohill

Hi Heather ~

Great, thank you! Since this is connected to JIRA and there is no due date or targeted resolution date setup. I am basically looking to create a dashboard that shows the number of tickets open, #of tickets closed and the average life of a ticket and/or days outstanding per month. I am I going about this the right way?

I am thinking I need to go back to my team to give me a targeted resolution date time frame like 30 days to accomplish such a view. Your thoughts?

Patrice Cohill

Reports can fulfill most of my needs but until reports can be incorporated into a Dashboard view it does me no good except to check my data.

Patrice Cohill

Paul Newcome

To be able to establish a variable % Complete, we would definitely need a target resolution date.

I assume your days spent for rows without a resolution date are basically today minus the creation date?

Patrice Cohill

Yes sir.

Patrice Cohill

I did a quick dashboard with what I have.

It would work if some how I could color code the tickets that are complete all whiling showing the days spent to resolution.

PATH-309 & 304 are complete.

Paul Newcome

Ok. Then we will definitely need a target date to be able to to calculate a percent complete. Either that or a target number of days. We need something to compare to.

Patrice Cohill

Thanks, Paul. It seems that is the only way.

I do see simpler solutions but they are solutions Smartsheet does not yet offer.