Calculate average business days to complete task

[jent]

I would like help to calculate the average business days a task takes to complete and then be able to divide that total to get average by month. My spreadsheet contains data about the work tickets over the past year with a column for data started, date completed. I used the following formula found on this forum to get the average time to complete a task (=IF(A1, A1-B1, "")) but now need to ensure only business days are calculated. In addition, I need to be able to get average time to complete tasks over a month date range.

I do have rows where the end date is not entered yet so I really like that the above formula takes that into account.

I searched for a past solution to this issue but didn't find anything that covered it exactly. Hope I am not creating a duplicate entry here. Thanks in advance for your help!

[jeffreybrown]

How does one know whether it is a business day or not?

[jent]

I would like to eliminate Saturdays & Sundays from the calculation. Thanks!

[jeffreybrown]

Do you have a sample to post?

[jent]

Tracking Sample.xlsx

I would like to calculate Column M - Column F when populated eliminating weekend days. From there I'd like to slice the data to get a monthly average time to complete as well. Thanks!

[jeffreybrown]

Thank you for the sample.

Two observations. The data in this file has no dates which are either a Sat or Sun. Also, in column M there is a time included with the date. Do you want the time excluded? For example, M2 is 7/6/2012 9:49 AM and F2 is 7/6/2012. In this case, 7/6/2012 - 7/6/2012 is 0.

Once you have the calcualtion, you could put this in a pivot table to get the average by month. Is that an option for you?

[jent]

Agreed - no work would either be started or completed on a Sat/Sun but may start on a Friday, complete on a Tuesday so in those cases I want the calculated days to complete to be 3 days vs 5 days.

I could include the time in the calculation. I was thinking my data maybe didn't include the time value for all my data columns but I have verified it is available in the data. Please include that in your formula if possible. That will ultimately give a more accurate calculation.

I can put the calculation in a pivot table - just not too familiar with those at this point.

[jeffreybrown]

Give this a try

[jent]

Awesome! And the new formula in Column S takes out the weekend days, correct?

[jeffreybrown]

It doesn't specifically say take out Sat or Sun, but the formula account for a change in weeknumbers and then calculates out the number of days.

Look at column S and see if the number of days is correct based off of column M and F.

[jent]

OK, I believe that works. wondering how the new formula reacts when there is no data in column F? There are orders on the list that have not completed yet so the formula will need to disregard those lines where no date is present. Does it do that now?

[jeffreybrown]

I updated the attachment in post #8

[jent]

I tried to apply the formula you'd sent to my spreadsheet with all the year's data in it but it doesn't seem to be working correctly. I am no longer getting a value for June which I would think I would. In addition, July's average is currently calculating out to be an average of 24.5 days to complete a task. I thought I had modified the formula to use all data in the file but maybe I made an error somewhere. Thanks for your continued help!

[jeffreybrown]

Look at the formula in V2. You need to adjust the ranges for the entire formula. You missed the ranges on the divided by part of the formula.

[jent]

Thanks! All works great now! Thread closed.