Calculating time between sets of 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:
- On the Variables and Questions tab, right-click anywhere and select Insert Variable(s) > JavaScript Formula > Text.
- 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; - 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:
- On the Variables and Questions tab, right-click anywhere and select Insert Variable(s) > JavaScript Formula > Numeric.
- 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 - 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 then 60, and so on.
Next
How to Create Filters Using Date Questions