How to Calculate the Difference Between Two Dates

This article describes how to calculate the difference between two dates, be it in years, months, days, minutes, hours, or even seconds. 

Displayr offers a set of useful built-in JavaScript formulas for doing these calculations.

Requirements

You will need two Date/Time variables. Date variables are a special data format that, in Displayr, stores the number of milliseconds since midnight, 1st of January 1970. This number is then converted automatically by the software and presented on-screen as a readable date. Other software packages use different points of reference.  SPSS, for instance, uses the number of seconds since 14th of October 1582, when the Gregorian calendar was first introduced. However, the 1st of January 1970 is standard in many applications.

Method

Date Conversion

In source data files, dates are often stored as Text variables. If the text variable uses a consistent standard format, such as month/day/year or day/month/year, selecting it in the Data Sources tree and changing Data > Attributes > Structure to Date/Time in Properties will automatically create a date-format variable for you.

Note, that dates in mixed formats within the same variable will cause issues and should be cleaned up before you convert the Text variable to a Date/Time.

To deal with converting more exotic date formats, you will find R offers a range of solutions. For example, the flipTime package's AsDate() and AsDateTime() functions, or lubridate's ymd_hms() function which can convert UTC (Coordinated Universal Time) dates that are stored as an ISO-8601 string, e.g. 2018-11-19T16:52:48.000Z.

When time and date are stored separately

Sometimes dates are stored separately from timestamps in your data. For instance, if one Text variable contains the date when something started, and another Text variable the time on that day then we need a single variable in a suitable format that stores the combined date and time before we can convert it to a usable Date/Time variable.

The combined variable must be in a consistent format that Displayr can interpret as a date, so keep in mind that the finished product should contain the date in one of the standard formats, e.g., dd/mm/yyyy (day, month, year separated by a /).  The time stamp’s format should be HH:MM:SS (i.e., hours, minutes, and seconds, separated by a colon).  With the data stored in the correct format, you can combine them into a single variable by using a little bit of code – assuming that the variable names are date and time:

1. In the Data Sources tree, click the + where you want to insert the new variable, and select Custom Code > JavaScript > Date/Time.

2. In the Code panel, enter code like the below (but replace date_from and date_to with the names of the variables in your file):

date + " " + time;

The new variable will now contain the combined text variables and read as 03/06/2021 15:41:32, as an example. 

Time between two date variables

Because dates are stored, under the hood, as the number of milliseconds at two different points, you can perform mathematical functions on them. Displayr has a range of built-in functions for displaying the difference:

• Q.YearDif(date1,date2) - Returns the difference in years as a whole number.
• Q.MonthDif(date1,date2) - Returns the difference in months as a whole number.
• Q.WeekDif(date1,date2) - Returns the difference in weeks as a whole number.
• Q.DayDif(date1,date2) - Returns the difference in days as a whole number.
• Q.HourDif(date1,date2) - Returns the difference in hours as a whole number.
• Q.MinuteDif(date1,date2) - Returns the difference in minutes as a whole number.
• Q.SecondDif(date1,date2) - Returns the difference in seconds as a whole number.

As an example, we have a date_to and a date_from variable in our data set, and we wish to work out the difference between the two in days.

1. In the Data Sources tree, click the + where you want to insert the new variable, and select Custom Code > JavaScript > Numeric.

2. In the Code panel, enter code like the below (but replace date_from and date_to with the names of the variables in your file):

Q.DayDif(date_to,date_from)

You will now have a numeric variable that displays this difference.

Next

How to Use JavaScript in Displayr

How to Easily Convert Strings to Times and Dates in R with flipTime