EXPAND_DATE_RANGE
Syntax
EXPAND_DATE_RANGE(<range_start:date>;<range_end:date>[;<interval:integer> or time interval])
Description
Expands a date range into multiple rows, one for each interval, one day by default. The generated series of dates starts with the range_start parameter and end with the range_end parameter (if applicable) and the series is sorted based on the relationship between these parameters:
If range_start is before range_end, the series runs forward in time from range_start.
If range_start is after range_end, the series runs backwards in time from range_start.
The interval parameter defaults to one day and can be changed in the field using an integer set in milliseconds (ms) or a time interval (10d, 1h, 30s, etc.)
Results must have a record count lower or equal to 100,000 rows.
Example
Given the following data:
| Date_1 | Date_2 |
|---|---|
| Jan 1, 2012 | Jan 15, 2012 |
Then use the function EXPAND_DATE_RANGE(#Workbook!Date_1;#Workbook!Date_2)
The result of the function is:
| EXPAND_DATE_RANGE returns |
|---|
| Jan, 1, 2012 12:00:00 AM |
| Jan, 2, 2012 12:00:00 AM |
| Jan, 3, 2012 12:00:00 AM |
| Jan, 4, 2012 12:00:00 AM |
| Jan, 5, 2012 12:00:00 AM |
| Jan, 6, 2012 12:00:00 AM |
| Jan, 7, 2012 12:00:00 AM |
| Jan, 8, 2012 12:00:00 AM |
| Jan, 9, 2012 12:00:00 AM |
| Jan, 10, 2012 12:00:00 AM |
| Jan, 11, 2012 12:00:00 AM |
| Jan, 12, 2012 12:00:00 AM |
| Jan, 13, 2012 12:00:00 AM |
| Jan, 14, 2012 12:00:00 AM |
| Jan, 15, 2012 12:00:00 AM |
Datameer X always uses the following format:
Three character month abbreviation (space) two digit day (comma)(space) four digit year (space) two digit hour (colon) two digit minute (colon) two digit second (space) two character meridiem notation.
(e.g., 12.01.2012 gets transferred to Jan 12, 2012 12:00:00 AM)
When selecting a pattern, you select the way the data gets parsed (your input format, this can be connected to your systems representation of dates and times).