Help

Re: aging formula, difference of days between date opened and when records status = complete

Solved
Jump to Solution
894 0
cancel
Showing results for 
Search instead for 
Did you mean: 
athomas
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello, 

I need to create an aging formula with an If statement to track when a record is open with a count of days until it gets closed and ultimately stops counting.  I then want to be left with the difference of days between the date opened and the date completed,  in the days-old column.   Currently, the Days old column counts days open from the date the record was created.  DATETIME_DIFF(TODAY(), DATETIME_PARSE({Opened date}, 'MM-DD-YY'), 'days') and just keeps counting.  Any help is appreciated! 

 

 

1 Solution

Accepted Solutions
TheTimeSavingCo
17 - Neptune
17 - Neptune

If I were you, I would:
1. Create a new Date field called "Closed Date"
2. Create a new Formula field called "Today's Date" and give it the formula `TODAY()`
3. Create an automation that triggers when a record is "Closed" (Not sure how you're doing this) that update the "Closed Date" field with the value from "Today's Date"
4. Modify the formula to find the difference between "Opened Date" and "Closed Date"

Should get you what you're looking for I reckon

 

See Solution in Thread

2 Replies 2
TheTimeSavingCo
17 - Neptune
17 - Neptune

If I were you, I would:
1. Create a new Date field called "Closed Date"
2. Create a new Formula field called "Today's Date" and give it the formula `TODAY()`
3. Create an automation that triggers when a record is "Closed" (Not sure how you're doing this) that update the "Closed Date" field with the value from "Today's Date"
4. Modify the formula to find the difference between "Opened Date" and "Closed Date"

Should get you what you're looking for I reckon

 

athomas
5 - Automation Enthusiast
5 - Automation Enthusiast

@TheTimeSavingCo   Thank you again! I think I was trying to make it too hard. LOL.   So I ended up with three date columns.  (Opened Date, Date Closed, Days old) I changed the formula in the Days old column to be 

DATETIME_DIFF({Opened date}, {Date Closed}, "days" ) . I then built an automation that was triggered when the status field changed to Completed, By Design, and Can't Reproduce. When the automation is triggered it will update the Date Closed field and then the Days old field will display the days that the ticket was opened. So simple, duh on me!! I'm very visual so I included a picture. Thank you for helping me get out of my blindspot! Screenshot 2023-01-10 135824.png