Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

# Formula for ongoing aging with a stop date

Topic Labels: Formulas
Solved
542 3
cancel
Showing results for
Did you mean:  5 - Automation Enthusiast

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.

1 Solution

Accepted Solutions  18 - Pluto

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)
``````
3 Replies 3  18 - Pluto

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)
``````  5 - Automation Enthusiast

thankyou!!! i will try this right now and let you know if it worked  5 - Automation Enthusiast

It Worked! thankyou so much! 