HIDE the HOLIDAYS

Having a list of financial sector trading days is a useful tool for many businesses.

QUESTION

IS THERE A WAY TO CREATE A LIST OF TRADING DAYS FROM A TABLE OF PUBLIC HOLIDAYS?

Answer iconANSWER


We will demonstrate the answer by creating a list of Australian trading days over the period 31 December 2013 to 1 January 2010. The list will be in descending order with the most recent date at top.

First, the lists of holidays are entered in a range in a worksheet named TDList. An undated list is avaliable at: ASX non trading days


1. Create a table of holidays

Using appropriate labels, the holiday dates are entered is date reference style as shown by the range C2:J6 below. This range is given the name: Closed.

TD
Fig 1: List of ASX non trading days - 2010 to 2013 inclusive

The custom format ddd d mmm yyyy has been applied to the Closed range in figure 1. This improves accuracy in validating the data.

Next we construct a list of weekdays.


2. Create a list of weekdays

To create a list of weekdays follow these steps:

1. Enter the last (most recent) date of the data range selected as the seed date. See cell C13 in figure 2.

TD
Fig 2: Enter the seed date - 31 Dec 13 in cell C13

2. On the Excel ribbon, select Home > Editing > Fill, then click the Series item. Complete the following properties on the Series dialog box:

The completed Series dialog box looks like this - figure 3:

Series Dialog
Fig 3: Series dialog box - with settings completed

The holiday entries can now be filtered by using the Excel Advanced Filter.


3. Use the advanced filter to hide the holidays

The Advanced Filter requires a Criteria Range for its operation. In this case the Criteria will be based on a formula that returns TRUE when the Weekdays date is not in the Closed range. To determine this, the Excel COUNTIF function is used in combination with a logical operator.

The formula has two parts. The first component is =COUNTIF(Closed,C13). The second component is the the logical operator and the final formula is =COUNTIF(Closed,C13)=0.The setup of the Advanced Filter criteria is explained below.


How does the filter formula work?

The COUNTIF function

If COUNTIF returns 1, then the date is a holiday. As we only want to display records where the where the date is NOT a holiday, we are only concerned with records where COUNTIF returns 0.


Apply the advanced filter criteria

1. Enter the filter formula, =COUNTIF(Closed,C13)=0 at cell C9. Only the address of the first record need be included, C13 in this case. The address must be relative reference style.

The Filter Criteria cell will return TRUE or FALSE depending on the value in first cell.

2. The cell above (C8) is the criteria label, enter the label Trading Filter. Alternatively, this cell can be left blank, but it must be included in the Criteria range - figure 4.

Advanced Filter Dates
Fig 4: The criteria range - C8:C9

3. On the Excel ribbon, select Data > Sort & Filter, then click the Advanced item. This will display the Advanced Filter dialog, shown in figure 5.


Complete the following properties on the Advanced Filter dialog box - figure 5.

Advanced Filter Criteria
Fig 5: Advanced filter dialog box - with settings completed

The filter will be completed by pasting the filtered days to Filtered vector at cell E12.

Sample Filter Output
Fig 6: Filtered weekdays - a list of trading days for the ASX 2010 to 2013

When completed, the Advanced Filter creates a Criteria range name, and an Extract range name.

The Filtered Weekdays vector can now be used as desired.


Associated Excel file: hidetheholidays.xlsx 26 KB. This file has been updated to include non trading days until 31 December 2018.

Related material: ASX non trading days