Public Holidays.xlsm
SHA-256 hash 89db0e4481d36f7d1c7663529de795aa44c668363e36098cb4801221b54239d2
An Excel spreadsheet that generates public holiday dates from the rules used to define them. Holidays that fall on weekends and which are deferred to a following weekday are managed.
This spreadsheet is useful when planning activities and events, even though database backends to calendar apps can display public holidays directly in those apps. I still find myself using some of the internal formulas in other spreadsheets when I need to calculate a specific date from a rule.
Entry sheet
The Holidays sheet is used to enter the public holiday rules.
The year is entered at the top, and public holidays and the rules defining the dates on which they occur are entered in the table.
Rules may be of several types.
- A specific date may be entered as day and month. For example Christmas Day is 25 December.
- The day can be specified as an ordinal day in a month. For example Melbourne Cup Day is the first Tuesday in November.
- A day adjustment can be applied. For example Reconciliation Day is the first Monday on or after 27 May.
- Good Friday is calculated using a formula, which I’ve had for so long I have lost the source,
FLOOR(DAY(MINUTE(Year/38)/2+56)&”/5/”&Year,7)-36
where Year is the four digit year. Easter Monday is derived from that. - Some public holidays are always celebrated on their nominated date, but others will have a deferred public holiday to the Monday of the following week. The Defer value allows this: a value of 1 specifies Monday, and a value of 2 allows Tuesday if another deferral takes the Monday (such as happens for Christmas and Boxing Day.)
Macro-enabled file
The Holidays spreadsheet contains Excel macro code to facilitate the Sorting of holidays into ascending date order.
Since it is possible for a malicious user to modify the embedded code in a file, the SHA-256 hash is provided above to ensure your download is my original, unaltered file.
If you are unable or unwilling to accept the macros, it is still possible to access the required sort via the toolbar Data~Sort.
Multi-year List
A multi-year view is provided in the Years sheet. No data entry is required in this sheet as it is populated from the values in the Holidays sheet.
The logic area on the right can be used to filter holidays for a particular State.
