Calculate the date difference between two text fields using Arcade in ArcGIS Online

Summary

In ArcGIS Online, the DateDiff() function may not work properly when calculating the date difference between two fields, especially if the date fields are in different formats and are stored as text or string. The date difference between two text fields can be calculated using an Arcade expression. This method transforms qualitative temporal information into quantitative metrics, enabling more informed and strategic approaches to managing and interpreting time-sensitive data.

Note:
The date difference between two text fields can only be calculated if the date format used in the text fields is in dd-mmmm-yyyy.

This article provides the workflow to calculate the date difference between two text fields in an attribute table using an Arcade expression in ArcGIS Online.

Procedure

1. In ArcGIS Online, click the desired hosted feature layer. On the item details page, click the Data tab.

Note:
Skip to Step 3 if the script is run on an existing field.

2. In the Table view, create a new field. Refer to ArcGIS Online: Add a field for instructions. Ensure String or Double is selected from the Type drop-down list. In this example, a field named Date_Difference is created.
3. Click the field header and select Calculate. In this example, Date_Difference is selected.
4. In the Calculate Field dialog box, click Arcade.
5. In the Arcade Calculator dialog box, specify the Expression field as follows to calculate the date difference between the two text fields in days:

var months = {
    "January": "01",    
    "February": "02",
    "March": "03",
    "April": "04",
    "May": "05",
    "June": "06", 
    "July": "07",
    "August": "08",
    "September": "09",
    "October": "10",
    "November": "11",
    "December": "12"
};

1. This variable is used to define the breakdown of the months.

var <variable1> = $feature.<field_name1>;
var <variable2> = $feature.<field_name2>;

2. Replace <variable1> and <variable2> with the variables to represent the field names. In this example, startDate and endDate are used.
3. Replace <field_name1> and <field_name2> with the names of the fields containing the field values. In this example, StartDate and EndDate are used.

var <variable3> = Split(<variable1>, " ");
var <variable4> = Split(<variable2>, " ");

4. Replace <variable3> and <variable4> with the variables to represent the splitting of <variable1> and <variable2> into an array of parts using the Split function. In this example, dateParts and dateParts2 are used.

var dayNum = dateParts[0];
var monthNum = months[dateParts[1]];
var yearNum = dateParts[2];

var dayNum2 = dateParts2[0];
var monthNum2 = months[dateParts2[1]];
var yearNum2 = dateParts2[2];

5. These variables store the day, month, and year components of the date after splitting.

var <variable5>  = yearNum + "-" + monthNum + "-" + dayNum;
var <variable6> = yearNum2 + "-" + monthNum2 + "-" + dayNum2;

var dateDifference = DateDiff(<variable6>, <variable5>, "days");
return dateDifference;

6. Replace <variable5> and <variable6> with the desired variables. In this example, updatedStartDate and updatedStartDate are used.

Below is the full working code of the Arcade expression.

var months = {
    
    "January": "01",
    "February": "02", 
    "March": "03",
    "April": "04",
    "May": "05",
    "June": "06",
    "July": "07",
    "August": "08",
    "September": "09",
    "October": "10", 
    "November": "11",
    "December": "12"
};

var startDate = $feature.StartDate;
var endDate = $feature.EndDate;

var dateParts = Split(startDate, " ");
var dateParts2 = Split(endDate, " ");

var dayNum = dateParts[0];
var monthNum = months[dateParts[1]];
var yearNum = dateParts[2];

var dayNum2 = dateParts2[0];
var monthNum2 = months[dateParts2[1]];
var yearNum2 = dateParts2[2];

var updatedStartDate = yearNum + "-" + monthNum + "-" + dayNum;
var updatedEndDate = yearNum2 + "-" + monthNum2 + "-" + dayNum2;

var dateDifference = DateDiff(updatedEndDate, updatedStartDate, "days");
return dateDifference;

6. Click Test to verify the expression, and click OK to apply the query.

Note:
To calculate the difference between the two date fields in other units such as hours, minutes, or seconds, use the same expression and replace 'days' with the desired unit of measurement.

The image below displays the date difference in days between the two text fields calculated and populated in the new field.