Expand my Community achievements bar.

Join us LIVE in San Francisco on November 14th for Experience Makers The Skill Exchange. Don't miss out on this free learning event!
SOLVED

Can someone help me with a calculated field? I'm trying to add a field that tells me when a task or issue is added to an iteration. I can't seem to get the expression correct.

Avatar

Level 5

My plan is to add a calculated field that notes when the iteration ID has a value but I can't determine how to do this so that it adds a timestamp of the first time ANY value is present in that field. Here's where I am and this doesn't work:

IF(NOTBLANK{Iteration ID}),IF(ISBLANK({DE:Iteration Assigned Date}),$$NOW,{DE:Iteration Assigned Date}),{DE:Iteration Assigned Date})

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi Melissa,

From this handy Calculated Data Expressions article, I believe the term "ISBLANK" (vs NOTBLANK) is what you're after, and suggest you try this formula instead:

IF(ISBLANK({Iteration ID})

,{DE:Iteration Assigned Date}

,IF(ISBLANK({DE:Iteration Assigned Date})

,$$NOW

,{DE:Iteration Assigned Date}

)

)

Regards,

Doug

View solution in original post

5 Replies

Avatar

Correct answer by
Community Advisor

Hi Melissa,

From this handy Calculated Data Expressions article, I believe the term "ISBLANK" (vs NOTBLANK) is what you're after, and suggest you try this formula instead:

IF(ISBLANK({Iteration ID})

,{DE:Iteration Assigned Date}

,IF(ISBLANK({DE:Iteration Assigned Date})

,$$NOW

,{DE:Iteration Assigned Date}

)

)

Regards,

Doug

Thanks Doug! I'm not sure this will work though. I'm looking to calculate the date of the first time that there IS data in the IterationID field. So the first time it goes from blank to not blank.

Avatar

Community Advisor

Yes, that's what I'm intending too, Melissa; here's the concept:

  • Initial State: Iteration ID blank, Iteration Assigned Date blank
  • Time passes
  • An edit is made, causing this formula to recalculate, and since the Iteration ID is still blank, the Iteration Assigned Date is left alone (so, still blank)
  • More time passes, with the previous step repeating (so, still blank)
  • Eventually, an Iteration ID does get assigned
  • The formula kicks in again, but this time, since the Iteration ID is NOT blank, proceeds to the second part, confirms that the Iteration Assigned Date is IS blank, and fills it in with $$NOW, capturing this moment when the Iteration ID was first set
  • Time passes
  • Regardless of what is edited thereafter, both branches of the formula leave the Iteration Assigned Date alone (with that initial $$NOW value)

Worth testing, of course, but I believe it should work.

Regards,

Doug

Avatar

Level 5

I see! I'll try it out and let you know. Thank you for the further explanation. Makes so much more sense.

Doug, thanks for the post. I was having a similar issue and your answer helped me solve the problem. Have a good day.