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_1Date_2
Jan 1, 2012Jan 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 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).