Subtract dates to get number of days between them

  • 1
  • Question
  • Updated 3 years ago
  • Answered
What would be the best way to show how many days are between two dates? Ui Only field with a formula? Custom renderer with JS?

Right now I would like to show in a table the number of days between the CreatedDate and Today, however this could be used to compare two dates on the same object and get the duration of days between them.
Photo of Craig Rosenbaum

Craig Rosenbaum

  • 4,776 Points 4k badge 2x thumb

Posted 3 years ago

  • 1
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,106 Points 10k badge 2x thumb
Hi Craig -

The easiest approach would be a UI-Only field.  Unfortunately, there isn't a datediff formula function but you can mimick that behavior using a combination of formula functions:

ABS(NOW() - {{CreatedDate}})/(1000*24*60*60)

* 1000 accounts for the fact that NOW() - {{CreatedDate}} will return the number of milliseconds between the two
* 24 accounts for number of hours in a day
* 60 accounts for number of minutes in an hour
* 60 accounts for number of seconds in a minute

You can also layer in CEIL/FLOOR/ROUND depending on if you want decimals or not.

Note that I do not believe this solution will handle daylight savings properly so you'll want to test various scenarios to ensure it meets your requirements.
Photo of Craig Rosenbaum

Craig Rosenbaum

  • 4,776 Points 4k badge 2x thumb
Thanks Barry. That's pretty much what I figured. I didn't know if there was a quick function way of doing it that I had overlooked.
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,106 Points 10k badge 2x thumb
Unfortunately no quick function :(  

Shameless plug warning: That said, it would be really easy to write one if the ability to register custom formulas existed.  If you think that is something that would be helpful, feel free to vote up the issue at https://community.skuid.com/skuid/topics/add-support-for-registering-custom-formulas.