Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Formula help dates

Topic Labels: Formulas
Solved
Jump to Solution
1103 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Jessi_Burns
4 - Data Explorer
4 - Data Explorer

How would I calculate days in foster care from the intake date to today but have it stop counting once the animal’s status is adopted? Ive got the intake date and adopted date but wasnt sure how the formula should be worded.

an example of the info
Screen Shot 2020-11-08 at 12.49.52 PM

1 Solution

Accepted Solutions
Nick_Dennis
7 - App Architect
7 - App Architect

Hi @Jessi_Burns!

Are there any other Status options besides “Available” and “Adopted”?

If not, then I think you’re looking for a formula like this:
IF(Status = "Adopted" ,DATETIME_DIFF({Adopted},{Intake}, 'days'), DATETIME_DIFF(TODAY(),{Intake}, 'days'))

This will count the number of days between Intake and Adopted if the Status is “Adopted”. Otherwise it will count the number of days from Intake to today. If there are other status possibilities then this might need to be tweaked slightly.

Hope that helps.

See Solution in Thread

3 Replies 3
Nick_Dennis
7 - App Architect
7 - App Architect

Hi @Jessi_Burns!

Are there any other Status options besides “Available” and “Adopted”?

If not, then I think you’re looking for a formula like this:
IF(Status = "Adopted" ,DATETIME_DIFF({Adopted},{Intake}, 'days'), DATETIME_DIFF(TODAY(),{Intake}, 'days'))

This will count the number of days between Intake and Adopted if the Status is “Adopted”. Otherwise it will count the number of days from Intake to today. If there are other status possibilities then this might need to be tweaked slightly.

Hope that helps.

Yeah there’s quite a few, around 13. Really only Available, Hold, Pending, and Adopted need the date, the rest either don’t apply or wouldn’t have both dates anyway.

Would there be a way to exclude certain statuses?