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

  • 0
  • 2
  • Question
  • Updated 1 year ago
  • Answered
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!
Photo of Marc Del Zotto

Marc Del Zotto

  • 90 Points 75 badge 2x thumb

Posted 1 year ago

  • 0
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
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
Photo of Marc Del Zotto

Marc Del Zotto

  • 90 Points 75 badge 2x thumb
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)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
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.