locked
Networkdays in Sharepoint calculated column RRS feed

  • Question

  • Can somebody help me in translating this to sharepoint calculated formula?

    =IF((NETWORKDAYS(Q4,P4)<7),"Unplanned","Planned")

     

    Thanks in advance.

    Thursday, May 12, 2011 5:20 PM

Answers

  • Ravi, 

    I do understand what you are requirements are. The formula also works in the same way. The only condition you would need to take  a stand on is, you would need to have the created date always less than Date of execution. (Created Date should always occur before date of execution)

    In the below case, where you were seeing the result "#Num!" is because your created date was after Date of Execution. Also, the Created Date is read only date and Date of Execution (probably) occurs only after date of creation. Is this a right assumption?

    You would need to tell me, whether created date or date of execution occurs first. This needs to be consistent when data is being entered. I hope this is clear.   

    5/11/2011       5/12/2011 10:58
    #NUM!

     


    V
    • Marked as answer by Peng Lei Monday, May 23, 2011 8:32 AM
    Wednesday, May 18, 2011 11:22 AM

All replies

  • The above code Networkdays(Q4,P4) is used in excel, it typically means calculate difference of days between P4 and Q4. If difference is less than 7, it is unplanned, else it is planned.

    Below your Q4 and P4 are startdate and completed date respectively

    =IF(((DATEDIF([Start Date],[Completed Date],”d”))-INT(DATEDIF([Start Date],[Completed Date],”d”)/7)*2-IF((WEEKDAY([Completed Date])-WEEKDAY([Start Date]))>0,2,0)+1)<7), "Unplanned","Planned"))

    Let us know if this has helped you.


    V
    Thursday, May 12, 2011 5:45 PM
  • Tried and got this error.

     

    "The formula contains a syntax error or is not supported."

     

    Not sure how to proceed.

    Thursday, May 12, 2011 6:04 PM
  • Try this now.

    =IF(((DATEDIF([Start Date],[Completed Date],"d"))-INT(DATEDIF([Start Date],[Completed Date],"d")/7)*2-IF((WEEKDAY([Completed Date])-WEEKDAY([Start Date]))>0,2,0)+1<7),"Unplanned","Planned")

    SaiDinesh, I have tried the above code and it worked. Do let us know if it did work for you.


    V
    • Marked as answer by Abinash Ravi Friday, May 13, 2011 6:12 AM
    • Unmarked as answer by Abinash Ravi Friday, May 13, 2011 10:08 AM
    Thursday, May 12, 2011 6:13 PM
  • Yes. i can see the values populated now. But seems to be incorrect. Let me explain my requirement. Pls help me in getting this solved.

     

    There are two columns say "Active date" and "Created", the third calculated column should give the text as "Planned", if "Created" date is 5 networkdays(working days) lesser than "Active date". Else it should be "Unplanned".

     

    Eg: Active Date : May-23-2011; Created : May-13-2011 Value: Planned

    Because there are 5 working days between 13 and 23 (i.e. 16, 17, 18, 19, 20)

    if Created : May-15-2011 or May-14-2011, still the value should be Planned, because they are weekends.

    But if Created : May-16-2011, the value should be planned.

    Ultimately if there are 5 days between Active date and created, excluding those two dates, the value should be planned. else unplanned.

    Pls let me know if i am not clear.

     

    TIA


    • Marked as answer by Abinash Ravi Friday, May 13, 2011 6:12 AM
    • Unmarked as answer by Abinash Ravi Friday, May 13, 2011 6:18 AM
    Friday, May 13, 2011 6:12 AM
  • Ravi,

    Try this code.

    =IF(((DATEDIF([Start Date],[Completed Date],"d"))-INT(DATEDIF([Start Date],[Completed Date],"d")/7)*2-IF((WEEKDAY([Completed Date])-WEEKDAY([Start Date]))<0,2,0)+1>5),"Unplanned","Planned")

    Replace Start date with Active Date and Created Date with Completed Date. Also, do let us know if it did or did not work for you.


    V
    Friday, May 13, 2011 1:44 PM
  • Calculated column shows as

    #NUM!

    Friday, May 13, 2011 4:17 PM
  • Ravi,

    While entering the formula, select "Number" as the data type returned from this formula. That should fix it. Let us know the result.


    V
    Friday, May 13, 2011 7:44 PM
  • Tried with "Number" and got the same issue.

    If my understanding is correct, the formula should return only "Planned" or "Unplanned". In that case isn't the right way to keep the data type as Text. Please correct me if i am wrong.

    Saturday, May 14, 2011 8:38 AM
  • V,

    Please let me know as how to proceed.

    TIA

    Saturday, May 14, 2011 4:23 PM
  • Ravi, 

    The formula works for me, can you paste the forumla you are using? Also,would appreciate if you could upload screenshots of the list. and calculated column.


    V
    Saturday, May 14, 2011 6:56 PM
  • =IF(((DATEDIF([Date of Execution],[Created],"d"))-INT(DATEDIF([Date of Execution],[Created],"d")/7)*2-IF((WEEKDAY([Created])-WEEKDAY([Date of Execution]))<0,2,0)+1>5),"Unplanned","Planned")

     

    SNAPSHOT:

    5/11/2011 5/10/2011 3:12 PM
    #NUM!
    5/11/2011 5/11/2011 1:03 PM
    Planned
    5/11/2011 5/11/2011 1:34 PM
    Planned
    5/12/2011 5/11/2011 7:11 PM
    #NUM!
    5/12/2011 5/12/2011 10:28 AM
    Planned
    5/11/2011 5/12/2011 10:58 AM
    Planned
    5/12/2011 5/12/2011 2:52 PM
    Planned
    5/10/2011 5/13/2011 12:39 PM
    Planned

    Sunday, May 15, 2011 5:56 AM
  • Ravi, 

    You are probably confused with the columns, Created and Date of Execution. In my earlier formula, start date and completion dates are similar to "Created" date and "Date of Execution" respectively. You were using those two fields in the wrong place. The formula was right, all you have to do is interchange those fields.

    =IF(((DATEDIF([Created],[Date of Execution],"d"))-INT(DATEDIF([Created],[Date of Execution],"d")/7)*2-IF((WEEKDAY([Date of Execution])-WEEKDAY([Created]))<0,2,0)+1>5),"Unplanned","Planned")

    Let us know the result. 

     

     


    V
    Sunday, May 15, 2011 1:11 PM
  • V,

    Copy pasted the about formula and got the same issue.

    5/11/2011 5/10/2011 3:12 PM
    Planned
    5/11/2011 5/11/2011 1:03 PM
    Planned
    5/11/2011 5/11/2011 1:34 PM
    Planned
    5/12/2011 5/11/2011 7:11 PM
    Planned
    5/12/2011 5/12/2011 10:28 AM
    Planned
    5/11/2011 5/12/2011 10:58 AM
    #NUM!
    5/12/2011 5/12/2011 2:52 PM
    Planned
    5/10/2011 5/13/2011 12:39 PM
    #NUM!

     


    Sunday, May 15, 2011 5:23 PM
  • The above formula works based on the condition that, date of Execution is after date of creation or date of execution is same as created date. In the case where #NUM! are displayed, it is because of the same reason. You will need to be consistent with the dates.
    V
    Monday, May 16, 2011 1:23 PM
  • Sorry for not explaining my requirement clearly.

     

    I have two columns.

    User created – “Date of Event”

    System created – “Created”

     

    If the “Created” is 5 working days less than “Date of Event”, the calculated column should return Planned; else unplanned.

    Eg: Created :  18-May-11; Date of Event : 26-may-11        result = Planned

    Created :  18-May-11; Date of Event : 25-may-11               result = Un Planned

    Created :  27-May-11; Date of Event : 26-may-11               result = Un Planned

     

    Networkdays formula helped me to calculate this in excel. Need to automate this in sharepoint.

    =IF((NETWORKDAYS(Q4,P4)<7),"Unplanned","Planned")

     

    Please let me know if I am not clear.

    Wednesday, May 18, 2011 2:22 AM
  • Ravi, 

    I do understand what you are requirements are. The formula also works in the same way. The only condition you would need to take  a stand on is, you would need to have the created date always less than Date of execution. (Created Date should always occur before date of execution)

    In the below case, where you were seeing the result "#Num!" is because your created date was after Date of Execution. Also, the Created Date is read only date and Date of Execution (probably) occurs only after date of creation. Is this a right assumption?

    You would need to tell me, whether created date or date of execution occurs first. This needs to be consistent when data is being entered. I hope this is clear.   

    5/11/2011       5/12/2011 10:58
    #NUM!

     


    V
    • Marked as answer by Peng Lei Monday, May 23, 2011 8:32 AM
    Wednesday, May 18, 2011 11:22 AM