Calculated Field - Count of Days Between Two Dates

Overview

This article will walk through how to use the daysUntil() and 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. 

Use Case

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. 

Prerequisites

• 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 date1_c and date2_c.  
• 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.

Formula

The formula below will calculate the number of days between the two date fields for your custom integer field.

abs(subtract(daysUntil($date1_c),daysUntil($date2_c)))

Formula Breakdown

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".

Application

Once your custom integer field is created with the calculated value formula and added to the module's record view, it will reflect the number of days between the two date fields.