# 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.

## 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.

## 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.

### Office Blogs Comments

• I found that the formula falls over when using a different date locale. Over here in the UK we use the "dd/mmm/yyyy" format and this means that all the Names listed are working on January, so that for April, DATEVALUE("4/1/"&TheYear) would equate to 4th Jan of TheYear.

I believe this can be solved by replacing:

DATEVALUE("4/1/"&TheYear)

with:

VALUE(""&DATE(TheYear,4,1))

so that:

=DATEVALUE("4/1/"&TheYear)-WEEKDAY(DATEVALUE("4/1/"&TheYear))+1

becomes:

=VALUE(""&DATE(TheYear,4,1))-WEEKDAY(VALUE(""&DATE(TheYear,4,1)))+1

I have included extra functions in the above as I originally evaluated this in a cell. In the Name definition ths formula could reduce to:

DATE(TheYear,4,1))-WEEKDAY(DATE(TheYear,4,1))+1

as the each cell formula should be able to work with the date as either a date or a numeric value.

I think this should always work as the DATE() function should be independant of date locale regarding the position of the arguments.

I haven't fully tested the above, so if there are any mistakes, my apologies!

Sean

• There are lots of ways to create a calendar. I prefer a method that uses a single multi-cell array formula to display a month. Named formulas are not required. I also created named styles so the user can easily change the look of the calendar elements.

I describe my perpetual calendar workbook here:

• I don't have Excel 2007... is there something comparable for Excel 2003?

Thanks!

• (I'm posting this on behalf of Mark Gillis who is having some trouble with his blog account)

Sean,

Thanks for pointing this out and providing a solution. I'll look at it and update the calendar to make it more "locale-agnostic".

John,

Yes there are different ways of doing a perpetual calendar and yours is a great solution. One of my goals was to showcase the new name manager feature and explain a mega-formula in a more digestible way to the uninitiated.

Joan,

No plans for a 2003 version. However, I recommend downloading a free trial of 2007 ( See

us20.trymicrosoftoffice.com/default.aspx ) or buy it a great price ( See office.microsoft.com/.../FX102798291033.aspx ).

• I have downloaded your perpetual calendar and it works great. I have one question, I am guessing the JanSun1 function is a product of Excel 2007, but I am using 2003 and it is still working, is there a special add-in or something like that which allows you to use that function in 2003?

Thanks

SVANO

• SVANO,

JanSun1 is a named formula. You can use these when running this calendar in Excel 2003, after having converted the 2007 format to 97-2003 format. There's a slight loss of fidelity, bit the named formulas still work.

Mark

• For anyone else who may be trying to use this in a region where date format is dd/mm/yyy (as opposed to mm/dd/yyyy) - i got this calendar to work for me by editing the formaule of the named ranges [mmm]Sun1 (i.e. JanSun1 - DecJan1) so that the hard-coded part of the date is in your local format.

e.g. taking DecSun1 as an example:

in the formula: =DATEVALUE("12/1/"&TheYear)-WEEKDAY(DATEVALUE("12/1/"&TheYear))+1

change all instancves of "12/1/"  to "1/12/".

One question for you Joseph - how did you get the cloud fill effect? (I know how to do it for a chart, but it seems as though a different method was used here?)

Cheers

brz

• Brzak,

I used a background image on the worksheet:

Page Layout | Page Setup | Background | Sheet Background dialog box

The picture is standard on Vista in the Pictures folder.

Mark