Formula for ongoing aging with a stop date

[Bridget_Ruiz]

Hello, I am trying to create a formula to track of the aging of a record. I am using date fields for this formula, the Booked date and the Funded date.
I was able to come up with a formula that calculated the age, but it only calculated the age once the funded date was updated. I need to be able to keep track of the ongoing aging of each record from the date it was booked all the way to the date the record is funded. I also need the formula to stop keeping track of the aging once its been funded.
for example, if a record was booked on June 1st and today is June 17th,and the record has not been funded I need to see that the record is 17 days old. However if the record was booked on June 1st and funded on June 16th, then even though today is the 17th, the formula should still show that the record is 16 days old.

[Justin_Barrett · Accepted solution]

This should work:

DATETIME_DIFF(IF(Funded, Funded, NOW()), Booked, "days")

Note that the age is a day off because of how DATETIME_DIFF() calculates differences by subtracting the end from the start, effectively omitting the end date from the count. If you want that end date counted, add a “+ 1” to the end of the above formula.

DATETIME_DIFF(IF(Funded, Funded, NOW()), Booked, "days") + 1

Finally, if you want clean fields (i.e. no “NaN” in records where there’s no {Booked} date set), use one of these:

IF(Booked, DATETIME_DIFF(IF(Funded, Funded, NOW()), Booked, "days"))

// or //

IF(Booked, DATETIME_DIFF(IF(Funded, Funded, NOW()), Booked, "days") + 1)

[Bridget_Ruiz]

thankyou!!! i will try this right now and let you know if it worked

[Bridget_Ruiz]

It Worked! thankyou so much!