It can sometimes be useful to calculate the amount of time, be it in minutes, hours, or even seconds, between two points in time.  In survey research, a common situation is to check for speedsters in your survey data by calculating the duration between the beginning and the end of the survey for each respondent. But, so long as you have a start point and an end point, it’s possible to calculate the time in between dates.

Due to the nature of date variables, it can easily get confusing.  Here, I go through a few examples of different situations you may come up against, and what to do.

Making sure your date is a Date

The first thing to do, which applies to all of the following section, is to make sure that your date data is stored in a Date format. In source data files, dates are often stored as Text variables. This necessitates converting the variable to date format.  In Q, you do this by:

  1. On the Variables and Questions tab, select the Text format date variable.
  2. Change the Variable Type to Date.

This automatically creates a date-format variable that you can use in further analysis.  Note, that it depends on consistent formatting of the dates. Dates in mixed formats, e.g. month/day/year and day/month/year in the same variable will cause issues and should be cleaned up before you convert the Text variable to a Date.

Date variables are a special data format that, in Q, 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.

Calculating time between sets variables that store time and date 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 an usable Date variable. The combined variable must be in a consistent format that Q 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 :).  With the data stored in the correct formats, 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. On the Variables and Questions tab, right-click anywhere and select Insert Variable(s) > JavaScript Formula > Text.

2. In the Expression field, enter code like the below (remembering to replace date_from and date_to with the names of the variables in your file):

date + ” ” + time;

3. Click OK.

The new variable now contains the combined text variables.  Looking at it on the Data tab should show data that reads 30/04/2019 16:41:32, as an example (also the exact time I’m writing this!).  You can then convert this new variable into a Date variable using the same steps as above before using it in any calculations.

Calculating 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.  Thus, to calculate the difference between two date variables, you subtract the first date from the second.  Then, divide by the relevant numbers to get the unit of time you would like to analyze.  The steps to take in Q follow, using the variable names date_from and date_to:

1. On the Variables and Questions tab, right-click anywhere and select Insert Variable(s) > JavaScript Formula > Numeric.

2. In the Expression field, enter code like the below. Remember to replace date_from and date_to with the names of the variables in your file:

(date_to – date_from) / 1000

3. Click OK.

This gives you a new Number variable that stores the number of seconds.  Remember that the original data contains milliseconds, so dividing by 1000 will return seconds.  If you want minutes, you would divide by 1000 and then 60, and so on.