Can somebody help me in translating this to sharepoint calculated formula? =IF((NETWORKDAYS(Q4,P4)<7),"Unplanned","Planned") Thanks in advance.

Tried and got this error. "The formula contains a syntax error or is not supported." Not sure how to proceed.

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

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

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 (Active date lesser than May23 including weekends should show unplanned) Pls let me know if i am not clear. TIA

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 (Active date lesser than May23 including weekends should show unplanned) Pls let me know if i am not clear. TIA

Awesome. Working perfect.

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

Calculated column shows as #NUM!

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

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.

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.

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.

V, Please let me know as how to proceed. TIA

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

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

=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: Date of Execution Created Leave Type 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

=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: Date of Execution Created Leave Type 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

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

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

V, Copy pasted the about formula and got the same issue. Date of Execution Created Leave Type 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!

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

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.

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. Date of Execution Created Leave Type 5/11/2011 5/12/2011 10:58 #NUM! V

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. 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. Date of Execution Created Leave Type 5/11/2011 5/12/2011 10:58 #NUM! V