Happy New Year to all Excel Hero readers!
One of my very first posts on this blog was about the
Live Calendar, and that was almost two years ago!
To celebrate our two year anniversary as a community and since it is the dawn of a new year, I thought it only appropriate to share another calendar.
But this one is truly special, a veritable tour de force of the Excel methods that we champion here.
When I develop Excel solutions, I try to componentize as much of the solution as possible.
The VBA is broken down into focused, reusable subroutines and functions. Variables are used to hold values that will be referenced more than one time. The With statement is used for object properties and methods that will be used more than once in close proximity of code execution.
But this methodology does not end with VBA. On the front end of workbooks, I use and reuse Named Formulas in exactly the same way as variables in VBA, meaning I define names for any formula that will be reused.
All of this is done to maximize maintainability and to simplify complexity.
Today I'm sharing a Universal Calendar. While it does not make use of any VBA, it very clearly shows the power of componentized named formulas.
I'm proud of this workbook, as it is enormously instructive. Everything is generalized. The user can specify the year and month (school years start in September for example) that the 12-month calendar begins. Likewise, the user can specify the day of the week the calendar starts with.
Then the fun really begins. The user can select one through four date types to display on each day of the calendar from a pallet of seven date display types, and in ANY order. The types are the normal calendar day (1-31), Ordinal Date (what many people incorrectly refer to as
Julian Dates, and is really the day number for the year), Ordinal Date Custom (which is the day number since the start of the user's custom start month for this calendar), Days to End of Year, Days to End of Year Custom, the true scientific Julian Date, and finally, BLANK.
Formula Symphony
This is all accomplished through named formulas, with no scratch areas on any worksheet and no helper cells.
Another interesting aspect are the formulas used to calculate the holidays, such things as the 3rd Thursday in November, the last Monday in May, and the like... and they work on a 12-month calendar where the starting date is specified by the user. For example, if the Custom Calendar Start month is February, then January comes after February. So the formulas need to take this into account and work for any start month when calculating the standard holidays. This Universal calendar also allows the user to specify hard dates that will be counted as a holidays as well.
All of these dynamic calculations and dynamic reporting output is lightning fast. However, the workbook is not. Because adding the conditional formatting required for the holiday lookup to the 2,220 cells in the calendar really slows down the performance. On my computer running Excel 2010, the workbook takes about one second to recalculate. Usually this would be unacceptable performance for one of my models, but in this case I think it does not really matter. A dynamic calendar is not something that is updated often.
If I had allowed myself to utilize VBA, the resulting calendar would be instantaneous, but I purposefully chose a pure formula play for this project since many companies restrict VBA usage and I wanted to make this Universal Calendar Template useful to as many individuals and departments as possible.
Explore the Workbook
When you open the workbook, first go to the setup sheet. Play around with the various settings to see just how dynamic the calendar really is. Study the formulas for the calculated holidays. The technique is a special case of generalized approach that can be used in any situation where you need to find the Nth occurrence of a criteria from a list, either from the beginning or the end of the list. The wrinkles here are that the list of dates is virtual (not in cells), and created as arrays within named formulas; and the other wrinkle of course is the fact that the months are in an unpredictable order!
Next, open the Name Manager and study the named formulas.
I'm calling this the Universal Calendar because it is a template that will work in all versions of Excel from 2000 onwards. It should work in virtually all language versions as well. It allows the user to select the start year, the start month, and even the day of the week to be in the first column of the calendar. It automatically calculates standard US holidays. It allows for custom holidays and will display and highlight those holidays on the calendar. And it supports seven date display types and allows the user to select up to four of those date types to display on each day of the calendar - Calendar day numbers, Ordinal Days, Ordinal Days Custom, Days to End of Year, Days to End of Custom Year, true Scientific Julian Dates, and BLANK. And the four selected date display types can be displayed in any order on each day. And finally, if you are using 2007 or newer, this calendar fully supports Excel Themes.
The key to all of this is a contiguous array of dates in memory for the entire custom year that is indexed appropriately for each display day.
Get The Calendar
I feel that the average Excel user could learn a tremendous amount by studying this workbook. If you have not yet had a chance to enroll in the
Excel Hero Academy, please consider doing so. The next class starts in a few weeks! You can learn how to design solutions like this, taking Excel to the Next Level... and you'll learn all about VBA as well. There is no other course like it.
Here is the elegant Excel Universal Calendar Template:
To learn more about wielding Excel formulas like a master, read these pillar articles here at Excel Hero:
