locked
Workdays left on project RRS feed

  • Question

  • Hello:

    I am a sharepoint novice. Using Sharepoint 3.0

    In Excel I can calculate the number of workdays left between 2 dates. =NETWORKDAYS(Todays date,Project end Date)-2

    the -2 represents non-working holidays.

    Is there a way to create a webpart on a sharepoint page to do what I do in Excel?

    If possible please provide detailed instructions how to create.

    Thank you

    Eric

    • Moved by Mike Walsh FIN Friday, August 26, 2011 8:23 AM admin q not general (From:SharePoint - General Question and Answers and Discussion (pre-SharePoint 2010))
    Thursday, August 25, 2011 8:19 PM

Answers

  • Hi Eric,

     

    The formula is used to a calculated column in a list, you can display a list in a web part within a page.

     

    For excluding 2 days for holidays, you can “-2” in the last 2 parameters like this,

     

    =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)-2),((((DateDIF([Start Date],[End Date],"D")+1))-(FLOOR((DateDIF([Start Date],[End Date],"D")+1)/7,1)*2))-2))

     

    Thanks


    Regards, Daniel
    Wednesday, August 31, 2011 11:29 AM
    Moderator

All replies

  • HI,

     

    Excel Networkdays function implementation in sharepoint realted question is already postedin MS Technet forum.

    http://social.technet.microsoft.com/forums/en-US/sharepointgeneral/thread/d71cb2b8-72f7-4b9f-a5c9-7c08a9ccf6c9/

    Refer this url, this will help you.

    Regards

    Friday, August 26, 2011 5:01 AM
  • Thanks Nehruraj.

    I think I can use this formula that I found from your previous link post.

    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)))

    How do I exclude a certain number of company holidays in the formula?

    Can I paste this code into a webpart? In the link from above they talk about adding a column. I am a novice sharepoint user.

    Thank you

    Eric

    Friday, August 26, 2011 2:28 PM
  • Hi Eric,

     

    The formula is used to a calculated column in a list, you can display a list in a web part within a page.

     

    For excluding 2 days for holidays, you can “-2” in the last 2 parameters like this,

     

    =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)-2),((((DateDIF([Start Date],[End Date],"D")+1))-(FLOOR((DateDIF([Start Date],[End Date],"D")+1)/7,1)*2))-2))

     

    Thanks


    Regards, Daniel
    Wednesday, August 31, 2011 11:29 AM
    Moderator