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

Christiana Gkini

Hello

Is anyone able to help me here?

Looking forward for some help.

Thank you!

Christiana Gkini

Hello

Can someone help me here please? I desperately need some assistance here.

I have tried to apply the solution that Paul gave above, however the formulas in some of the columns don't seem to work and the error appears as 'Invalid Data Type', even though the column names appear to be highlighted and I have added the helper columns as I was advised.

Could you please help me understand what is wrong with the formulas in this case? And if any of those are not right which formula is the best for my case?

I'm really confused with this, please help me.

Thank you!

MichaelTCA

Hello,

You have a lot of helper columns going on here! My opinion, it's a good practice to create helper columns to develop the data you need and then combine the functions into less columns.

First, the function NETDAYS(Date1,Date2) or NETWORKDAY(Date1,Date2,SpecifiedHolidays) will calculate the number of days between the dates. It includes "today" so if you want only the net days between the dates, you'll have to subtract 1, otherwise it will include the date started. Math likes to start at the beginning or end which can throw off some values.

We'll start with this function in the first helper column: (THE DATES HAVE TO BE SPECIFIED AS DATE PROPERTIES OF THAT COLUMN FOR THIS FUNCTION TO WORK. Even if you try to add or subtract dates in text/number columns, it will not work. Manipulating dates and using date functions require the dates to be in specified Date Columns)

=NETDAYS([Date Created]@row, [Date Closed]@row)

Now we have the net days and only have to calculate the net time spent. Since you're using a 24-hour system, it shouldn't be an issue since we can easily relate it to a percentage of the day spent. Using the Left, Mid, or Right functions can help big time.

Since you're using a 24-hour system and a day just so happens to be 24 hours, we're going to convert all numbers into a unit of hours and divide by 24 to make them a unit of measure of "days".

Calculate difference in Hours:

=(VALUE(LEFT([Time Closed]@row, 2)) - VALUE(LEFT([Created Time]@row, 2))) / 24

Calculate difference in Minutes: (Works like the LEFT() function, except you pick the starting point

=((VALUE(MID([Time Closed]@row, 4, 2)) - VALUE(MID([Created Time]@row, 4, 2))) / 60) / 24

Calculate the difference in Seconds:

=((VALUE(RIGHT([Created Time]@row, 2)) - VALUE(RIGHT([Time Closed]@row, 2))) / 3600) / 24

Add the net days and here's our final result for the duration:

=NETDAYS([Date Created]@row,[Date Closed]@row) + ((VALUE(LEFT([Time Closed]@row, 2)) - VALUE(LEFT([Created Time]@row, 2))) / 24)+(((VALUE(MID([Time Closed]@row, 4, 2)) - VALUE(MID([Created Time]@row, 4, 2))) / 60) / 24)+(((VALUE(RIGHT([Created Time]@row, 2)) - VALUE(RIGHT([Time Closed]@row, 2))) / 3600) / 24)

If you want the helper columns to use for analytics or other reasons, you can keep them and use the sum function for the specified range in a row. Otherwise, you can use 1 function for the entire duration in a single column.

If you want to get fancy, you can add +" days" to the end of the function. Include the + symbol.

I would double check my column names if I were you. I tried to make it the same, but you never know.

Christiana Gkini

Hi Michael,

Thank you so much for this detailed guidance.

I tried this and it says Invalid Data Type. Please see the pictures below. What can I do to fix this? My columns are all date columns, however I'm wondering if this isn't working because I have formulas behind the dates and the time columns ? Happy to jump on a quick zoom to help me with this.

Looking forward to hearing from you!

Thank you!

MichaelTCA

Hello,

Try only using the first part of the function for net days in one column and the second part of the function for time in another.

If there is still an invalid data type in the net days, then most likely the variables in the function are a different type than the column properties.

But start with splitting the net days and the net time into 2 columns and see if you get the same "invalid data type" for both.

Christiana Gkini

Hi Michael,

Thank you for getting back to me.

I just refreshed the page and it looks like it worked. However, I'm not sure what this number exactly indicates? 12 hours for example?

Thank you!

MichaelTCA

Fantastic!

The unit of measure is in "days".

Christiana Gkini

Also I tried to drag the formula to the rows below and it shows the same message as before. Not sure why this happened.

MichaelTCA

Interesting.

The date in the first row looks correct.

After entering a date, a "date column" will change the value to look like this depending on what the format is set to:

Double check the default setting for dates at the top right. Kind of looks like a calendar.

Christiana Gkini

Hi Michael,

Thank you for this.

I tried to apply that and by selecting the cells in the date columns and the date format didn't change. When I double clicked on a few cells the date format did change. But I have over 1000 cells I am not able to do them one by one. Is there any other way to change the format to all the cells in the column? I wander if the problem is that the date columns have formulas behind?

MichaelTCA

Oh really. So you are pulling that data from another source as well?

Then, possibly, we have to go back and change the column type of the source.

Lookup functions only show what the cell displays (obviously) and not the function behind it.

Christiana Gkini

Hi Michael,

Initially the data came with one column containing both date and time. Therefore I used a formula to separate those in different columns. Could I use the date format in these initial columns? Or is there any other formula to use the data as it is in the initial columns? Not sure what should I do now.

Christiana Gkini

Hi Michael,

I looks like the problem was the date format that all the formulas I tried weren't working. By changing the date format all the formulas worked. Do you know if there is any other way to change the format to all the cells in the column? Also which is the best way to display the duration in the dashboard when you have over 1000 tasks?

Looking forward for your help.

MichaelTCA

Hello,

The only thing I can think of is selecting the whole column and changing the column type or changing the format that way.

You could try changing the whole column to a text/number. Save. Refresh.

Then change it back to a date. Save. Refresh.

Maybe that will work.

Definitely use a report. Group the report, collapse the groups, and summarize the data. You can easily create tables and graphs from reports. The size of the columns on the report dictate the size of the columns displayed in the dashboard. I try to make it a habit that the first column on the left side of the table is the x-value and the rest are y-values (even though you can swap it around within the dashboard widget).

Christiana Gkini

Hello here,

I have managed to get this formula working for my sheet. However, my question now is, what is the best way to display the Duration column on a dashboard, when I have over 1500 rows in my sheet? Is there a way to break it down by ticket type and take the average ?

I tried to do this as below, but not sure what these numbers really indicate. So ideally I would like to use the Duration Column (as shown in the picture above) as it is more clear.

Looking forward for some help here.

Thank you!