Many clients have expressed interest in finding the number of days in between two date fields in their data. This can easily be done using SQL and the JULIANDAY function. The JULIANDAY function essentially puts the date in a format that will allow me to subtract the number of days between two dates.
First make sure your date fields are in database format by selecting the checkbox ‘Table Date Format’ and then typing in the columns you’d like to reformat. Separate multiple columns by a comma.
Next, enter in the following code:
JULIANDAY([DateField2]) - JULIANDAY([DateField1])
Press the connect button, and you’ll see that now you have a calculated difference between the two date fields. You could now use the aggregate button to find the average of your Julianday column.
Questions? Email us at firstname.lastname@example.org or chat in on the site.