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

Christiana Gkini

Hello,

I have a sheet with various tasks (over 1000) and I also have the date and time the task was created and the date and time the task was completed.

I would like to display in a dashboard the duration that the task took to be completed. Could you please help me find the best way to show this?

Thank you!

Paul Newcome

There are a number of solutions here that should help you get what you need:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Christiana Gkini

Hi Paul,

This is very helpful, thank you for sharing.

I tried a couple of those formulas you shared and it looks like it didn't work for me. See the pictures below and let me know what should I fix please.

Thank you!

Paul Newcome

You will need to use all of the helper columns out of whichever solution you are using. In your last screenshot, none of the column names are highlighted like they are in the first two screenshots which means either those columns do not exist in your sheet, or they are named something different.

Christiana Gkini

Thanks Paul. But even on the first screenshot that the names are highlighted, and the cells are selected, the formula is not working. How could I fix this?

And if I would use the last one I coppied this from one of the sheets you shared where you also had columns with start date, start time, end date and end time but the column names are not in your formula.

Paul Newcome

Ok. So the first two... What error message are you getting?

Right. That formula in your third screenshot is referencing other helper columns that have formulas in them that eventually reference back to the start/end date/time. So while they are not referenced directly in the formula that is generating the final output, they are referencing other columns that have formulas in them. You have to make sure you are using all of the helper columns and each one has the appropriate formula in it.

For example... In the one you are using is there a formula in the Years column? If so, does that reference the start/end date columns? If so, you will need to insert a text/number column called "Years" and drop the appropriate formula from the example into it. Then you will want to do that for every single column in that sheet from Duration to Minutes Difference. It all ties together and feeds into other pieces, so leaving even one piece out means the solution will not work.

My solution also uses a different formatting for the time. It uses "HH:MM AM/PM" whereas your solution is in 24 hour format and goes down to the seconds, so any formula referencing the time columns will need to be adjusted accordingly which mean that even after you insert all of the columns, copy/pasting of the formulas will not work. There are quite a few different solutions tucked away in that thread. I do remember there are a handful that deal with 24 hour time formats and a few that deal with seconds. You may need to combine a few of the solutions into one to get the exact outcome you want.

Christiana Gkini

Hi Paul,

I took this formula from one of your examples, which has the same column names and you don't have the word 'Years' or 'Hours' at any of your columns. Then how mine is not working? What should I change for this to work for me?

Paul Newcome

I know exactly which one you are referring to, and there are those columns. Try scrolling to the right.

Christiana Gkini

Hi here,

I have tried a couple of formulas trying to get the duration that the task took to be completed. Those formulas are still not working for me.

Could someone help me please? What should I update in the formulas to make them work?

And if this are not the right ones, which should I use instead?

Your help will be much appreciated.

Thank you!

Ray Lindstrom

Hi @Christiana Gkini,

In your first one, your IF statement has no ELSE which means it will only return a value "if" sum@row * 60 is less than 10.

In your second and fourth ones, you're missing the helper rows. That's why, as Paul mentioned, the range references aren't highlighting in colors. There's no data to calculate because the columns are missing.

What kind of error or wrong data are you getting in the third which looks like you're calculating for the Sum row?

Christiana Gkini

Hi Ray,

Thank you for getting back to me.

The error message I'm getting for the 3rd one is the below.

Could you please help me write a formula that will work for my case? And if any of those are not right which formula is the best for my case?

I'm really confused with this.

Thank you!

Paul Newcome

@Ray Lindstrom That first one doesn't need an "else". That is basically adding in the zero if the number fo minutes is less than 10.

@Christiana Gkini

Based on the second image, it looks like you have still not added the necessary helper columns.

In the third image, you are using a formula set up for hh:mm, but you have hh:mm:ss in your sheet. There is a solution in the previously shared time thread that accommodates seconds.

In the fourth image, it looks like you are not using the column names that are actually in your sheet.

Christiana Gkini

Hi Paul

Thank you for your comment.

Could you please help me write a formula that will work for my case? And if any of those are not right which formula is the best for my case?

I'm really confused with this.

Thank you!

Christiana Gkini

Hi Paul,

I started to create the helper columns, but the Minutes Difference column doesn't work although the right columns are selected.

Is there a quicker way/formula to calculate the duration between these columns, without having to create 100 helper columns?

We are supposed to run this quite frequently with the team and it won't be efficient for us to do 100 columns every time to just get the duration.

Thank you!

Paul Newcome

If that error is in either of the two columns referenced, it will pull through. The formula in your screenshot does not have any issues with it that I can see.

Christiana Gkini

Hi Paul

I don't see any errors in the other columns. The formulas worked perfectly fine there.

So what should I do for this to work?

Looking forward for some help here.

Thank you!