HIDE the HOLIDAYS
Having a list of financial sector trading days is a useful tool for many businesses.
IS THERE A WAY TO CREATE A LIST OF TRADING DAYS FROM A TABLE OF PUBLIC HOLIDAYS?
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:
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.
2. On the Excel ribbon, select Home > Editing > Fill, then click the Series item. Complete the following properties on the Series dialog box:
- Series in: Columns
- Type: Date
- Date unit: Weekday
- Step value: -1 (because the dates are in descending order - ie. going back in time)
- Stop value: 1 January 2010 (entered as a date 1Jan10 - using Windows Regional Settings - English Australia)
The completed Series dialog box looks like this - figure 3:
- Click the OK button.
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
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
- Syntax: COUNTIF(range, criteria)
- range: is the range where you want to count non blank cells.
- criteria: is the condition that defines which cells will be counted.
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
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
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.
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.
- List range:
$C$12:$C$1055. This must be an absolute reference. Include the column heading otherwise the filter may not perform as expected.
- Criteria range:
$C$8:$C$9. This must be an absolute reference.
- Under the Action heading, select
Copy to another location.
- Copy to:
TDList!$E$12. This is the top cell of the target range. It must be on the same worksheet as the source.
- Click the OK button.
The filter will be completed by pasting the filtered days to Filtered vector at cell
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