MOSS 2007-Networkdays formula

All Replies

• Tuesday, June 05, 2007 10:50 AM

   

   
  

  0
  My first question would be what is a networkday and how does it differ from a 24-hour day?
   
• Wednesday, June 06, 2007 2:37 AM

   

   
  

  0
  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!!!!
  
   
• Friday, August 03, 2007 4:39 PM

   

   
  

  0

  Did you got something?

  I have the same problem?

   

  Thanks

   

   
• Friday, November 09, 2007 6:32 PM

   

   
  

  0
  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
  •  
   
• Wednesday, November 14, 2007 1:00 AM

   

   
  

  0

   

  Thanks for the post Krishnan!

   

  Is there a way that I can add holidays?

   

  Rob

   
• Monday, June 09, 2008 6:44 AM

   

   
  

  0

  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
  •  
   
• Saturday, April 04, 2009 8:41 PM

   

   
  

  0
  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
  •  
   
• Wednesday, December 02, 2009 12:58 AM

   

   
  

  0
  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 :)
   
• Tuesday, July 20, 2010 6:45 AM

   

   
  

  0

  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