Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ticke...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
PhilipTreacy
Super User
5 years ago

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.