Aging & Duration

BHelms · 03/15/22

I have a tracker set up in Smartsheet to log request tickets.

I want to calculate the Age of the ticket based on [Today's Date] - [Initiated Date] unless the ticket [Status] is Completed or Cancelled, and then I'd like to calculate the Duration between the [Resolution Date] - [Initiated Date]. I've tried so many variations of formulas on these community boards and none seem to work for me.

Thank you for the help!

BHelms · 03/18/22

Someone in my company ended up helping me find the right formula to use, so I wanted to post it here in case it's helpful to anyone else:

"You needed an OR statement, rather than just showing the criteria, its Criteria 1 OR Criteria 2

I created a sheet and shared with you, look at the formula in the Days Ticket Open column.

Also to use today date try..... Today()

Also, please ensure that your Initiated Date and Resolution Date fields are set as a Date format."

The formula ended up being:

=IF(OR(Status@row = "Complete", Status@row = "Cancelled"), [Resolution Date]@row - [Initiated Date]@row, TODAY() - [Initiated Date]@row)

Intern98 · 03/16/22

=IF(([Status]="Completed", "Cancelled"),([Resolution Date]@row - [Initiated Date]@row),([Today's Date]@row - [Initiated Date]@row))

apostrophes might have to be re-entered if there is any error

BHelms · 03/16/22

https://community.smartsheet.com/discussion/comment/322811#Comment_322811

Thanks! I tried this and I'm getting #UNPARSEABLE

Here's what I entered (b/c I changed some of the column headers):

=IF((Status@row="Complete","Cancelled"),([Actual Resolution Date]@row-[Initiated Date]@row),(Today@row-[Initiated Date]@row))

I tried adding in spaces around the minus signs and tried adding a space between "Complete", "Cancelled") but no luck.

BHelms · 03/16/22

Is the problem that I'm trying to put 2 functions into 1 column?

Age = No Resolution Date yet, simply adding up the days the ticket has been opened (Initiated Date - Today's Date)

Duration = There is now a Resolution Date, and I want to show the # of days it took to resolve (Initiated Date - Resolution Date)

BHelms · 03/18/22

Someone in my company ended up helping me find the right formula to use, so I wanted to post it here in case it's helpful to anyone else:

"You needed an OR statement, rather than just showing the criteria, its Criteria 1 OR Criteria 2

I created a sheet and shared with you, look at the formula in the Days Ticket Open column.

Also to use today date try..... Today()

Also, please ensure that your Initiated Date and Resolution Date fields are set as a Date format."

The formula ended up being:

=IF(OR(Status@row = "Complete", Status@row = "Cancelled"), [Resolution Date]@row - [Initiated Date]@row, TODAY() - [Initiated Date]@row)

Aging & Duration

Best Answer

Answers

Help Article Resources

Categories