Note: this sheet is available in the Startup File in our store.
In this post:
Purpose
There are some nice Excel templates to create, annotate and print monthly or annual calendars in Excel. Check them out before you start creating your own. If you’re like me you don’t print much, and if you want a perpetual calendar you have many options of course, including Outlook, Google, Apple and others. These full-featured calendar apps allow you to manage your detailed schedule, highlight events, or as you may be chagrined to admit, control your life.
So why build a calendar in Excel when there are so many great ones available? Clearly I like to customize things, but what’s missing?
I want small day cells, yet see clearly what’s happening and coming up. With an input table next to the calendar, I can enter dates and descriptions, then highlight the calendar with those dates. As this concept gestates, I think of other types of inputs, like date ranges, and annual events like birthdays. I think I can calculate holidays with configured rules. Finally, I want another output table to count workdays and custom cycles per week or month bucket. These are all opportunities to demonstrate various features and techniques working with dates.
From humble birth this sheet grew and matured into a showcase, with 6 column groups, 9 controls, 10 tables, 19 range names and 37 formula names. Of the 10 tables, 5 are input, 3 are reference and 2 are output. The tables combined have more than 30 Conditional Formatting rules. Data Validation is used on multiple ranges and cells to provide pick list, enforcement or simply information.
I’m not boasting. This sounds like more complexity than is called for, which is a real risk. My job is to make the case for the complexity, in fact, to show that it is integral to elegant presentation. One flaw in one part could ruin everything. The details must have integrity.
This sheet will be useful in other projects, so I will make it portable. There is no code, and the range and formula names are scoped to the sheet, which means that when they are used they are prefixed with the sheet name, like 'Calendar'!. To simplify notation here, the prefix is removed from references.
Calendar Table
The main output table is Calendar, in the form of a continuous perpetual calendar, with eleven columns; year, quarter, month, week, and the seven days of the week. I use the international standard of weeks starting on Monday, putting the most common weekend at the end of the row (as in weekend). Weekends will be configurable, but not the Monday start. If desired, the table and formulas can be modified to start on a different day.
In preparation for writing formulas and conditions, I create two relative names in Name Manager, This = the current cell and Above = the cell above it, hitting F4 three times to make both column and row relative. Thus, with active cell A2, This = A2 and Above = A1. I accept the implicit disclaimer that Above cannot be used in row 1.
To begin the table, I type in short headings, create the table, name it Calendar, and turn off the filter buttons. I start with a width of 3 for all columns.
Formulas
I’ll calculate the Monday column and use it to derive all the other columns. The Monday formula must combine two things:
For the first row, retrieve start date, calculate Monday of that week
For every other row, increment the previous row by 7
For start date I can use =TODAY() most of the time, but I want to be able to set it as well, so I define an input cell named StartInput, and a formula named Start. If there is a date in StartInput, it is used, if not, the current date is used.
Start =IF(N(StartInput),StartInput,TODAY())
Next, I need to derive the Monday of the week of Start. This calls for WEEKDAY, which returns an ordinal day-of-week number with several options. I usually use type 2, here I use type 3, Monday=0 through Sunday=6, to simplify the formula. For example, if I enter 1/1/2022 in Start, then =WEEKDAY(Start,3) returns 5, that is, Saturday. To get to Monday of that week, I subtract the weekday: Saturday – 5 = Monday.
Monday = Start-WEEKDAY(Start,3)
Finally there is the point of distinguishing the first data row from the others. All the data are numeric and only the header is text, so I can use that to differentiate the top row from others. I can use ISTEXT(Above), TRUE only in the top row, or ISNUMBER(Above), FALSE only in the top row. N(Above) converts text to zero, so if I know the numbers are never zero, this is also FALSE only in the top row. I wouldn’t use N() if the column data are not guaranteed never to have 0 value, but here they are. Whenever N(Above) is TRUE, I add 7 to it, for “next Monday”. When it’s FALSE, “first Monday” applies.
Monday = IF(N(Above),Above+7,Start-WEEKDAY(Start,3))
This formula could be entered on the sheet versus Name Manager, but I’ll be able to use this again elsewhere, so having a name adds value. In column [Mo] I enter =Monday, then every other column is derived from it. Each other day is an offset; Tu=[@Mo]+1, We=[@Mo]+2, etc. Once the seven days are established, I select all seven data columns and use custom number format “d” to show only day numbers, 1 to 31.
For week number, I use =ISOWEEKNUM([@Mo]) and leave it as format General. ISO is the International Standards Organization, which defines the first week of the year as the one containing the first Thursday of the year, which is also the first 4-day week of the year, counting from Monday.
The seven day columns plus week number column will all have values that change by row. Month, quarter and year will each have repeat values that I’ll want to hide. Considering this, I need to choose between calculation and formatting.
For example, I can use custom number format “m” or “mm” to format a date as month number, or “mmm” as abbreviated month name. To do so I would simply copy Monday, M =[@Mo]. The display would be constant for the weeks of each month, but the underlying value would change in each row. Thus, I would not be able to hide repeating values.
To create constant values per month, quarter and year, I calculate:
Month M =MONTH([@Mo]); result is 1 to 12 Quarter Q =ROUNDUP([@M]/3,0); result is 1 to 4 Year Y =YEAR([@Mo]); or, for 2-digit year, use MOD(YEAR([@Mo]),100)
Later, I decided that I preferred to use text names for month and quarter. To do this while keeping constant values, I created two text arrays:
Mons ={"Jan","Feb","Mar","Apr",... etc.} Qtrs ={"JFM","AMJ","JAS","OND"}
The final formulas:
Month M =INDEX(Mons,MONTH([@Mo])) Quarter Q =INDEX(Qtrs,ROUNDUP(MONTH([@Mo])/3,0))
Basic Formatting
I set initial base and Conditional Formats:
Border color is also set for contrast. The point here is not to overstate the details, but to impress on you that they are very receptive to attention.
I try entering some start dates in the input cell, and drag the table bottom edge down to expand. How far down to copy? I’ll mostly be looking at the first screenful, about six months, but extending it for a couple of years doesn’t hurt. Just for fun, I try some outrageous dates. With 12/1/9999 start, I confirm that the computer is not Y10K compliant, and that the “end of time” 12/31/9999 will be a Friday. Y10K is one error I will not bother protecting against.
User Inputs
Now that I have the main output table, I want to design a user interface that will take different types of inputs and reflect each type differently in the Calendar. Collecting my earlier design notes, I’ll have a list of discrete dates, a list of date ranges, and a list of year-independent dates. I previously mentioned the start date input cell, and I’ll add two more input cells to define a cycle; reference date, and cycle length.
As I start to plan how to arrange all these inputs neatly, the advantage of tables becomes clear. When tables are stacked on the sheet and have the same number of columns, insertion and deletion of rows in the upper table move the lower table down and up without disturbing any other columns.
The three individual input cells, StartInput, First and Cycle, can be in one row, with three input tables below it in the same columns. DateRanges table needs two columns for from and to dates, the third will be free text. DateList and Annuals tables will each have one date column and two free text columns.
With the tables set up, I practice navigation. Ctrl+ inserts a row and moves lower tables down, Ctrl- deletes a row and moves lower tables up. Tab in the bottom right corner of any table adds a row to the bottom of the table and moves lower tables down. Unfortunately, this does not guarantee correct formatting. Tab in the bottom row of the bottom table adds a row, but adopts the blue sheet fill instead of the white table fill. This can be avoided by using Ctrl+, or corrected by copy down from the row above.
Since these date entries will be full dates, date format is a concern and will be handled via configuration. They will also share the same Conditional Format that they cause in the Calendar, as a visual link.
Holidays will be configured and calculated. A holiday input table is also included, for holidays that don’t fit the rules defined, or for other non-work days that are not regular holidays. This table resides with configuration rather than inputs.
Configuration
Configuration settings are inputs that are a step removed from the output purpose, and therefore likely to be set once or very rarely. They can be hidden most of the time. In some projects I put them on a separate hidden sheet. Since this sheet is designed to be self-contained, I place them in a column group.
The configuration area contains tables and individual input cells. It’s important to consider how to make them easily maintainable. When tables and non-table data are stacked vertically, the tables should be below, or occupy the same columns as the non-table data, to allow for table row insert/delete. A static lookup table can have fewer columns, since it will not be maintained.
Configuration comprises four input cells, the Weeks reference table, the HolidayRules input table, and the HolidayInput table. Holiday logic will be explained in the next section.
The first three input cells are selections from a validation list. The fourth, WkCustom, is a custom weekend string, following the format as defined in WORKDAY.INTL and NETWORKDAY.INTL functions.
Date Format
The format in which dates are entered is controlled by the computer’s regional settings, but Excel does not automatically display them in the same way. To display them in the selected regional format requires one of only two date formats that begin with an asterisk (*), or to make the format user-selectable.
For control and convenience, I make them selectable via two choices; DateOrder and DateSeparator, each with three choices. DateOrder is one of dmy,mdy,ymd and DateSeparator is one of .,/,-. These two cells define nine total format options. All ranges with full dates are set in Conditional Formatting with nine rules based on these two selections, following the example:
Condition: =AND(DateOrder="dmy",DateSeparator="."), etc. Format: dd.mm.yy, etc.
Work Week
Weekends are of course an important feature of any calendar, but do they need to be customized? From my cursory internet research, approximately 90% of the world’s population work Monday to Friday and have Saturday and Sunday off. Most of the remaining 10%, which are mostly Muslim countries, work Sunday to Thursday, or some close variant. Some countries or companies might work six days, or other pattern. I try to be as inclusive as possible. It is not much trouble to offer work week selection and even unique pattern definition.
Here I use the seven-digit binary string format to define the week, as per the functions WORKDAY.INTL and NETWORKDAYS.INTL. There are simpler ways to select standard, two-consecutive-days weekends, but there is no simpler way to define a custom pattern. The seven-digit string gives me a consistent method of identifying weekends, whether standard or custom.
Holiday calculation references the work week in order to move the holiday from a weekend to the next or previous workday. This is not practically feasible to set up for all weekend possibilities, but it works for any of the seven patterns with two consecutive weekend days.
Work week is selected from a table that lists these seven options; “Mon-Fri”, “Tue-Sat”, etc., and an eighth option for “custom”. The second column in the table lists the WEEKDAY return type, using 11 through 17 for the standard cases, ensuring the weekend is always on days 6 and 7 for the holiday logic. The third column is the binary string, e.g. 0000011 for Saturday-Sunday weekend.
One cell in this table, the custom string cell, is a formula to retrieve the string entered by the user. Here I reluctantly violate a table rule by mixing text and formula in a table column, but it is all read-only. Meanwhile, the name WkList =Weeks[Work] passes the pick list to Data Validation on the selection cell, WkSelect.
Custom week string input cell, WkCustom, has a gray font to make it look inaccessible. When the user selects the custom option in the WkSelect cell, the gray-font cell WkCustom reverts to automatic font color, indicating it is now relevant and ready for input. Data Validation is defined for the input cell, with appropriate instructions, and the following condition:
Allow: Custom Formula: =AND(LEN(WkCustom)=7,NOT(ISERR(BIN2DEC(WkCustom)))) Error message: must be 7 digits of 0's and 1's
Name Manager formulas WkString and WdType retrieve the week string and weekday type based on a match of WkSelect against WkList.
WkString is used in the workday count formulas, and also provides access to a Boolean “is weekend” condition by dissection of the string. The formula segment below picks the character from WkString corresponding to the weekday of the reference date. Since the seven characters of the string always represent Monday to Sunday, it is based on weekday type 2.
(is weekend) = ... MID(WkString,WEEKDAY(Date,2),1) ...
Can the start day of the week be customized? It’s possible if I don’t use a table (then I could use a formula header), or if I use VBA, but that’s a cost I’m not willing to pay. Monday start is a common international standard, letting most people see their weekend at the week’s end.
Holidays
Holiday calculation itself is a worthy of an app or add-in, and there are no doubt some available. Here it’s an opportunity to illustrate a process.
Holidays are defined at various levels – country, state, local – and employers choose which ones to give employees as days off. Some are standard, but there are a lot of choices, and some are made up. Some factories shut down for the first day of hunting season. It’s not hard to input a list of dates once a year, but holidays do follow rules, and if I can discover the rules then I can use them to calculate the dates for any year. This presents an intriguing challenge.
I chose a list of eleven US holidays that are representative of various rules – first Monday of the month, last Monday of the month, fixed date unless weekend, and some “Eve/Day” pairs that require special rules for weekends. There’s even a day after the last Thursday of the month. Other religious and national holidays may have more complex rules which may need other logic or manual entry.
When I looked at all the US rules, I realized that they fall into two general types, or forms:
The “not weekend” form: fixed date unless on a weekend
The “nth weekday” form: a certain weekday of the month
The goal here is to come up with a single formula that covers both forms with all their variants. The formula must work for any given input year and rule. To enable this, each holiday is defined as a rule in a table with four parameters, and several stages of calculation. But before I could define the parameters and formulas, I had to work out the logic.
Not Weekend
For now assume that the weekend is Saturday and Sunday. This form has three cases. The first is a stand-alone holiday, which moves to the nearest business day. If the date falls on a Saturday it is moved to Friday, and if it falls on Sunday it is moved to Monday. The second and third cases are for two consecutive holidays, “Eve” and “Day”, which need special rules, depending on which date or if both dates fall on the weekend.
Each of these cases has a nice simple formula, derived empirically by laying out Friday to Monday with the offset that occurs whenever the holiday is on a weekend. For example, move type 1 moves from Saturday to Friday, offset -1, and from Sunday to Monday, offset +1. For move type 2 and 3, if the Eve/Day pair is Friday/Saturday, Eve doesn’t move, Day moves +2 to Monday. The table below summarizes the observed logic, the shaded area showing three possible scenarios for Eve/Day.
Here are the form 1 calculation stages in descending order. The bottom three names retrieve the rule parameters, and two others transform them to date and weekday.
Nth Weekday
To determine nth weekday, I again turn to a blank grid to work out the possibilities. To begin, I take stock of what I know: there will always be four of every weekday in any month, some months will have a fifth of some weekdays, and the nth of any weekday depends on which weekday begins the month.
Based on this, I build a grid with 7 rows for “weekday of the first of the month” and 35 columns for “nth (1-5) weekday (1-7)”. If the fifth weekday spills into the next month, I’ll handle that later.
I start filling the grid by just counting. For example, US Labor Day is the first (Nth=1) Monday (Wd=1) of September. If September 1 is a Wednesday (3), we can count off the days to find that Labor Day will be September 6, as outlined in the table above.
Very soon, patterns emerge that are hard to intuit without this kind of brute-force method. For one thing, it clearly depends on the difference between the two weekdays, the sought-for one and the first-of-the-month one.
Finally, I arrive at the following steps:
Get the date of the first day of the month (FoM)
Add input weekday minus FOM weekday (NWd_1)
Calculate Nth weeks, minus a week if input weekday >= FOM weekday (NWd_2)
Add step 2 + step 3 + offset day if defined (NWd_3)
If step 4 is past the end of the month then subtract 7 (NthWd)
Here are the form 2 calculation stages in descending order. Note the similarity between this table and the “not weekend” table, from the bottom row up; Month is used in both forms, followed by formulas to retrieve par1 and par2. In form 2 though, Nth (par1) is capped at 5, and Wd (par2) has INT applied, in case of a decimal offset, which adds a day via Add.
Holiday Rules Table
The HolidayRules table auto-increments the rule index by =N(Above)+1, and the user maintains the description and the four parameters. Data Validation is used in each column to guide and control entries. Allowed values are whole numbers for months (1-12); form (1-2); and par1 (1-31); decimal for par2 (1.0-7.9). Entry-point guidance is essential due to the obscure logic, and the Input Message is perfect for this.
Holidays Input Table
The rules established are not sophisticated enough to calculate all holidays, such as those based on lunar cycles. The easy way around the exceptions is to enable manual entry of holiday dates. The downside is that they must be maintained for each year, the upside is flexibility.
The HolidayInput table is situated below HolidayRules and with the same number of columns. It has just two input columns for name and date. Other columns are not really necessary, except [err], which checks for conflicts with weekends and calculated holidays. By making the tables the same width, rules can be inserted or deleted and the lower table will keep its distance.
Holidays Calculation Table
The result of all the Name Manager and HolidayRules table setup work is the HolidayCalc output table. This table is in its own column group, so that it can be extended as long as needed and viewed by the user as desired. The objectives are to start with the current year based on input date, and automatically update when rules are added or deleted. The table has five columns.
The year starts with the minimum year of the calendar Sunday column, and then increments when rule 1 comes up next. The rule starts at 1 in the first row, and starts over after the last rule number is reached. Name looks up the holiday name, and Holiday is the final output formula, choosing NotWe or NthWd as described earlier.
Holiday = IFERROR(CHOOSE(INDEX(HolidayRules[form],Holidays[@rule]),NotWe,NthWd),"")
The [err] column is to flag holidays that conflict with weekends. This wouldn’t make sense, but can occur while setting up the sheet. In case of error, the table row turns red. The user must then adjust either holidays or weekends as needed.
A cell above the table header checks both HolidayCalc and HolidayInput tables and reports if any error is found. It also checks if HolidayCalc horizon is less than the Calendar horizon, prompting the user to extend the horizon by copying the bottom row down.
=IF(SUM(HolidayCalc[err],HolidayInput[err]),"errors", IF(MAX(Calendar[Mo])>MAX(HolidayCalc[date]),"extend",""))
Highlighting
Now that I have user inputs and configuration defined, it’s time to return to the Calendar to set up Conditional Formatting. This is a chance to play artist. First, let me review what it is that I’m highlighting.
I’ve defined four types of input dates; date ranges, discrete dates, annual events, and cycles. I’ve configured two other types of dates; weekends and holidays. Finally, I want to highlight the current date. I have a total of seven conditions to format.
The next task is to decide the type of format for each, and the order. I think that most of the time, most of the date types will not overlap, but of course it will happen, and I’ll handle it as best as I can.
Condition Order
When multiple conditions are applied to the same range, consider the order in which they apply. If the conditions are mutually exclusive, then they can’t overlap and order doesn’t matter. For example, the first Monday and the second Wednesday of each quarter can never conflict.
If they are not mutually exclusive, they apply in the order listed. That’s why there are buttons to move conditions up and down in the list. If the formats themselves are not mutually exclusive, they accumulate top-down, unless “stop if true” is ticked.
For example, if you have two or more conditions that can overlap, say a birthday and a holiday on the same day, and they both set fill color, the top one wins. But if one sets fill color and the other sets bold font, they can both show.
I work out a reasonable balance of formats and order based on importance. Later, I’ll add conditions to show overlaps. The six initial format types and their inputs are listed here, and explained in order from bottom up.
Weekend
As described above, WkString is a seven-character digital string. The condition formula inWeekend then picks out the character of the string representing the weekday of the current cell’s date. Because the weekend string format is Monday to Sunday, WEEKDAY type 2 identifies the character. VALUE converts the character to a number, 1 = TRUE for weekends.
inWeekend =VALUE(MID(WkString,WEEKDAY(This,2),1))
I give this condition a dark gray, thin diagonal crosshatch pattern, very visible but not thick enough to obscure the numbers or the alternating month color.
Current Date
The current date is the easiest one to do directly in Conditional Formatting. If the date is today, flag it. Note, if start date is blank, today will always be in the top row, and if start is in the future, of course today will not be visible!
Format condition formula for the current day: =This=TODAY()
This is given a fill color, but as a low priority. It is later combined with Cycle to set bold font, in case the fill is covered by another condition.
Date Ranges
DateRanges table has key columns [from] and [to]. The condition formula is in two steps.
Step 1 finds the [from] date that indicates that This is in a defined date range. This calls for MATCH type 1. I create the name From, which finds the equal or nearest lower [from] date to This, or IFNA returns 0 if none found. This type of matching requires that the [from] column be sorted ascending.
Step 2 then uses INDEX to find the [to] date in the same row as From; AND collects the dates that are between [from] and [to], inclusive.
From = IFNA(MATCH(This,DateRanges[from],1),0) Format condition formula for date ranges: inRanges = AND(From,This<=INDEX(DateRanges[to],From))
Data Validation rules are set to ensure the two requirements.
Valid: [from] is sorted ascending =M10>N(Above) Valid: [to] >= [from] =N10>=M10
Holidays
As explained previously, holidays can come from either HolidayCalc table or HolidayInput table. The condition is the sum of two COUNTIF functions against each table, equivalent to logical OR.
COUNTIF(HolidayCalc[date],This)+COUNTIF(HolidayInput[date],This)
Annual Events
Annuals are meant to highlight birthdays, anniversaries, and so forth that occur on the same date every year. Annual events need to be matched based on month and day only, independent of year. To do this I convert dates to “mdd” integers, from 101 to 1231, by 100*Month+Day. MDThis applies to This cell; MDAnnuals returns an array representing the event dates, Annuals[date], converted in the same way. MATCH returns TRUE when MDThis is in MDAnnuals.
MDThis = 100*MONTH(This)+DAY(This) MDAnnuals = 100*MONTH(Annuals[date])+DAY(Annuals[date]) Format condition formula for annual events: inAnnuals = MATCH(MDThis,MDAnnuals,0)
Date List
DateList condition uses COUNTIF as a Boolean “exists or not”.
Format condition formula for date list: inList=COUNTIF(DateList[date],This)
Cycle
This is intended for a 14-day pay cycle, 91-day reporting cycle, or for any event that occurs with regular frequency. To highlight the cycle in the calendar I define two input cells, First is a reference date and Cycle is the cycle in days. The Cycle cell is formatted "every "0" days", so that the number entered is displayed as a phrase. The condition uses MOD to tell when This is a multiple of Cycle from First.
Format condition formula for cycle: inCycle = MOD(This-First,Cycle)=0
User Controls
Adding form controls to a project is a choice to make when it enhances value. Among the simplest opportunities is a checkbox control, it’s a nice way to present an option toggle switch. The checkbox will be linked to a cell which will contain its TRUE or FALSE state. This cell is then used in conditional formulas. The general steps to follow are:
Name and hide the switch cell
Insert a checkbox and link it to the switch cell
Add the switch cell as a condition in formulas or formats
Here I want to give the user the option to display or not each of the input types. The switch cells should be invisible – I don’t want “TRUE” and “FALSE” all over the screen. This could be done by formatting the cells, or by hiding them in a group, or on a hidden sheet. Within the calendar control panel I name and hide each switch cell. The names follow a convention: ShowAnnuals, ShowCycle, etc. The cells are hidden by format, “;;;”.
From the Developer tab I insert a Form Check Box for each switch cell, adjusting size and alignment to make them all fit the row height and look neatly spaced. With a right-click on the control, I select Edit Text to write what it does – “show dates” etc. With another right-click, I select Format Control, and in Cell link, type in the respective switch cell name.
With the Check Boxes formatted and linked to their respective cells, I edit each input table and Calendar format condition, adding “Show...” to the existing one, using AND. Checking each box now toggles the highlight on and off in both the input table date column and the Calendar table. The final conditions for both tables are shown below. For input columns, LEN(This) formats only the non-blank cells.
Coming back after building the second output table of bucket counts, I needed controls for the exclusion or inclusion of certain days in the work day and cycle count calculations. Additional “count” checkboxes were added, neatly juxtaposed with the relevant “show” controls for cycles, date ranges, date list and holidays.
Process Table
As sometimes occurs when I reach a point of mounting complexity, I felt the need to extract the conditions into a table for better control and visibility of both Calendar and Buckets tables. This was added after Buckets, described in the next section, but needs to be explained now for clarity.
The point of overlapping conditions was like a bug in my ear. I wanted some way to flag such cases. Combining conditions would quickly become overly complex and was out of the question. On top of that, the calculation of bucket counts was cumbersome, with multiple ranges of dates to subtract from working days. I tried various ways, finally settling once again on a process table. It had to be granular, to expose daily conditions that could then be counted. I decided on a reference table named Days, hidden in another column group.
The first four columns are Y, M, W and Day, with appropriate duplicate hiding as used before. The first row lines up with the first row of the Buckets table, to ensure that counts will be accurate.
[Day] =IF(N(Above),Above+1,IF(Bucket="W",Monday, EOMONTH(EDATE(Start,-1),0)+1))
Show Conditions
Following [Day] are four columns of conditions for the four main “show” options; Annuals, Ranges, List and Holidays. Weekend, Today and Cycle are not needed. Each column uses the logic as described in the previous section, but instead of being based on This, each condition is based on [@Day]. These four columns, [isAnnual], [isRange], [isList], and [isHoliday], each determine if [@Day] is their type, and return 1 if so or 0 if not. The logic can be reviewed in the Highlighting section, here’s an example:
[isList] =IF(COUNTIF(DateList[date],[@Day]),1,0)
Conditions are modified in Name Manager for each type, to look in its own Days column and return TRUE if 1 is found. Conditional Formatting rules are unchanged. Example:
inList =INDEX(Days[isList],MATCH(This,Days[Day],0))
To identify days with multiple conditions, it must be based on the user’s selections of whether to display them or not. For convenience, above the header of each column I retrieve the setting thus:
=IF(ShowAnnuals,1,0), etc.
These four cells are formatted to display "show" if 1 and blank if 0, and the four-cell range is named DaysShowSwitches. The next column [Highlights] counts the selected conditions by multiplying two binary four-cell ranges.
[Highlights] =SUMPRODUCT(DaysShowSwitches,Days[@[isAnnual]:[isHoliday]])
Now I can find overlapping conditions by creating two more Name Manager formulas.
inTwo =INDEX(Days[Highlights],MATCH(This,Days[Day],0))=2 inThree =INDEX(Days[Highlights],MATCH(This,Days[Day],0))>2
There’s not a lot of options left for formatting that will show through the fill, but I manage to find two types that have an adequate effect; Number format "+"d, combined with Font format single underline for inTwo, and double underline for inThree.
Count Conditions
The second purpose of Days is to help with counts. Annuals are not relevant, so there are three more columns, [CtRng], [CtList] and [CtHols]. The respective “count” checkbox values are retrieved, and hybrid-named = DaysCountSwitch, absolute row and relative column in which they lie. Each column has a simple formula to retrieve the condition already established, if the count is turned on, returning 1 or 0.
[CtRng] =[@isRange]*DaysCountSwitch, etc.
The final column collects all the selected off days by multiplying the max of the three columns (0 or 1) by [@Day]. This single column can now be used as the “holidays” argument in the workdays calculation to come next.
[OffDays] =PRODUCT(MAX(Days[@[CtRng]:[CtHols]]),[@Day])
Bucket Counts
The primary Calendar output is ready, and I’ve defined several inputs to enhance its usefulness. Now there’s the other output table, to summarize weekly or monthly counts of workdays and cycles.
Workdays are used for capacity planning, and this will be useful in other projects. Even with weekends alone and no holidays, months range from 20 to 23 workdays, a 15% difference. Add holidays and the gap widens, add special days and it widens further. Output expectations and work assignments need to consider capacity based on working days available.
For cycles, a common payday example is every 14 days, 26 per year. Most months have two, and two months per year have three. For monthly budgeting it’s nice to know which months have three paydays. For longer cycles, it helps to know in which buckets it occurs.
It can be important to have counts for both weeks and months. Above the table is a selection cell named Bucket with two options, M or W. The Buckets table has five columns, with formatting to display either months or weeks.
The middle column [bucket] is the key. This is a date value, =Monday if week (the same formula used in Calendar), or else the first of the month.
[bucket] =IF(Bucket="W",Monday,EOMONTH(IF(N(Above),Above, EDATE(Start,-1)),0)+1)
[num] converts [bucket] to either week number or month number, and [year] is just the year of the bucket (what a time to be alive!).
[bucket] format is “dd mmm”, appropriate for week buckets, with two conditions; for weeks within the same month, “dd” to remove month, and for month buckets, “mmm” to remove day.
Work Days
Column [work] uses the international function NETWORKDAYS.INTL with all four arguments. I can use [@bucket] as the start argument, because it is always the first day of the bucket. For the end argument, another name is used:
EoBThis =IF(Bucket="W",Buckets[@bucket]+6, EOMONTH(Buckets[@bucket],0))
WkString is the weekend argument, and Days[OffDays] is the holiday argument.
[work] =IFERROR(NETWORKDAYS.INTL([@bucket],EoBThis, WkString,Days[OffDays]),0)
Cycles
To count cycles requires knowing the date of the first occurrence of the cycle within the bucket, if any. The initial steps are as numbered below and are defined in a formula named FirstCoB
Get the last day of the previous bucket
Subtract the portion of cycle passed as of then
Add cycle
FirstCoB = Buckets[@bucket]-1-MOD(Buckets[@bucket]-1-First,Cycle)+Cycle
Using this to get the count, if FirstCoB is beyond the end of the bucket, the count is 0, otherwise, count the cycles remaining in the bucket, and add 1 for the first.
[cycles] = IF(FirstCoB>EoBThis,0,IFERROR(ROUNDDOWN((EoBThis-FirstCoB)/Cycle,0)+1,""))
These two output columns, [work] and [cycles] have a small range of values, and the extremes are what is interesting, so I use data bars on each column separately to visualize them.
Horizons
With any project, especially one with so many interdependent tables, it’s important to have a governance strategy. I’ve built in error handling with Data Validation and special red-flag formulas and messages. A more subtle risk is the chance that supplying tables run out of data before the receiving tables. This might be called a domain mismatch, or since we’re working with dates, a horizon mismatch.
Calendar table shows every day, but in seven columns, so a row is one week. Days table is one row per day. An obvious concern is that, in order to cover the same horizon, it will need seven times as many rows as Calendar.
Days is an intermediate supplier, input tables are the primary suppliers. HolidayInput, DateRanges and DateList of course are dependent on the user to maintain as far into the future as needed and possible. Days adds no value beyond the user input horizon. The risk is uncertainty, that the user could input dates that don’t show or are not counted as expected.
The three input tables noted are each added another rule, to turn the font red if a date is beyond the Days table horizon. An accompanying message appears above each table to alert the user.
It is not necessary to extend Days to cover Calendar and Buckets output tables, as long as it covers the input table horizons. However, for transparency, I also add output table formats to show where the Days horizon ends, with a gray cross-hatch similar to weekends. The message is not to rely on the output that far out.
There might be a touch of overkill in all these conditions. Most of the user time spent on this sheet will be on the first screen. It’s a matter of integrity to account for the rare, even unexpected use so as never to misinform.
A final note on horizon maintenance is that it is incumbent on the user to delete the past occasionally.
Tweaks and Tips
Data Validation messages are attached to several symbols in row 1, one for Calendar Help and the others as column group headers.
The column group labeled “Settings and Inputs” is the main dashboard of user interaction, and I expect it will normally be open. If closed, the checkbox controls will remain visible, which is not desirable. A tip in Calendar Help is to hide controls by Ctrl+6. When done, also hidden is a rectangle that hides text, saying “Ctrl+6 to show controls”. It is more important as a reminder how to turn them on again than to turn them off.
A fourth column is added to each of the input tables in this section, and the non-table settings range at top is adjusted to match. The column [until] calculates days until the next date, capped at ±999:
[until] =IF([@date],MIN(MAX([@date]-TODAY(),-999),999),0)
For Annuals and for Cycle it is modified to return days until the next occurrence.
The Calendar can be used to pick a date, then copy and paste where it’s needed. The target cell should have an appropriate date format, and the paste should be the value only. Paste values via right-click or from the quick access toolbar, or by one of the shortcut methods shown earlier. This technique can be practiced by copying a date from the calendar and pasting it as a value to the StartInput cell.
Comments