DateOffset
The DateOffset() function returns a date (or dates) relative to a specified base date.
- The Offset value determines how many date units to move forward or backward from the base date.
- The Frequency determines the unit of measurement (days, trading days, week starts, etc.) and can account for weekends and public holidays according to the relevant securities exchange or country.
Syntax:
Qx.DateOffset(Offset(s), Evaluation Date, Frequency, ShowError)- In Excel with Quintessence addin versions prior to 25.1, use:
DateOffset() - In the Quintessence Editor (Studio) using Quintessence language, use:
DateOffset() - In the Quintessence Editor (Studio) using Python language, use:
Qx.DateOffset()
ⓘ See parameter descriptions
| Offset(s) (optional) | The number of date offsets, as defined by the Frequency parameter, before or after the evaluation date. • Use a positive integer for dates after the Evaluation Date. • Use a negative integer for dates before the Evaluation Date. • Multiple offsets can be supplied as a comma-separated list, returning an array of dates. |
| Evaluation Date (optional) | The base date from which the offset is calculated. |
| Frequency (optional) | Specifies the unit and period type. 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 with T means trading days only. For example, “TWE” means week ends that are also trading days. |
| ShowError (optional) | If TRUE, an error message is displayed when the function fails. |
Remarks:
- Unless changed by an administrator, the default parameters are:
- Offset:
0 - Evaluation Date: today
- Frequency:
TD
- Offset:
Today()is a volatile function that recalculates automatically, which can slow large spreadsheets. In these cases, useDateOffset()instead. It returns today’s date but does not recalculate automatically when reopening the spreadsheet.
Examples:
The examples provided below are based on fictitious data for illustrative purposes only.
> Example 1: Trading days relative to a specified base date
Return 10 trading days before, 1 trading day before, 5 trading days after, and 15 trading days after 21 May 2015:
=Qx.DateOffset("-10,-1,5,15", "21 May 2015", "td")| 2015/05/07 |
| 2015/05/20 |
| 2015/05/28 |
| 2015/06/11 |
> Example 2: Dates relative to today
Return 15 days before and 15 days after today (28 May 2015 in this example):
=Qx.DateOffset("-15,15", Qx.DateOffset(), "d")| 2015/05/13 |
| 2015/06/12 |
> Example 3: Last month-end that was a trading day
Return the last month-end that was also a trading day, relative to today (28 May 2015):
=Qx.DateOffset(-1, Qx.DateOffset(), "tme")| 2015/04/30 |
> Example 4: First trading day of 2016
Return the first trading day of 2016:
=Qx.DateOffset(1, "2015-12-31", "td")| 2016/01/04 |