Forum Discussion
Custom Column - Add condition to date calculation
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?
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! 🙂
4 Replies
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! 🙂
Is this what you need?
= Table.AddColumn(Source,"Duration",each (if [closed_date]=null then DateTime.LocalNow() else [closed_date])-[created_date])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.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
