Using IF and Contains in a calculated fields

[ThomasNe2]

I am trying to create a calculated field for a series of Tasks.

If the Task name Contains the number "1" I need it to display a date from the Project custom form.

I was able to return a True/False result using CONTAINS and I have been able to display the date without a condition but when combining the IF and CONTAINS functions I am getting no result.

Here is my expression - can anyone help?

IF(CONTAINS({name},"1"),{DE:UKI - Bet Offer 1 Banner Live Start Date & Time},"")

[_Manish_Singh]

Hello @ThomasNe2, Please try this..

IF(CONTAINS("1",{name}),{DE:UKI - Bet Offer 1 Banner Live Start Date & Time},"")

[ThomasNe2]

Hi Manish - that's giving me N/A unfortunately. I have checked that the {name} is pulling from Task and the Custom Field is pulling on Project. 

[_Manish_Singh · Accepted solution]

Hmm.. It would be..

IF(CONTAINS("1",{name}),{project}.{DE:UKI - Bet Offer 1 Banner Live Start Date & Time},"")

[ThomasNe2]

That is working a charm! Thank you! How can I stack this up with more conditions? For example now if the Task name contains a 2 I would now like another field to be pulled in? I have copied the expression and added it after separate with a comma how I would anticipate but I am getting an error!

Thanks so much for the help!

[_Manish_Singh]

We will replace the else ("") portion from the formula with more conditions. Something like below. Is this what you are trying to do?

IF(
  CONTAINS("1",{name}),
  {project}.{DE:UKI - Bet Offer 1 Banner Live Start Date & Time},
IF(
  CONTAINS("2",{name}),
  {project}.{DE:UKI - Bet Offer 2 Banner Live Start Date & Time},
IF(
  CONTAINS("3",{name}),
  {project}.{DE:UKI - Bet Offer 3 Banner Live Start Date & Time},
""
)))

[ThomasNe2]

It's not liking that at all haha invalid expression

[_Manish_Singh]

Could you replace Bet Offer 2 Banner and Bet Offer 3 Banner from my expression with the actual fields from your system and check. If it doesn't work, post it here and I will have a look.

[ThomasNe2]

That's great! Been able to get it to work on several other fields now with those changes! Thanks Manish!

[ThomasNe2]

Hi Manish - I have trialled this for a while and it's working great but I need the expressions to be able to update in real time without being prompted - how could that expression be translated to work in a view?

[_Manish_Singh]

Hello Thomas, the calculated field updates in real-time. I'm not sure what you are referring to here. Are you talking about the report view? If so, you can directly add the calculated field in the report without an expression.