none
Formula Gets Corrupted RRS feed

  • Question

  • I created the following formula to show a RAG (traffic light) status in a Text Field in MS Project 2010:

    Switch([% Complete]=100,"C",[Number1]=99 And [Baseline Finish] >= #1/1/1984# And [Baseline Finish]  < #1/1/2050#, "TR",
    [Number1]<>99 And [Baseline Estimated Finish] >= #1/1/1984# And [Baseline Estimated Finish]  < #1/1/2050#, "R",
    [Number1]=99 And Status=2,"TR",[Number1]=99 And [Finish]>[Baseline Finish],"TR",[Number1]=99 And [Text8]<>"","TA",[Number1]<>99 And [Status]=2,"R",[Number1]<>99 And [Finish]>[Baseline Finish],"R",[Number1]<>99 And [Text8]<>"","A",
    [Number1]<=99,"G")

    However, after running it once, it stops working.  I look in the Edit Formula Dialog and this is what I see:

    Switch([% Complete]=100,"C",[Number1]=99 And [Baseline Estimated Finish]>=#01/01/1984# And [Baseline Estimated Finish]<#01/01/205,#,,TR",[Number1]<>99 And [Baseline Estimated Finis#01/01/1984#984# And [Baseline Estimated Fini#01/01/205,#,50#,"R",[Number1]=99 And ,tatu,=2,"TR",[Number1]=99 And [Finish]>[Baseline,Fini,h],"TR",[Number1]=99 And [Te,t8]<,"","TA",[Number1]<>99 And [S,atu,]=2,"R",[Number1]<>99 And [Finish]>[Baseline,Fin,sh],"R",[Number1]<>99 And [Te,t8],>"","A",[Numb,r1]<=99,"G")

    So Project has inserted bits of text, commas and substituted letters.  The funny thing is, that this is consistent.  I deleted the field, saved the file, shut down MSP, and recreated the formula.  The corruption is exactly the same as the one above.

    I've looked for folks with similar problems online and for once, I have come up with nothing.

    It seems to be the # character, as when I omit these it doesn't corrupt, but it doesn't work either.

    Can anyone tell me what's going on here?

    Mikey

    Thursday, August 10, 2017 1:51 PM

All replies

  • Mikey,

    Is your version of Project 2010 fully updated? It should be version 14.0.7176.5000. If it is not, run Windows Update.

    Assuming you are fully updated, the answer to your question is, your file is corrupt and simply replacing the formula won't fix that. I suggest you save the file as XML and then re-open the XML file in Project. Note, you will lose any custom formatting with this process.

    If that doesn't clear the problem, then you can try doing a repair of Project through the Windows Control panel. If that doesn't help, locate your working Global and move it to the trash. Then re-start Project, a new default Global will be generated. Note, you will lose any custom views, fields, filters, etc. using this process.

    Hope this helps.

    John

    Thursday, August 10, 2017 3:44 PM
  • John, I apologize for jumping in on the thread.

    Mikey, I would say just replace the date literals with the DateValue function. For example, use DateValue("1/1/2050") in place of #1/1/2050#. IMO, the formula evaluator gets confused when it encounters with multiple number signs in the formula. 

    Hope this works. 


    Ismet Kocaman | eBook on Formulas

    Thursday, August 10, 2017 3:58 PM
  • Ismet,

    No problem, your expertise is always welcome. I wondered about the date literals myself as I always enter dates as strings however I tried his formula as posted and it worked fine under a fully updated Project 2010 test file.

    I also wondered how Project 2010 would react to the 1/1/2050 date even if it is in a "less than" test. I would use <12/31/2049

    John

    Thursday, August 10, 2017 5:59 PM
  • Thank you John.

    I've stopped updating Project 2010 after SP2 since the cumulative updates (they now call these hotfixes as public updates) caused some strange issues. I'll wait for SP3.

    I have experienced the same issue with the date literals while testing formulas in Project 2013. I have just replaced the date literals with the DateValue expressions to solve the problem. As you said, updating Project 2010 may solve Mikey's problem. In order to eliminate the possibility that it might happen again, I would use DateValue. 

    Tests show that the Proj functions comply with Project's range of valid dates. On the other hand, the DateValue function will accept the dates outside that range (dates from January 1, 100 through December 31, 9999).


    Thursday, August 10, 2017 7:55 PM
  • Ismet,

    If you're waiting on SP3 for Project 2010 you've got a loooooong wait. My understanding is that update support for Project 2010 ended in 2016.

    John

    Thursday, August 10, 2017 8:02 PM
  • Ismet,

    If you're waiting on SP3 for Project 2010 you've got a loooooong wait. My understanding is that update support for Project 2010 ended in 2016.

    John

    Well, I recall it now, you are correct. Thank you. 

    Thursday, August 10, 2017 9:49 PM
  • Ismet,

    No problem, your expertise is always welcome. I wondered about the date literals myself as I always enter dates as strings however I tried his formula as posted and it worked fine under a fully updated Project 2010 test file.

    I also wondered how Project 2010 would react to the 1/1/2050 date even if it is in a "less than" test. I would use <12/31/2049

    John

    John,

    Help article says "Project 2010 uses the same Jet Expression service for formulas that Microsoft Access 2010 uses". Therefore, IMO, the date 1/1/2050 will be considered as a valid one in a formula expression. 


    Saturday, August 12, 2017 9:19 PM