ValueExpression Syntax Help

[BenettaPe]

For a column in a report:

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

valueformat=HTML

Problem 1: moving the commas and the parenthesis makes the difference and I can't seem to get them in the right place. When First Submit is complete, the actualcompletiondate will display, however, if First Submit is not complete, then the second part of this multiple nested IF does not display anything. so there exist a syntax error. Please Help here....

Problem 2. I really want actualCompletionDate to ultimately display the Month only - can't figure out if there is a function to do this, but I can live with the full date being displayed, if there is not a way to only display the month. I've searched the help for hours and found nothing.

Problem 3. How do I add 30 days to the actualCompletionDate, meaning if the date is 1/15/2021, I want it to display 2/15/2021

Problem 4. After I get this working, with your help - please - I would like to convert it ultimately to a calculated custom field to be used site wide vs just in a column of my report - whose syntax is very different.

Thanks in advance for helping me with these syntax problems.

[NickVa1]

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

[AnthonyIm]

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

[MattMc2]

Thank you, Anthony. This is a really well laid out answer - I have used your tips before (e.g. sharecol). Quick Question, how would you rewrite scenario if status did not equal CPL?

[AnthonyIm]

You would put the other two IF statements in the FALSE area. (Now some people use != but I don't get that to work all the time so prefer just switching the formula around to use the false values)

So...

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

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

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

Then...

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

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

Then...

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

Finally...

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

[MattMc2]

Awesome, thanks Anthony! It seems obvious now - I was down a rabbit hole messing about with "Mod=notin". Thanks, again!