locked
MOSS 2007-Networkdays formula

    Question

  • Hi,
    I am trying to add a column with calcuation of networkdays.I found the calculation formular for the difference for days but not for networkdays.
    I was trying to use general excel networkdays formula such as =networkdays(date, date) but it doesn't accept?
    Is there any way that I can calculate the networkdays in column?
    Thanks
    Monday, June 04, 2007 3:22 AM

Answers

  • SharePoint NETWORKDAYS Formula (for Start and End dates that are Week days)
    =IF(AND((WEEKDAY([End Date],2))<(WEEKDAY([Start Date],2)),((WEEKDAY([Start Date],2))-(WEEKDAY([End Date],2)))>1),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)-2),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)))

     

    I had success in using the above formula, that gives results similar to Excel's NETWORKDAYS, as long as the 'Start Date' and 'End Date' are week days. If either or both dates are Saturday or Sunday, then they are counted too.

    • Proposed as answer by looptloop Saturday, April 04, 2009 8:40 PM
    • Marked as answer by Mike Walsh FIN Sunday, April 05, 2009 6:30 AM
    Friday, November 09, 2007 6:32 PM

All replies

  • My first question would be what is a networkday and how does it differ from a 24-hour day?
    Tuesday, June 05, 2007 10:50 AM
  • Hi,

    Networkdays shows the days without weekend. Because when you count working days you don't need to include the weekends. But you just use the calculation to find out the days difference between two, it shows with weekends which is more than actual working days.
    In excel, you use =networkdays (C1,C2) but i don't know how i can find the formula to calculate networkdays.

    I hope this helps and please someboy help me on this!!!!
    Wednesday, June 06, 2007 2:37 AM
  • Did you got something?

    I have the same problem?

     

    Thanks

     

    Friday, August 03, 2007 4:39 PM
  • SharePoint NETWORKDAYS Formula (for Start and End dates that are Week days)
    =IF(AND((WEEKDAY([End Date],2))<(WEEKDAY([Start Date],2)),((WEEKDAY([Start Date],2))-(WEEKDAY([End Date],2)))>1),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)-2),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)))

     

    I had success in using the above formula, that gives results similar to Excel's NETWORKDAYS, as long as the 'Start Date' and 'End Date' are week days. If either or both dates are Saturday or Sunday, then they are counted too.

    • Proposed as answer by looptloop Saturday, April 04, 2009 8:40 PM
    • Marked as answer by Mike Walsh FIN Sunday, April 05, 2009 6:30 AM
    Friday, November 09, 2007 6:32 PM
  •  

    Thanks for the post Krishnan!

     

    Is there a way that I can add holidays?

     

    Rob

    Wednesday, November 14, 2007 1:00 AM
  •  Krishnan P wrote:
    SharePoint NETWORKDAYS Formula (for Start and End dates that are Week days)
    =IF(AND((WEEKDAY([End Date],2))<(WEEKDAY([Start Date],2)),((WEEKDAY([Start Date],2))-(WEEKDAY([End Date],2)))>1),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)-2),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)))

     

    I had success in using the above formula, that gives results similar to Excel's NETWORKDAYS, as long as the 'Start Date' and 'End Date' are week days. If either or both dates are Saturday or Sunday, then they are counted too.

     

    Unfortunately this formula takes Sundays and Mondays as the weekend days, while here we use Saturdays and Sundays. I think it has something to do with WEEKDAY?

    • Proposed as answer by looptloop Saturday, April 04, 2009 8:26 PM
    • Unproposed as answer by Mike Walsh FIN Sunday, April 05, 2009 6:30 AM
    Monday, June 09, 2008 6:44 AM
  • Here is another possible solution that does account for the start & end dates being on the weekend. It is essentially similar to the Excel NETWORKDAYS function but yields a value that is one day less -- on purpose!

    =IF(DATEDIF(StartDate,EndDate,"d")-(((DATEDIF(StartDate,EndDate,"d")+WEEKDAY(StartDate,3)-WEEKDAY(EndDate,3))/7)*2)+1*(WEEKDAY(StartDate,3)>5)-(WEEKDAY(EndDate,3)-4)*(WEEKDAY(EndDate,3)>4)<0,0,(DATEDIF(StartDate,EndDate,"d")-(((DATEDIF(StartDate,EndDate,"d")+WEEKDAY(StartDate,3)-WEEKDAY(EndDate,3))/7)*2)+1*(WEEKDAY(StartDate,3)>5)-(WEEKDAY(EndDate,3)-4)*(WEEKDAY(EndDate,3)>4)))

    NOTE: The version below has a shorter formula but will have a -1 value in certain cases where the start and end days are on the same exact weekend,
    but this should not be of any real significance during typical business usage.

    =DATEDIF(StartDate,EndDate,"d")-(((DATEDIF(StartDate,EndDate,"d")+WEEKDAY(StartDate,3)-WEEKDAY(EndDate,3))/7)*2)+1*(WEEKDAY(StartDate,3)>5)-(WEEKDAY(EndDate,3)-4)*(WEEKDAY(EndDate,3)>4)

    • Proposed as answer by looptloop Saturday, April 04, 2009 8:43 PM
    • Unproposed as answer by Mike Walsh FIN Sunday, April 05, 2009 6:29 AM
    Saturday, April 04, 2009 8:41 PM
  • Hi Krishnan,

    I've usd your formula and it works almost perfectly.  I'm using it for a library system i'm woking on.  I've changed Start Date and End Date to Check out Date and Due Date respectively.  The problem I'm getting is that it's returning  an answer of 1 even though no dates are selected.

    Could you shed some light on this for me??

    many thanks :)
    Wednesday, December 02, 2009 12:58 AM
  • hi pssmithy,

    am using krishnan's formula amd it works well but i had to remove the +1 befor the floor function.............now am looking for a way to include public holidays i waz thinking of creating another list with the holidays then a workflow to check if a public holiday falls in between the start and end date the workflow deducts  those days from the result of this formula.......

    pssmitthy did you get a solution ????

    sharepoint rob change the first day of the week to monday in regional settings hopefuly that will fix inclusion of mondays as a weekend

     

     

    David

    Tuesday, July 20, 2010 6:45 AM