Help

Formula for ongoing aging with a stop date

Topic Labels: Formulas
Solved
Jump to Solution
969 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Bridget_Ruiz
5 - Automation Enthusiast
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
Justin_Barrett
18 - Pluto
18 - Pluto

This should work:

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

Screen Shot 2020-06-17 at 9.03.01 AM

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)

See Solution in Thread

3 Replies 3
Justin_Barrett
18 - Pluto
18 - Pluto

This should work:

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

Screen Shot 2020-06-17 at 9.03.01 AM

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)

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

It Worked! thankyou so much!