Ms Project Custom Fields Error
Contents |
(עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeOnline20132010Other VersionsLibraryForumsGallery Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: How do I avoid #Error results in Microsoft Project Formulas Project > Project Standard and Professional General
Ms Project Formulas Examples
Questions and Answers Question 0 Sign in to vote Hi - Is there ms project iif function a way of detecting errors in Microsoft Project 2010 formulae resulting from effects other than dividing by zero (which obviously projdatevalue function can be prevented)? For example, when a formula refers to an Actual Start which has a value NA, #Error results. I can't find a way of detecting the NA or the #Error in
Ms Project Datediff
order to avoid unsightly results in the table. Can anyone help? With thanks Skerpie Sunday, March 03, 2013 10:29 PM Reply | Quote Answers 1 Sign in to vote The #Error just needs to be found through testing - although, often hitting F9 to refresh the calculations will resolve it. To test for NA, you need to test for ProjDateValue('NA'), i.e. "=IIF([Actual Start]=ProjDateValue('NA'),"Unstarted","Started")"Andrew Lavinsky [MVP] Blog:
Projdatediff
http://azlav.umtblog.com Twitter: @alavinsky Marked as answer by Rod GillMVP, Moderator Monday, March 04, 2013 2:41 AM Monday, March 04, 2013 1:55 AM Reply | Quote Moderator 0 Sign in to vote Hi, Iif([Baseline Duration]>0,((([Finish Variance]/[Baseline Duration])*10) & "%"),"") Greetings, Proposed as answer by Max_Pin Wednesday, July 03, 2013 7:28 AM Marked as answer by Jan De Messemaeker - Project MVPMVP, Moderator Wednesday, July 03, 2013 1:02 PM Wednesday, July 03, 2013 7:14 AM Reply | Quote Moderator All replies 1 Sign in to vote The #Error just needs to be found through testing - although, often hitting F9 to refresh the calculations will resolve it. To test for NA, you need to test for ProjDateValue('NA'), i.e. "=IIF([Actual Start]=ProjDateValue('NA'),"Unstarted","Started")"Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky Marked as answer by Rod GillMVP, Moderator Monday, March 04, 2013 2:41 AM Monday, March 04, 2013 1:55 AM Reply | Quote Moderator 0 Sign in to vote Hi Andrew, On the same lines i am having a custom field to calculate SV% and i get the same #error for a few fields; how can i use the following formula not to get #error and to replacce #error with zero or blank (
Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Project Management beta Questions Tags Users Badges Unanswered Ask Question _ Project Management Stack Exchange is a question and answer site for project managers. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question https://social.technet.microsoft.com/Forums/projectserver/en-US/71ae82f4-d58a-46ef-b8d2-1c1a3e9a654a/how-do-i-avoid-error-results-in-microsoft-project-formulas?forum=projectprofessional2010general Anybody can answer The best answers are voted up and rise to the top #ERROR in User-Defined Field Formula Even After Testing Against ProjDateValue('NA') up vote 0 down vote favorite I've been struggling with a Microsoft Project (2013 Standard) formula assigned to a number user-defined field to calculate the difference between tasks' current Finishes and a baseline Finish I've previously set. Switch( [Finish]="",0, [Baseline1 http://pm.stackexchange.com/questions/15766/error-in-user-defined-field-formula-even-after-testing-against-projdatevaluen Finish]="",0, [Finish]=projdatevalue('NA'),0, [Baseline1 Finish]=projdatevalue('NA'),0, [Finish]-[Baseline1 Finish]<=0,0, [Finish]-[Baseline1 Finish]>0,[Finish]-[Baseline1 Finish] ) On tasks for which [Baseline1 Finish] is "NA" I consistently get a #ERROR, even though I should have trapped for it between lines 3 & 5 above. I've tried everything I can think of, such as: Using an equivalent IIF formula Using different user-defined date fields (e.g., [Baseline2 Finish]) Removing different elements of the formula one at a time Checking against a "high" date value (e.g., 2^30, since this is how I understand Project actually renders "NA" behind the scenes) instead of using ProjDateValue (c.f. http://masamiki.com/project/customfieldFAQ.htm) But no seeming change in behavior. Is there a defect in Project when using ProjDateValue in a formula against one of the user-defined date fields? ms-project ms-project2013 share|improve this question edited Aug 6 '15 at 15:46 asked Aug 4 '15 at 20:08 JimMSDN 1208 add a comment| 2 Answers 2 active oldest votes up vote 1 down vote accepted Here are a few things to note: Instead of checking the task Finish field for N/A or 0, use the IsDate function. While N/A values for date fields are internally stored as very large in
problem http://www.domorethanmanage.com/articles/2008/02/19/ERRORforProjectCustomFiel.html with Project Server 2007 where any formula put into a project-level custom field would come back with #ERROR. It did not matter whether the formula was a calculation or just a reference to another field the result was #ERROR in Project ms project Professional. (It would show up blank in the PWA Project Center.) Task and resource level custom fields with formulas continued to work properly. There were no errors in the ULS logs, SQL logs, or Event log. We flipped emails back and forth ms project custom to attempt to address the problem: Checked Project's calculation mode -- it was automatic Closed and reopened Project Cleared local cache (in case of corruption) Created a simple formula (=1) -- it still failed Eventually we struck gold when we checked other formulas for problems or corruption. There was an old custom field with a formula that referenced another custom field that had been deleted. It was failing and all of the formulas calculating after it were failing too. Once we removed the problem formula and did a recalc, all the results from formulas showed up correctly. Posted by Gord Schmidt on Tuesday, February 19, 2008 | Permalink | Resolve Predecessor: Microsoft Project Conference 2007 Presentations Successor: Coming Up Next: a 64-Bit World