+ Reply to Thread
Results 1 to 15 of 15

Calculate average business days to complete task

  1. #1
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas City, KS
    MS-Off Ver
    Excel 2010
    Posts
    8

    Calculate average business days to complete task

    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!

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: Calculate average business days to complete task

    How does one know whether it is a business day or not?
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas City, KS
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Calculate average business days to complete task

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

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: Calculate average business days to complete task

    Do you have a sample to post?

  5. #5
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas City, KS
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Calculate average business days to complete task

    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!

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: Calculate average business days to complete task

    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?

  7. #7
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas City, KS
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Calculate average business days to complete task

    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.

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: Calculate average business days to complete task

    Give this a try
    Attached Files Attached Files
    Last edited by jeffreybrown; 07-09-2012 at 06:57 PM. Reason: Updated attachment

  9. #9
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas City, KS
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Calculate average business days to complete task

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

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: Calculate average business days to complete task

    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.

  11. #11
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas City, KS
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Calculate average business days to complete task

    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?

  12. #12
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: Calculate average business days to complete task

    I updated the attachment in post #8

  13. #13
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas City, KS
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Calculate average business days to complete task

    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!
    Attached Files Attached Files

  14. #14
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: Calculate average business days to complete task

    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.

  15. #15
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas City, KS
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Calculate average business days to complete task

    Thanks! All works great now! Thread closed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1