This module contains a worksheet with a list of non trading days for the Australian Stock Exchange (ASX). The Excel date references are assigned to the dynamic range named: Holidays.
For further details of ASX trading days, see: Calendars – ASX
TIP: Download a list of ASX non trading dates from the public holidays list in the figure 1: Excel Web App # 1 below
Dynamic range | OFFSET /w COUNT
The Holidays range name is dynamic – Holidays Refers to:
=OFFSET(holidays!$B$3,0,0,COUNT(holidays!$B:$B)-1,8). This allows the name to expand when the user adds new dates for years 2019 and later.
The OFFSET function returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
- SYNTAX: OFFSET(reference, rows, cols, [height], [width])
The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments.
- SYNTAX: COUNT(value1, [value2], …)
In this example, the OFFSET reference is cell
$B$3 which contains the upper left date – Fri 1 Jan 2010 – this is the anchor point – so the OFFSET rows and cols are set to 0. The value for the OFFSET height is returned by the COUNT function – there are 8 numbers (Windows Excel dates are represented by the number of days since 1 January 1900; 1 Jan 2010 = 40,179) in the left date column so the height is 9 (rows). To avoid a false value, the entire column B should not contain any other cells with numbers. The column must be an absolute reference
Because the count values start at 1, and the offset values start at 0, you need to subtract 1 from the COUNT return value to synchronize the two series. OFFSET width is set to 8 because this is the number of Australian national public holidays. With the anchor point set to $B$3, and years 2010 to 2018 inclusive, the Holidays range is 9 rows x 8 columns (ie. a 9 x 8 array).
The choice of worksheet name: holidays, and range name Holidays is intentional. Including the three character day “ddd” item assists with verification of the Holiday values. For example, the Good Friday date reference should always return Fri.
Download the file by using the Microsoft Excel Web App link in #1.
TIP: Copy the date array – if you copy the dates in columns B to I, they will paste as text strings. Instead, scroll to the right in the holidays worksheet, then copy the date serial values in columns K to R
- This example was developed in Excel 2013 Pro 64 bit.
- Last modified: 26 Mar 2018, 3:28 pm [Australian Eastern Time (AET)]