How to Create a Perpetual Yearly Calendar in Excel
Today's author: Mark Gillis is an Excel writer. He was born October 3, 1952. Does his birthday fall on the same weekday as your birthday?
Do you have nagging, unanswered questions floating around in the back of your mind like: "What day of the week does the date, January 23, 4589 fall on?", "What's the date of the eighth Thursday of 1922?", and "Is 9000 a leap year?".
If you're like me, the answer is clearly no. Nevertheless, there's a remote chance that someday a question like this will arise and you won't have the answer at your finger tips. If and when that day comes, you will either need a friend with a photographic memory or a perpetual yearly calendar. If your friends have other talents, you'll need to dig up a calendar of this type. To save you the trouble, I've written one for you that uses Excel and here's how it works.
Download the perpetual yearly calendar
An overview of the problem
As most of you probably know, Excel's date calculation engine handles a time span between the years 1900 and 9999 by using a serial number to calculate dates within that time span. January 1, 1900 is a value of 1 and Dec. 31 9999 is a value of 2,958,465. Excel's date formatting features, formula calculation abilities, and built-in date and time functions give you a wonderful foundation when it comes to creating a perpetual calendar.
Many yearly calendars are formatted by showing the twelve months of the year in a grid pattern of three by four for the months, with each month further displayed as a "month matrix" of six rows and seven columns, with the month and day of the week as column headers.
As you can see, the month name and day of the week are fixed in a pattern that works with any month of any year, so that part is easy when it comes to creating a perpetual yearly calendar. And we already know what a valid range of years is for the calendar, so that's a simple matter of adding data validation to ensure a correct year.
All we need to do is fill in the blanks, where appropriate, for each year. That's all…
The most pressing challenge when creating a perpetual yearly calendar is to create a formula or set of formulas that can calculate the correct date number for each possible position in the calendar, and only display the date if it falls within the current month matrix. The issue is the same for each month and year, although the position of each date varies from month to month and year to year. Here are the extreme cases that you would need to handle in the formula for each "month matrix":
|
The "first" falls on a Sunday |
The "first" falls on a Saturday |
|
S |
M |
T |
W |
T |
F |
S |
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
|
8 |
9 |
10 |
11 |
12 |
13 |
14 |
|
15 |
16 |
17 |
18 |
19 |
20 |
21 |
|
22 |
23 |
24 |
25 |
26 |
27 |
28 |
|
29 |
30 |
31 |
|
|
|
|
| |
|
|
|
|
|
|
|
|
S |
M |
T |
W |
T |
F |
S |
| |
|
|
|
|
|
1 |
|
2 |
3 |
4 |
5 |
6 |
7 |
8 |
|
9 |
10 |
11 |
12 |
13 |
14 |
15 |
|
16 |
17 |
18 |
19 |
20 |
21 |
22 |
|
23 |
24 |
25 |
26 |
27 |
28 |
29 |
|
30 |
31 |
|
|
|
|
|
|
Dates at the end of each month are not complicated to handle. When the month of the date changes to the following month, don't display it.
Dates at the beginning of each month are complicated to handle. If only the first day of every month began on a Sunday. But the calendar gods -- from assorted mythologies -- Janus (Roman), Thor (Norse), the Sun god (Egyptian), others who I dare not offend -- have conspired against us and so we have to figure that out for ourselves.
The first of any month can fall on any day of the week. The first Sunday of any month matrix can be any one of seven different dates. Therein lies the tricky part of creating a formula that can handle the vagaries of time. To find the date of the first Sunday of any month matrix, you need to find the date of the first of the month, subtract the day of the week number, and then add 1. When you subtract the day of the week number, you end up with the last date of the previous month, so that's why it's necessary to add 1. Here are the seven possibilities for any month, using January as an example:
|
+1 |
|
-1 |
-2 |
-3 |
-4 |
-5 |
-6 |
-7 |
|
S |
M |
T |
W |
T |
F |
S |
| |
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
| |
|
31 |
1 |
2 |
3 |
4 |
5 |
6 |
| |
|
30 |
31 |
1 |
2 |
3 |
4 |
5 |
| |
|
29 |
30 |
31 |
1 |
2 |
3 |
4 |
| |
|
28 |
29 |
30 |
31 |
1 |
2 |
3 |
| |
|
27 |
28 |
29 |
30 |
31 |
1 |
2 |
| |
|
26 |
27 |
28 |
29 |
30 |
31 |
1 |
Let's summarize the problem that the formula calculation must solve. For the years 1900 to 9999, in each position in a calendar grid, calculate the correct date number (from 1 to 31) and display that number, but only within the month matrix that the date occurs. For the dates of the previous month that fall within a month matrix (in row one), don't display them, and for the dates of the following month that fall within the month matrix (in row five or six), don't display them either.
The solution
If a picture is worth a thousand words, then a demo is worth a million. It's time for a little show and tell. Watch the following demo to see how the perpetual calendar works and how the formulas and names are used to calculate the correct dates.
Watch this: How the perpetual yearly calendar works
Summing up
Some final observations. Without stating the obvious, this calendar doesn't work properly without automatic calculation set. I mention this only because it's easy to forget that you might turn it off for whatever reason. Also, the calendar handles the possibility that the workbook might be set to the 1904 date system by using conditional formatting. Although I could have used error handling in the formula, that would have made the formula more difficult to read.
And yes, the rumors are true, there were years before 1900 and there hopefully will be years after 10,000. This calendar doesn't handle them. You are welcome to try. Time is on your side.
