This article will walk through how to use the
subtract() functions in a Sugar Logic formula to determine the number of days between two date fields.
For an overview on Sugar Logic and calculated fields, please refer to the article Introduction to Calculated Fields.
In this example, we will calculate the number of days between “Date 1” with a value of 05/09/2016 and “Date 2” with a value of 05/23/2016.
- You must be an administrator.
- You must have two existing date fields to compare in the module. For this article, our date fields are called
- You must create a new field of type integer and mark it as “calculated” before entering the following formula. For more information on creating new fields, please refer to the Studio documentation.
The formula below will calculate the number of days between the two date fields for your custom integer field.
The daysUntil() function translates the two date fields into integers and returns the number of days between today and a specified date. If the date is in the past, then it will return a negative number. The subtract() function takes the difference between the two numbers generated from “Date 1” and “Date 2”. Then the abs() function returns the absolute value of the result.
For our example, the daysUntil() function for “Date 1” returns -26 and the second daysUntil() function for “Date 2” returns -2. We then take the difference between the two values (-26 and -2), which equals -24. Now we take the absolute value of this result and get 24 as the number of days between “Date 1” and “Date 2”.
Once your custom integer field is created with the calculated value formula and added to the module’s detailview, it will reflect the number of days between the two date fields.