ValueExpression Syntax Help

Hi Benetta,

Without having time at the moment to set up and test out your custom column and valueexpression in my sandbox, I wanted to share two custom field calculations that I have set up in my system that are similar to what you're doing. These aren't custom columns, but I know in your 4th point you were eventually hoping to set these up as custom fields.

* A custom field on the task object that auto-populates the date/time when a task's Status field value changes to Complete:

IF(Status='CPL',IF(ISBLANK(Completed Status Timestamp),$$NOW,Completed Status Timestamp),Completed Status Timestamp)

* A custom field on the project object that auto-populates scores (numbers) based on values of other fields:

IF(Portfolio Name="Microsoft",120,IF(Portfolio Name="Everything But Water",100,IF(Portfolio Name="Yext",90,"")))+IF(Program ID="5f987872039fb36174a7653be2297f06",63,IF(Program ID="5f98787c039fbb4222cbf06e6d8c5519",51,IF(Program ID="5f987c9e03a22f2c5d57336b6a9e11bf",47,"")))+IF(Planned Hours<20,26,IF(Planned Hours>20,43,""))

As for your question about how to add days to the actionCompletionDate, this might be of help.

Hope this helps you on your way. If I get more time I'll dig in a little deeper.

Cheers.

Nick

Hi -

So Problem 1, it is a syntax error. You don't have FALSE representation for all your IF Statements. I also see you have Status=CPL twice so I would make that the first condition so you don't have to have it twice. Personally, I like to write out each statement before combining. So I'm tihnking sometihng like this.

IF({status}="CPL",TRUE,FALSE)

IF({name}="First Submit Date Flag",{actualCompletionDate},FALSE)

IF({name}="Full Access Target Date Flag",{actualCompletionDate},FALSE)

And then you will start from the bottom and replace the TRUEs or FALSEs with the below formula.

IF({status}="CPL",TRUE,FALSE)

IF({name}="First Submit Date Flag",{actualCompletionDate},IF({name}="Full Access Target Date Flag",{actualCompletionDate},FALSE))

And then...

IF({status}="CPL",IF({name}="First Submit Date Flag",{actualCompletionDate},IF({name}="Full Access Target Date Flag",{actualCompletionDate},FALSE)),FALSE)

Finally, replace any TRUEs or FALSEs with a value. So the final statement will be...

IF({status}="CPL",IF({name}="First Submit Date Flag",{actualCompletionDate},IF({name}="Full Access Target Date Flag",{actualCompletionDate},"NA")),"NA")

Problem 2: For this, I would actually have a calculated field that pulls the Month of the Actual Completion Date and then use that in the IF statement for problem 1. I believe the formula for the field is MONTH(Actual Completion Date). So if you called that field "Month of Actual Completion Date", your code for Problem 1 would be

IF({status}="CPL",IF({name}="First Submit Date Flag",{DE:Month of Actual Completion Date},IF({name}="Full Access Target Date Flag",{DE:Month of Actual Completion Date},"NA")),"NA")

Problem 3: You can do a calculated field for this pretty easily. The formula is ADDDAYS(Actual Completion Date,30) or you can do ADDWEEKDAYS(Actual Completion Date,30) if you want to make sure your new date is never a weekend. (Holidays are counted as Week Days) Just PLEASE remember to change the format on the field to Date or Date/Time. Can't tell you how many times I forgot that and left it Text and that is not helpful. LOL!

Problem 4: Confused a little about this one as I'm not sure where Problem 3 fits into this. Do you want it to display the month of the actual completion date or are you looking to display the date that is 30 days in the future? The conversion to IF statement as a custom field isn't too difficult. Instead of having something like {actualCompletionDate} you just have Actual Completion Date. So if you do what we talked about in Problem 2 it would be

IF(Status="CPL",IF(Name="First Submit Date Flag",Month of Actual Completion Date,IF(Name="Full Access Target Date Flag",Month of Actual Completion Date,"NA")),"NA")

Hope this all helps,

Anthony