How to show the duration a task took to be completed between start date and end date

Genevieve P.

Hi @Christiana Gkini

A Report won't be able to summarize text/number columns or create averages out of your Duration display since it contains text. What you have shown in the report is what I would have suggested for your summaries. As long as you're clear what that number represents (days, hours, etc) then you could use this Report as a source for a Chart to visualize the duration.

If you need the words with the numbers, then depending on how many sheets you have and how many Ticket Types, you could create a Metric Sheet instead using cross-sheet formulas.

One column would list all your Ticket Types. Then next to it you'd use a cross-sheet AVERAGIF function to average the Ticket Duration values for that Type, exactly as your report is doing. Then you would need a third formula to turn that number back into words (X Years, X Months, X Days, X Minutes).

Cheers,

Genevieve

Christiana Gkini

Hi Genevieve,

Thank you so much for your advice.

I tried to do that as you can see below. Is that what you were referring to? What is the formula I could write in the helper formula to turn this back into words?

Thank you!

Genevieve P.

Hi @Christiana Gkini

It actually looks like you only have simple numbers to bring back - I assume this is Days, is that correct? If you're looking for an average, I would just add the word " Days" to the end of your formula, like so:

=IFERROR(AVERAGEIF({Copy of Snow Q1 Range3}, [Ticket Type]@row, {Copy of Snow Q1 Range 2}), 0) + " Days"

It won't show you time at all, since this is an average, and if you have over 365 days it would still say "365 Days" instead of "1 Year". Will that work for your purposes?

Keep in mind that Charts can only be built off of numerical data so this would be to reference as text in a grid, like you have pictured above.

Cheers,

Genevieve

Christiana Gkini

Hi Genevieve,

You were right. I tried that and then having the word days in the grid it didn't generate in the chart.

I will leave it as it it.

Thank you so much for your help. Much appreciated!