Custom Column - Add condition to date calculation

[Anonymous]

Hi All,

I have a custom column which gives the duration of problem ticket records from when they are open to when they are closed ([closed_date]-[created_date]).

However, as some of the tickets are still open, I would like to add a condition that returns the duration between [created_date] and [today] if [closed_date] is null.

I am thinking it may need to be an IF statement of some kind but dont know how to achieve this.  Is anyone able to help please?

[v-yingjl]

Hi @Anonymous ,

Glad to hear the issue is solved. You can accept your reply as solution, that way, other community members could easily find the answer when they get same issues.

Best Regards,
Community Support Team _ Yingjie Li

[Anonymous · Accepted solution]

Hi @shaowu459  and @PhilipTreacy,

Apologies for the delayed response.  I wanted to thank you both; though the formulae you both provided did not quite work for me, I was able to work it out using your examples by snipping out this part:

= if [closed_date]=null then DateTime.LocalNow()-[created_date] else [closed_date]-[created_date]

Thanks to each of you for pointing me in the right direction! 🙂

[PhilipTreacy]

Hi @Anonymous 

Try this, it assumes you are dealing with dates and not datetimes.

= Table.AddColumn(Source,"Duration",each if [closed_date]=null then DateTime.Date(DateTime.LocalNow()) - [created_date]  else [closed_date]-[created_date])

Phil 

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[shaowu459]

Is this what you need?

= Table.AddColumn(Source,"Duration",each (if [closed_date]=null then DateTime.LocalNow() else [closed_date])-[created_date])