Date functions

functions for calendar and business date analytics and calculations. More...

Functions
int __stdcall	SFDB_EDATE (LONG argDate, LPCTSTR szPeriod, PLONG retVal)
	advance the date by the given period and return that date
int __stdcall	SFDB_NWKDAY (WORD weekdy, short order, WORD mnth, WORD year, PLONG retVal)
	return the date of the n-th weekday in a given month and year.
int __stdcall	SFDB_WKDYOrder (LONG argDate, short *retVal)
	return the order of the weekday of a given date in that month.
int __stdcall	SFDB_WEEKDAY (LONG argDate, WORD argReturnType, LPWORD retVal)
	return the numeric representation of the weekday for a given convention
int __stdcall	SFDB_DTADJUST (LONG argDate, WORD argNextPrev, LPCTSTR holidays, LPLONG zDates, size_t nSize, WORD nWkndNo, LPLONG retVal)
	calculate the date of the next (or previous) period using a day-counting convention to handle dates that fall on weekend and holidays.
int __stdcall	SFDB_ISWRKDY (LONG argDate, LPCTSTR holidays, LPLONG zDates, size_t nSize, WORD nWkndNo)
	examine whetehr a give data is a workday or not.
int __stdcall	SFDB_NETWRKDYS (LONG argStartDate, LONG argEndDate, LPCTSTR holidays, LPLONG zDates, size_t nSize, WORD nWkndNo, int *retVal)
	Return the number of whole working days between two dates (inclusive). Working days exclude weekends and any date identified as a holiday.
int __stdcall	SFDB_WORKDAY (LONG argDate, short nDays, LPCTSTR holidays, LPLONG zDates, size_t nSize, WORD nWkndNo, LPLONG retVal)

Detailed Description

The Date and Holiday Calendar functionality was introduced in version 1.56 (Zebra) to help you identify holiday, trading day, and weekday bias effects that often arise in time series analysis.

Why do we care about holidays?

A core assumption in econometric methods is that time series observations are equally spaced and present. This arises either because observations are made deliberately at even intervals (continuous process) or because the process only generates outputs at such intervals in time (discrete process).

Furthermore, the time unit of a sampling period (i.e., the step) between two consecutive observations can be either absolute (e.g., Daily, weekly, monthly, or annual) or based on a holiday calendar (i.e., adjusted for weekends and holidays).

For example, a daily financial time series of IBM stock closing prices is based on the NYSE holidays calendar, so each observation is taken on an NYSE trading day (open/close). For weekly or monthly time series, the number of trading days varies from one observation to another, and we may have to adjust to their effect. A good overage for holidays and trading days effect adjustment can be found in X-12-ARIMA literature (see reference section).

Remarks

1. NumXL supports dates, holidays, and Western and non-Western weekend functions.
2. NumXL defines ~100 holidays for 10 countries (USA, CAN, CHE, GBR, AUS, NZL, JPN, SAU, and ISR).
3. NumXL defines public (government) and bank holiday calendars for 10 countries, and it plans to support more in later releases.

Related Links

• Comparison of X-12-ARIMA Trading Day and Holiday Regressors With Country Specific Regressors

• Wikipedia - Workweek and weekend

• Wikipedia - Holidays

• Wikipedia - List of holidays by country

• Wikipedia - Long weekend

Function Documentation

SFDB_DTADJUST()

int __stdcall SFDB_DTADJUST	(	LONG	argDate,
		WORD	argNextPrev,
		LPCTSTR	holidays,
		LPLONG	zDates,
		size_t	nSize,
		WORD	nWkndNo,
		LPLONG	retVal )

Examines whether the given date falls on a weekend or a holiday (i.e. non-working day), and returns the nearest working business day using a Business Day Convention (BDC).

Parameters

[in]	argDate	(Required) The serial date number that represents the given date
[in]	argNextPrev	(Required) the Business Day Convention (BDC): 1=Following, 2=Following Modified, 3=Preceding, 4=Preceding Modified, 5=Unadjusted (default).
[in]	holidays	(Optional) A (:_:) separated list of holiday names, calendars, countries or currency.
[in]	zDates	(Optional) An array of holidays dates; each expressed as a serial number (i.e. number of days since 1.1.1970)
[in]	nSize	(Required) The number of dates in zDates.
[in]	nWkndNo	(Optional) is the weekend number (1-7,11-17). If 0, the western weekend (i.e. 1, "Saturday, Sunday") is used.
[out]	retVal	(Required) a pointer to a varaible to hold the calculated date of the nearest business day.

Returns

status code of the function call: zero = success, positive = success with a warning, and negative = error occurred.

Return values

NDK_SUCCESS	success
NDK_INVALID_ARG	failed (see remarks)
NDK_INVALID_VALUE	failed (see remarks)
NDK_FAILED	failed

Remarks

1. If the calendar module has not been initialized, the function will fail and return NDK_FAILED as the return code.
2. The function is available in the lite (free) version without any restrictions.
3. If the value of retVal is NULL, the function will fail and return NDK_INVALID_ARG as error code.
4. If the value of the argDate argument does not correspond to a valid date serial number, the function will fails and return NDK_INVALID_VALUE as error code.
5. If the value of argNextPrev (BDC) is not between one(1) and five(5), the function will fail and return NDK_INVALID_VALUE as error code.

Requirements

Requirement	Value
Target Platform	Windows
Header	SFDBM.h (include Windows.h)
Library	SFDBM.lib
DLL	SFDBM.dll SFLUC.dll SFLOG.dll

Since

v1.56

See also

• SFDB_EDATE
• SFDB_NWKDAY
• SFDB_WKDYOrder
• SFDB_WEEKDAY
• SFDB_ISWRKDY
• SFDB_NETWRKDYS
• SFDB_WORKDAY

SFDB_EDATE()

int __stdcall SFDB_EDATE	(	LONG	argDate,
		LPCTSTR	szPeriod,
		PLONG	retVal )

computed the date after a given a period (e.g. 1w, 1m, 3w, etc.)

Parameters

[in]	argDate	(Required) the base filename of the database file
[in]	szPeriod	(Optional) the description of a period range (e.g. "5-days", "1 month", "1m", "3 w", "-1 year").
[out]	retVal	(Required) a pointer to a varaible to hold the calculated date that falls after the given period.

Returns

status code of the function call: zero = success, positive = success with a warning, and negative = error occurred.

Return values

NDK_SUCCESS	success
NDK_INVALID_ARG	failed (see remarks)
NDK_INVALID_VALUE	failed (see remarks)
NDK_FAILED	failed

Remarks

1. The operation of this function does not rely on the initialization of the calendar module.
2. The function is available in the lite (free) version without any restrictions.
3. If the value of retVal or szPeriod is NULL, the function will fail and return NDK_INVALID_ARG as error code.
4. If the value of the argDate argument does not correspond to a valid date serial number, the function will fails and return NDK_INVALID_VALUE as error code.
5. If the text in the szPeriod argument is not a valid period (e.g., syntax error), the function will fail and return NDK_FAILED as error code.

Requirements

Requirement	Value
Target Platform	Windows
Header	SFDBM.h (include Windows.h)
Library	SFDBM.lib
DLL	SFDBM.dll SFLUC.dll SFLOG.dll

Since

v1.56

See also

• SFDB_NWKDAY
• SFDB_WKDYOrder
• SFDB_WEEKDAY
• SFDB_DTADJUST
• SFDB_ISWRKDY
• SFDB_NETWRKDYS
• SFDB_WORKDAY

SFDB_ISWRKDY()

int __stdcall SFDB_ISWRKDY	(	LONG	argDate,
		LPCTSTR	holidays,
		LPLONG	zDates,
		size_t	nSize,
		WORD	nWkndNo )

Examines a given date for weekends and holidays (non-working days), and returns FALSE if it falls on a non-working day; otherwise it returns TRUE

Parameters

[in]	argDate	(Required) The serial date number that represents the given date
[in]	holidays	(Optional) A (:_:) separated list of holiday names, calendars, countries or currency.
[in]	zDates	(Optional) An array of holidays dates; each expressed as a serial number (i.e. number of days since 1.1.1970)
[in]	nSize	(Required) The number of dates in zDates.
[in]	nWkndNo	(Optional) is the weekend number (1-7,11-17). If 0, the western weekend (i.e. 1, "Saturday, Sunday") is used.

Returns

status code of the function call: zero = success, positive = success with a warning, and negative = error occurred.

Return values

NDK_TRUE	success (TRUE)
NDK_FALSE	success (FALSE)
NDK_INVALID_ARG	failed (see remarks)
NDK_INVALID_VALUE	failed (see remarks)
NDK_FAILED	failed

Remarks

1. If the calendar module has not been initialized, the function will fail and return NDK_FAILED as the return code.
2. The function is available in the lite (free) version without any restrictions.
3. If the value of the argDate argument does not correspond to a valid date serial number, the function will fails and return NDK_INVALID_VALUE as error code.

Requirements

Requirement	Value
Target Platform	Windows
Header	SFDBM.h (include Windows.h)
Library	SFDBM.lib
DLL	SFDBM.dll SFLUC.dll SFLOG.dll

Since

v1.56

See also

• SFDB_EDATE
• SFDB_NWKDAY
• SFDB_WKDYOrder
• SFDB_WEEKDAY
• SFDB_DTADJUST
• SFDB_NETWRKDYS
• SFDB_WORKDAY

SFDB_NETWRKDYS()

int __stdcall SFDB_NETWRKDYS	(	LONG	argStartDate,
		LONG	argEndDate,
		LPCTSTR	holidays,
		LPLONG	zDates,
		size_t	nSize,
		WORD	nWkndNo,
		int *	retVal )

Returns the number of whole working days between two dates (inclusive). Working days exclude weekends and any date identified as a holiday.

Parameters

[in]	argStartDate	(Required) a serial date number that represents start date.
[in]	argEndDate	(Required) a serial date number that represents finish date.
[in]	holidays	(Optional) A (:_:) separated list of holiday names, calendars, countries or currency.
[in]	zDates	(Optional) An array of holidays dates; each expressed as a serial number (i.e. number of days since 1.1.1970)
[in]	nSize	(Required) The number of dates in zDates.
[in]	nWkndNo	(Optional) is the weekend number (1-7,11-17). If 0, the western weekend (i.e. 1, "Saturday, Sunday") is used.
[out]	retVal	(Required) a pointer to a varaible to hold the calculated number of work days in the given period.

Returns

status code of the function call: zero = success, positive = success with a warning, and negative = error occurred.

Return values

NDK_SUCCESS	success
NDK_INVALID_ARG	failed (see remarks)
NDK_INVALID_VALUE	failed (see remarks)
NDK_FAILED	failed

Remarks

1. If the calendar module has not been initialized, the function will fail and return NDK_FAILED as the return code.
2. The function is available in the lite (free) version without any restrictions.
3. If the value of retVal is NULL, the function will fail and return NDK_INVALID_ARG as error code.
4. If the value of the argStartDate argument does not correspond to a valid date serial number, the function will fails and return NDK_INVALID_VALUE as error code.
5. If the value of the argEndDate argument does not correspond to a valid date serial number, the function will fails and return NDK_INVALID_VALUE as error code.

Requirements

Requirement	Value
Target Platform	Windows
Header	SFDBM.h (include Windows.h)
Library	SFDBM.lib
DLL	SFDBM.dll SFLUC.dll SFLOG.dll

Since

v1.56

See also

• SFDB_EDATE
• SFDB_NWKDAY
• SFDB_WKDYOrder
• SFDB_WEEKDAY
• SFDB_DTADJUST
• SFDB_ISWRKDY
• SFDB_WORKDAY

<

SFDB_NWKDAY()

int __stdcall SFDB_NWKDAY	(	WORD	weekdy,
		short	order,
		WORD	mnth,
		WORD	year,
		PLONG	retVal )

Returns the serial number of the n-th weekday in a month.

Parameters

[in]	weekdy	(Required) A weekday (i.e. 1=Sunday, 2=Monday, 3=Tuesday, ..., 7=Saturday)
[in]	order	(Required) the n-th occurence in the month (i.e. 1=First, 2=Second, 3=Third, 4=Fourth and -1=Last).
[in]	mnth	(Required) the month in the year expressed as a number (i.e. 1=Jan, 2=Feb, ..., 12=Dec).
[in]	year	(Required) the target year (2 or 4 digits) (e.g. 99, 2007, 2008, 09, etc.).
[out]	retVal	(Required) a pointer to a varaible to hold the calculated serial number of the n-th weekday in a month/year.

Returns

status code of the function call: zero = success, positive = success with a warning, and negative = error occurred.

Return values

NDK_SUCCESS	success
NDK_INVALID_ARG	failed (see remarks)
NDK_FAILED	failed

Remarks

1. The operation of this function does not rely on the initialization of the calendar module.
2. The function is available in the lite (free) version without any restrictions.

1. If the value of retVal is NULL, the function will fail and return NDK_INVALID_ARG as error code.
2. If the value of mnth is zero or greater than 12, the function will fail and return NDK_INVALID_ARG as error code.
3. If the value of weekdy is greater than 6, the function will fail and return NDK_INVALID_ARG as error code.
4. If the value of year is less than 1900 or greater than 2100, the function will fail and return NDK_INVALID_ARG as error code.

Requirements

Requirement	Value
Target Platform	Windows
Header	SFDBM.h (include Windows.h)
Library	SFDBM.lib
DLL	SFDBM.dll SFLUC.dll SFLOG.dll

Since

v1.56

See also

• SFDB_EDATE
• SFDB_WKDYOrder
• SFDB_WEEKDAY
• SFDB_DTADJUST
• SFDB_ISWRKDY
• SFDB_NETWRKDYS
• SFDB_WORKDAY

SFDB_WEEKDAY()

int __stdcall SFDB_WEEKDAY	(	LONG	argDate,
		WORD	argReturnType,
		LPWORD	retVal )

Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default..

Parameters

[in]	argDate	(Required) The serial date number that represents the given date
[in]	argReturnType	(Required) a number that designates the weekdays coding (or ordering) scheme. If missing, convention=1 (Sun=1,..,Sat=7) is assumed.
[out]	retVal	(Required) The functions returns the day of the week corresponding to a date

Returns

status code of the function call: zero = success, positive = success with a warning, and negative = error occurred.

Return values

NDK_SUCCESS	success
NDK_INVALID_ARG	failed (see remarks)
NDK_INVALID_VALUE	failed (see remarks)
NDK_FAILED	failed

Remarks

1. The operation of this function does not rely on the initialization of the calendar module.
2. The function is available in the lite (free) version without any restrictions.
3. If the value of retVal is NULL, the function will fail and return NDK_INVALID_ARG as error code.
4. If the value of the argDate argument does not correspond to a valid date serial number, the function will fails and return NDK_INVALID_VALUE as error code.
5. If the value of the argReturnType greater than 17, the function will fail and return NDK_INVALID_ARG as error code.

Requirements

Requirement	Value
Target Platform	Windows
Header	SFDBM.h (include Windows.h)
Library	SFDBM.lib
DLL	SFDBM.dll SFLUC.dll SFLOG.dll

Since

v1.56

See also

• SFDB_EDATE
• SFDB_NWKDAY
• SFDB_WKDYOrder
• SFDB_DTADJUST
• SFDB_ISWRKDY
• SFDB_NETWRKDYS
• SFDB_WORKDAY

SFDB_WKDYOrder()

int __stdcall SFDB_WKDYOrder	(	LONG	argDate,
		short *	retVal )

Returns the order of the weekday in the month for a given date. This is the inverse operator of SFDB_NWKDAY().

Parameters

[in]	argDate	(Required) The serial date number that represents the given date
[out]	retVal	(Required) a pointer to a varaible to hold the calculated order of the weekday in the month for a given date.

Returns

status code of the function call: zero = success, positive = success with a warning, and negative = error occurred.

Return values

NDK_SUCCESS	success
NDK_INVALID_ARG	failed (see remarks)
NDK_INVALID_VALUE	failed (see remarks)
NDK_FAILED	failed

Remarks

1. The operation of this function does not rely on the initialization of the calendar module.
2. The function is available in the lite (free) version without any restrictions.
3. If the value of retVal is NULL, the function will fail and return NDK_INVALID_ARG as error code.
4. If the value of the argDate argument does not correspond to a valid date serial number, the function will fails and return NDK_INVALID_VALUE as error code.

Requirements

Requirement	Value
Target Platform	Windows
Header	SFDBM.h (include Windows.h)
Library	SFDBM.lib
DLL	SFDBM.dll SFLUC.dll SFLOG.dll

Since

v1.56

• SFDB_EDATE
• SFDB_NWKDAY
• SFDB_WEEKDAY
• SFDB_DTADJUST
• SFDB_ISWRKDY
• SFDB_NETWRKDYS
• SFDB_WORKDAY

SFDB_WORKDAY()

int __stdcall SFDB_WORKDAY	(	LONG	argDate,
		short	nDays,
		LPCTSTR	holidays,
		LPLONG	zDates,
		size_t	nSize,
		WORD	nWkndNo,
		LPLONG	retVal )

Returns the serial date number that represents the date that falls after the start date by a given number of working days

Parameters

[in]	argDate	(Required) The serial date number that represents the given date
[in]	nDays	(Required) A number of workdays to advance
[in]	holidays	(Optional) A (:_:) separated list of holiday names, calendars, countries or currency.
[in]	zDates	(Optional) An array of holidays dates; each expressed as a serial number (i.e. number of days since 1.1.1970)
[in]	nSize	(Required) The number of dates in zDates.
[in]	nWkndNo	(Optional) is the weekend number (1-7,11-17). If 0, the western weekend (i.e. 1, "Saturday, Sunday") is used.
[out]	retVal	(Required) a pointer to a varaible to hold the calculated serial date number.

Returns

status code of the function call: zero = success, positive = success with a warning, and negative = error occurred.

Return values

NDK_SUCCESS	success
NDK_INVALID_ARG	failed (see remarks)
NDK_INVALID_VALUE	failed (see remarks)
NDK_FAILED	failed

Remarks

1. If the calendar module has not been initialized, the function will fail and return NDK_FAILED as the return code.
2. The function is available in the lite (free) version without any restrictions.
3. If the value of retVal argument is NULL, the function will fail and return NDK_INVALID_ARG as error code.
4. If the value of the argDate argument does not correspond to a valid date serial number, the function will fails and return NDK_INVALID_VALUE as error code.
5. If the value of nDays argument is greater than 1000 or less than -1000, the function will fail and return NDK_INVALID_ARG as an error code.
6. If the value of the nWkndNo argument is a valid weekend no (see Weekend functions for full list), the function will fail and return NDK_INVALID_ARG as an error code.

Requirements

Requirement	Value
Target Platform	Windows
Header	SFDBM.h (include Windows.h)
Library	SFDBM.lib
DLL	SFDBM.dll SFLUC.dll SFLOG.dll

Since

v1.56

See also

• SFDB_EDATE
• SFDB_NWKDAY
• SFDB_WKDYOrder
• SFDB_WEEKDAY
• SFDB_DTADJUST
• SFDB_ISWRKDY
• SFDB_NETWRKDYS