+ Reply to Thread
Results 1 to 8 of 8

calculate business days(where there is 6 days a week)

  1. #1
    Forum Contributor
    Join Date
    02-11-2008
    Posts
    238

    calculate business days(where there is 6 days a week)

    Hi,
    I want to calculate net business days between two dates,I have used networkdays() but its not giving desired result.
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: calculate business days(where there is 6 days a week)

    Hi

    You need to tell us what results you expect and why.

    Regards

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: calculate business days(where there is 6 days a week)

    Which 6 days do you want to count? Assuming all days except Sundays you can use this formula in row 2 copied down

    =SUM(INT((WEEKDAY(C2-{2,3,4,5,6,7})+N2-C2)/7))

    If you have Excel 2010 you can use NETWORKDAYS.INTL function, i.e.

    =NETWORKDAYS.INTL(C2,N2,11)
    Audere est facere

  4. #4
    Forum Contributor
    Join Date
    02-11-2008
    Posts
    238

    Re: calculate business days(where there is 6 days a week)

    I need to calculate difference between column N and column C,and the formula would able to identify if there is any sunday in between the dates and exclude that date.

  5. #5
    Forum Contributor
    Join Date
    02-11-2008
    Posts
    238

    Re: calculate business days(where there is 6 days a week)

    Thanks for the formula,but if you look at the first row,although by normal substraction the difference comes 1 days,but using your formula its coming 2 days,we need to rectify it.

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: calculate business days(where there is 6 days a week)

    The formula I suggested works like NETWORKDAYS in that it include both start and end date, e.g. like NETWORKDAYS it will count 2 for a date range starting on a Tuesday and ending the next day (because it counts both the Tuesday and the Wednesday).

    If you want the count to be 1 in that instance then you can just subtract 1, i.e. use this formula.

    =SUM(INT((WEEKDAY(C2-{2,3,4,5,6,7})+N2-C2)/7))-1

    ....but if the start or end date of the range might be a Sunday then that might not give you the results you expect. What result do you expect if start date is Sunday and end date the following Tuesday....or Friday to Sunday?



  7. #7
    Forum Contributor
    Join Date
    02-11-2008
    Posts
    238

    Re: calculate business days(where there is 6 days a week)

    The start date cannot be a sunday,but the end date can be a sunday

  8. #8
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: calculate business days(where there is 6 days a week)

    OK, then by your definition Friday to Saturday =1, Friday to Monday = 2, is that right? so what should Friday to Sunday be? still 1? If so then the last formula I suggested should work for you, i.e.

    =SUM(INT((WEEKDAY(C2-{2,3,4,5,6,7})+N2-C2)/7))-1

+ 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