# Calculating a rolling count of days between two dates if one is empty

• 0
• Question
• Updated 2 years ago
I have two main date fields and I want to track the number of days between them.

Imagine "Open" and "Closed" dates, where my "Number of Days"  will equal the difference between "Open" and Today's date, until I input a "Closed" date.

I'm putting together an If statement using Null, but I'm not quite there yet. Formula is below:

If(IsNull(ToDays([Pit Backfilled On]-[Pit Open Date]),ToDays(Today()-[Pit Open Date])))

Thank you!
• 90 Points

Posted 2 years ago

• 0
• 75,144 Points
Try this

var date EndDate = IF(not isnull([Pit Backfilled On]),[Pit Backfilled On], Today());

ToDays(\$EndDate - [Pit Open Date])

I am making use of a formula variable to readability
https://help.quickbase.com/user-assistance/formula_variables.html
• 90 Points
The formula takes without error, which is fantastic. I wasn't getting any values in the target field at first, but it was successful after I had adjusted the destination field to Formula-Numeric in order to give me the number of days instead of Formula-Duration.

Thanks again.

Just out of curiosity - is it typical to set variables and use special characters (\$EndDate) when creating a formula? How would you recognize when to use these functions?
(Edited)
• 75,144 Points
It is good practice to use Formulas variables to make Formulas more stupid simple to read.

Stupid simple is good becabuse it may be your own foggy brain needing to adjust the formula a year from now or your successor.

You define the variable and then can refer to it by prefacing the name with a \$ sign.

I find it helps to organize my thoughts in a formula flow in a step wise linear fashion, so I typically use them a lot.