Forum Discussion

MarcZotto's avatar
MarcZotto
Qrew Member
8 years ago

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

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!
Formulas and functions
  • MarcZotto's avatar
    MarcZotto
    Qrew Member
    8 years ago
    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?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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.