This article describes different methods for converting variables into Date/Time variables. Date/Time variables in Displayr are a special type of variable that can be configured to show different time period aggregations (i.e. every 2 weeks, monthly, quarterly etc). These are inherently different than Nominal (categorical) variables that may have a label reflecting a time period (i.e. April 2024, Q4 2023, wave 1 etc) because Date/Time variables are based on a timeline, whereas Nominal variables are just categories. Because Date/Time variables are pegged to a timeline, it means the dates will always be displayed in sequential order, missing time periods will be shown as blank, and you can calculate the difference in time between them. Having a Date/Time variable is also a requirement to be able to perform significance testing against the previous period.
For files with metadata, like .sav files, if the variable has a type of Date/Time, it will have a Date/Time Structure in Displayr. For other date files without meta data (e.g. in Excel files), Displayr will initially treat these dates as categories or text, and they must be converted manually to a Date/Time structure. There are a number of approaches to make them usable as Dates.
- Method 1 - Changing the Variable Type
- Method 2 - Converting with R Code
- Method 3 - Encoding using Year, Month and Day with JavaScript
- Method 4 - Converting from the YYYYMMDDHHMMSS or YYYY.MM format
- Method 5 - Converting with JavaScript functions
- Method 6 - Reading dates from Value Labels
Requirements
- A Data Source with a variable you want to convert.
Method 1 - Changing the Variable Type
The most straightforward way to create a date variable is to change a variable's Structure to Date/Time in the object inspector. Displayr will automatically insert a new Date/Time variable underneath, and you will receive an error if Displayr can't detect the date format correctly to convert (i.e. mm/dd/yyyy and dd/mm/yyyy are both detected automatically).
In situations where Displayr cannot work out the date correctly, you will need to use one of the other methods below.
Method 2 - Converting with R Code
- Select Anything
> Data > Variables > New > Custom Code > R-Numeric
- Type in the code
flipTime::AsDateTime(datevar)
, replacing datevar with the name of the variable that contains the dates. - Change the Structure to Date/Time.
- Optional: The above code works on the most common date formats, but not all. If that code doesn't work for you, then you may need to use Method 6 - Reading dates from Value Labels below.
Method 3 - Encoding using Year, Month and Day with JavaScript
Displayr contains two special functions for computing dates given knowledge of year (y), month (m), etc:
- Q.EncodeDate(yyyy, mm, dd)
- Q.EncodeDateTime(yyyy, mm, dd, hh, mm, ss, ms)
This is useful if you have individual variables for the month, day, and year etc of the date. If you have a wave variable, you can also use these functions to assign each wave a date, for an example see: How to Create a Date Variable from a Categorical Variable Using JavaScript.
Method 4 - Converting from the YYYYMMDDHHMMSS or YYYY.MM format
The following are all examples of the date and time format YYYYMMDDHHMMSS: 20060929205137, 20060929184343, 20060929161711, 20060929160305, 20060929211356, 20060929190754 and 20060929190948, which are all from the 29th of September 2006. Where the variable v1 is a Text Variable in this format, the following Expression will convert it into a numeric JavaScript Variable:
Math.floor(Q.AsNumeric(v1)/1000000) - 20060000;
Alternatively, to turn this date into year and month (YYYY.MM), use:
Math.floor(Q.AsNumeric(LastResp)/100000000)/100;
Method 5 - Converting with JavaScript functions
Where x is the name of your Text Variable:
- Q.AsDate(x) Converts strings into dates. Accepts a variety of formats, but expects dd/mm/yy when in doubt.
- Q.AsDateUS(x) Converts strings into dates. Accepts a variety of formats, but expects mm/dd/yy when in doubt.
- Q.AsDateISO Converts strings into dates. Accepts a variety of formats, but expects yy-mm-dd when in doubt.
Directly computing the date
Displayr represents dates with the number of milliseconds since 1970, and expressions can be written in JavaScript which converts data to this format. Care should be taken to address date anomalies (e.g., leap years) and differences in time zones.
Other numeric date formats exist:
Type of Date | Definition | Notes |
---|---|---|
Q date | The number of milliseconds since 1 January, 1970 UTC. | Matches the dates used in the JavaScript language. |
SPSS date | The number of seconds since midnight, October 14, 1582, which was the first day of the Gregorian Calendar. | Date variables in SPSS .sav files will be automatically be converted to Q dates on loading. To manually convert from an SPSS date to a Q (JavaScript) date: (spss_date - 12219379200) * 1000 |
Excel date | The number of elapsed days since January 1, 1900. For example, Excel represents July 5, 1998, as the number 35981. | Q.ConvertExcelEpochDate() will convert Excel numeric dates to Q dates. |
Method 6 - Reading dates from Value Labels
If you have a date in a more custom format, you will need specify the format in either custom JavaScript or R variable.
- JavaScript: Where the value labels contain the date strings, Q.Label(x) can be used (e.g., Q.AsDate(Q.Label(x))) when creating a JavaScript Variable.
- R: Use the strptime() function and provide the specific format, similar to:
strptime(datevar, format = "%B %d, %Y")
you can find how to specify the format of the date based on the documentation here.
If your dates are ambiguous (doesn't include a month, day, and year) you will need to manually provide a month/day of the date. That is, if your variable labels are "March 2014", you may assume in the code that the day of the month is the 1st with strptime(paste0(datevar, "-1"), format = "%B %Y-1")
to be able to peg that label to a specific day on the timeline. Note that you can still use Aggregate in the Date/Time settings to aggregate the dates to 1 Month to show the monthly labels again, see How to Configure a Date/Time Variable.
Next
How to Configure a Date/Time Variable
How to Calculate the Difference Between Two Dates
How to Create a Date Filter on an Output