DateOffset
The DateOffset() function returns a date that is relative to a stipulated base date. A number is used to indicate how many date offsets to make prior to, or after, the base date; the frequency describes the type of date offsets to make (days, trading days, week starts), taking into account weekends and public holidays of the securities exchange or country under consideration.
Syntax
DateOffset (Offset(s), Evaluation Date, Frequency, ShowError)
| Offset(s) | The number of date offsets (where the date offset type is defined by the frequency parameter) before or after the evaluation date. A positive integer is used for the number of dates after and a negative integer for the number of dates before. Multiple offsets can be provided as a comma delimited list, which returns an array of dates accordingly. |
| Evaluation Date | The base date to which to apply the offsets. |
| Frequency |
The sequence of value dates to return. Possible values: “D” (days), “WS” (week starts), “WE” (week ends), “MS” (month starts), “ME” (month ends), “QS” (quarter starts), “QE” (quarter ends), “YS” (year starts), “YE” (year ends),
Any value prefixed by a ‘T’ means trading days only. For example, ‘TWE’ means week ends that are also trading days. |
| ShowError | If ShowError is set to TRUE, an error message will be displayed if the function fails. |
Note:
Input parameters can consist of Quintessence functions. This includes functions that return ranges in cases where a parameter value can be a range.
Examples
Example 1. Return trading days relative to a specified base date
Example 2. Return dates relative to today
Example 3. Return the last month end that was also a trading day
Example 4. Return the first trading day of 2016
Note:
How your system administrator configured Quintessence determines which function parameters are required. In most implementations, administrators configure functions so that certain parameters default to specific values when left blank.
Example 1. Return trading days relative to a specified base date
The DateOffset() function is used to return trading days that are 10 trading days before, 1 trading day before , 5 trading days after and 15 trading days after a stipulated base date.
| Function |
| =DateOffset(“-10,-1,5,15″,”21 May 2015″,”td”) |
| Output |
| 2015/05/07 |
| 2015/05/20 |
| 2015/05/28 |
| 2015/06/11 |
Example 2. Return dates relative to today
The DateOffset() function is used to return days that are 15 days before and 15 days after today (in this example, today is 28 May 2015).
Note:
Today() is a ‘volatile’ function, meaning it automatically recalculates itself. This can make larger spreadsheets very slow. In these cases, rather use DateOffset(). DateOffset() returns today’s date, but does not recalculate itself every time a spreadsheet is opened.
| Function |
| =DateOffset(“-15,15″,DateOffset(),”d”) |
| Output |
| 2015/05/13 |
| 2015/06/12 |
Example 3. Return the last month end that was also a trading day
The DateOffset() function is used to return the last month end that was also a trading day, relative to today (in this example, today is 28 May 2015)
| Function |
| =DateOffset(“-1″,DateOffset(0),”tme”) |
| Output |
| 2015/04/30 |
Example 4. Return the first trading day of 2016
The DateOffset() function is used to return the first trading day of 2016.
| Function |
| =DateOffset(“1″,”2016-01-01″,”td”) |
| Output |
| 2016/01/04 |