Feel free to Download the Excel Calendar HERE. (Note, this works if your regional settings are English and dates are input as MM/DD/YY). For the European version – where dates are input as DD/MM/YY – Please Download THIS VERSION.
(Many thanks to Adam for sharing this with me and our community)
Read on to find out how this dynamic Excel calendar was created.
Overview of Operation
NOTE: Click on images with greater detail to view an enlarged version of the image.
To use the calendar, select a year from the spin button in the upper-left corner of the sheet.
When selecting a new year, the mini-calendars change to display each month with its new Sunday-through-Saturday date arrangement.
Holiday Dates
There is a fixed set of holidays listed to the upper-right. This is locked and cannot be modified.
The dates for each holiday are calculated based on the previously selected year. Because most of the holidays are not set on a specific date (like December 25th for Christmas), the date must be calculated.
Paid holidays are colored with orange while unpaid holidays are colored in purple.
Personal Appointments
Another table is supplied in the lower-right to allow the user to enter dates for personal appointments or reminders. This is not locked and can be modified by the user.
Now that we know how to use this amazing calendar, let’s look behind the curtain and see what makes it tick.
Using the Name Manager to Define Operational Ranges
The Name Manager has several user-defined named ranges to ease the construction (and understanding) of the upcoming formulas.
I know this looks intimidating, but lucky for us the logic is the same for each of the entries.
If we break down one of the named ranges and understand what it is doing, the rest is will fall into place quite easily.
First, we can ignore the last two entries (“Table2” and “Print_Area”) since they don’t really play a part in the operational aspect of the calendar.
Next, we will take the entry for January named “JanSun1” and examine its logic.
=DATEVALUE("1/1/" & 'Any Year Calendar'!$A$3) - WEEKDAY(DATEVALUE("1/1/" & 'Any Year Calendar'!$A$3)) + 1- Using the DATEVALUE function, create a date for January 1st of the selected year in cell A3 from the spin buttons.
- Create the same date in Step 1 but use the WEEKDAY function to determine the day of the week that the day falls. This will be represented as a number from 1 to 7 (1=”Sunday” and 7=”Saturday”).
- Subtract the weekday number from the created 1/1/YYYY date.
- Add 1 day to the result to account for mathematical offset.
As an example, using 2019 as our selected year, all the cells representing January would calculate as follows:
1/1/2019 – 3 + 1
43466 – 3 + 1 (convert date to number)
43463 + 1
43464
12/30/2018 (convert number to date)
NOTE: This does NOT generate the displayed numbers in the mini-calendar. The numbers will be calculated by formulas in the mini-calendar cells as discussed in the next section.
At this point, the named range “JanSun1” stores the date of 12/30/2018.
Using the Same Logic for the Remaining Mini-Calendars
If you use the same logic for each of the remaining months, you will arrive at the following results (all cells for the month have the same date):
- JanSun1 – 12/30/2018
- FebSun1 – 1/27/2019
- MarSun1 – 2/24/2019
- AprSun1 – 3/31/2019
- MaySun1 – 4/28/2019
- JunSun1 – 5/26/2019
- JulSun1 – 6/30/2019
- AugSun1 – 7/28/2019
- SepSun1 – 9/1/2019
- OctSun1 – 9/29/2019
- NovSun1 – 10/27/2019
- DecSun1 – 12/1/2019
Calculating the Displayed Mini-Calendar Dates
Each cell in each monthly calendar stores a formula that calculates the day for a given cell.
=IF(AND(YEAR(JanSun1) = $A$3, MONTH(JanSun1) = 1), JanSun1, "")- Using the YEAR function, extract the year from the date calculated by the “JanSun1” named range.
- Compare the extracted year from Step 1 to the user-selected year.
- Using the MONTH function, extract the month number from the date calculated by the “JanSun1” named range.
- Compare the extracted month number from Step 3 to a hard-coded 1 (represents ‘January’).
- The results of both tests (Steps 2 & 4) are evaluated by an AND
- The results of the AND evaluation in Step 5 are sent to an IF function that decides whether to display the results of the “JanSun1” named range or display nothing/empty text (two-sets of double quotes).
Small Calculation Adjustments for Each Day of the Month
Each calculation needs to offset its result by a set amount. Starting with the first Sunday, the offset is not required.
The next day on the calendar (Monday) would have an offset of 1.
The next day on the calendar (Tuesday) would have an offset of 2.
This continues to the 42nd box in the calendar which would have an offset of 41.
As an example, using January 1, 2019 as our selected date, the first day (which falls on a Tuesday) would calculate as such:
(JanSun1 + 2) = A3 and (JanSun1 + 2) = 1
(12/30/2018 + 2) = A3 and (12/30/2018 + 2) = 1
1/1/2019 = A3 and 1/1/2019 = 1
2019 = 2019 and 1 = 1 (extracted year and month)
both are TRUE so the AND result is TRUE (send the result to IF)
IF(result, JanSun1 + 2, “”)
IF(TRUE, 12/30/2018 + 2, “”)
IF(TRUE, 1/1/2019, “”)
1/1/2019
1 (formatted with custom number format “d” to only display the day of the date)
This process is repeated for each day of the specific month with the appropriate offset.
NOTE: Each mini-calendar month must have a different hard-coded number to compare the extracted month to the month being examined.
Calculating Holiday Dates
Some of the holidays are on dates that are the same every year, like Christmas and New Year’s Day. Others, like Easter and Mother’s Day, move to different dates each year.
These moving dates must be calculated.
We will use the DATE function to convert the calculated dates into proper dates.
Fixed Dates
For fixed dates, we will concatenate the day and month to the selected year. Using Christmas as an example, the formula would appear as:
=DATE(A3, 12, 25)The image below highlights the non-moving holidays.
Moving Dates
Moving date holidays, like Mother’s Day, are a bit trickier. The good news is, once we understand the logic of one, we need only make small adjustments to the formula for the remaining moving date holidays (except Easter, we’ll talk about that one next.)
The formula to calculate Mother’s day, which falls on the 2nd Sunday of May, appears as:
=DATE(A3, 5, 1) + 7 + CHOOSE(WEEKDAY(DATE(A3, 5, 1)), 0, 6, 5, 4, 3, 2, 1)This formula uses the following functions to calculate portions of the result:
- DATE – create the date 5/1/YYYY (May 1st of selected year)
- WEEKDAY – determine the weekday of the 1st of May for the selected year
- CHOOSE – select an offset number based on the calculated weekday number
Once these have been determined, the date of 5/1/YYYY has 7 days added to it (this offsets to a different week: 7, 14, or 21) with an additional value determined by the CHOOSE function to offset to a particular day of the week.
The remaining moving date holidays (except Easter) use the same logic but with different adjustments based on the month, the week iteration, and the weekday.
The image below highlights the non-moving holidays.
You can examine each of the moving date holiday calculations by clicking through each of the “Calculated Dates” in the Holidays table.
Calculating Easter’s Date for a Selected Year
The calculation of Easter is a bit trickier than any of the other moving date holidays.
The formula is as follows:
=FLOOR("5/" & DAY(MINUTE(A3/38) / 2 + 56) & "/" & A3, 7) – 34The formula uses the FLOOR, DAY, and MINUTE functions combined with some concatenation, division, and subtraction.
In all honesty, this is a formula that was created by the legendary Chip Pearson and has reached a level of Excel Zen that probably few possess.
Unfortunately, Chip passed away in April of 2018 and his site explaining this formula (and everything else related to Chip) has been taken down. If you can shed some light on the mathematical offsets in this formula, the remaining readers of this post will be most grateful.
There are a few websites that explain the thought behind calculating Easter’s date for any given year. You can read them at the following links:
Contextures (site run by Debra Dalgleish)
(the same formula on her site was supplied by Norbert Hetterich)
For now, I’ll just leave you with the formula which shouldn’t require any modifications to use in other workbooks.
Coloring the Dates Based on Status
The final piece of this puzzle is amongst simplest. The color-shading of the calendar dates based on status.
To recap, the calendar colors represent the following:
- days are paydays (green)
- days are paid holidays (orange)
- days are unpaid holidays (purple)
- days are personal appointments (aqua)
Each of these color rules applies to different areas of the worksheet. Selecting each “Applies to” field will highlight the affected areas.
In the following example, we see the selected cells for days that require green shading if deemed a pay date.
Each of these cells is compared to a list of Friday dates using a Duplicate Values rule to see if there is a duplicate among the list.
NOTE: The list of Friday pay dates is in a hidden column. If you unhide Column AN, you will see pay dates for the next 30-years. The dates are calculated from a fixed start date where each successive date has 14 days added to its date.
If there is a matching pay date in a calendar and the table of pay dates, these duplicate cells will be colored green. The table of pay dates is hidden to reduce the visual confusion of the worksheet.
Vacation Days (Paid and Unpaid) and Appointments
The same logic of selecting cells and performing a Duplicate Values check if run against the table of paid and unpaid holidays, and the table of appointments.
The same Conditional Format – Duplicate Values examination is performed on the mini-calendars and the cells holding appointments (aqua), unpaid holidays (purple), and paid holidays (orange).
Blank Cells
The final “white” conditional format performs a bit of color shenanigans.
Because some of the previous Conditional Format colors will apply logically to various empty cells in the calendar, the “white” conditional format checks to see if a cell is blank. If the cell is blank, a white fill is applied to the cell.
This gives the illusion of the cell being untouched, when in fact its incorrect color assignment is being masked with white.
If we were to remove the “white” rule, we can see the accidental application of color (aqua) from other rules.
Noted Month Holidays
This feature is the easiest to explain. Holidays that occur in each month are listed at the bottom of the mini-calendars via linked cells to the “Calculated Date” column of the Holidays table.
The displayed date has a custom number format of “m/dd” applied to suppress the year.
Download Your Excel Calendar
Feel free to Download the Excel Calendar HERE. (Note, this works if your regional settings are English and dates are input as MM/DD/YY). For the European version – where dates are input as DD/MM/YY – Please Download THIS VERSION.
Leila Gharani
I'm a 5x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
