This article describes different methods for creating date variables in Q.
Variables that are stored with the Type of Date in the data file will be automatically recognized as Dates when the data is imported. For date information stored in different ways (e.g. in Excel files), there are a number of approaches to make them usable as Dates in Q.
1. Changing the Variable Type
The most straightforward way to create a Date Variable is to change a variable's Variable Type to Date in the Variables and Questions tab. If it is a Text Variable Q will automatically insert a new Date Variable underneath.
2. Creating an R Variable
- Create > Variables and Questions > Variable(s) > R Variable
- Type in the code flipTime::AsDateTime(datevar), replacing datevar with the name of the variable that contains the dates.
- Press the play button (the blue triangle at the top), enter a Question name at the bottom, and press Add R Variable.
- Change the Variable Type to Date/Time.
3. Encoding using Year, Month and Day
Q 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)
4. Converting from the YYYYMMDDHHMMSS or YYYY.MM format
Math.floor(Q.AsNumeric(v1)/1000000) - 20060000;
Alternatively, to turn this date into year and month (YYYY.MM), use:
5. Reading dates from Text Variables
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
Other numeric date formats exist:
|Type of Date||Definition||Notes|
|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 dates to Q dates.|
6. Reading dates from a Value Labels