Skip to main content
Question

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

  • December 5, 2017
  • 0 replies
  • 121 views
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!
    This topic has been closed for replies.

    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
    • MarcZotto
    • Author
    • Registered
    • December 6, 2017
    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?
    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.
    Terms & ConditionsAccessibility statement