Perpetual Calendar Template

Holidays and Events: When you enter a date in the list of events within the yearly calendar, only the first 2 events per day will be listed in the monthly calendars.

Formatting: The dates are highlighted in the yearly calendar using Conditional Formatting to change the background color. If you want to manually format the dates, you can remove the conditional formatting (read about that in the Excel help system).

Creating a PDF: If you have a PDF print driver, you can select all the worksheet tabs at the same time, and then print the entire calendar to the PDF driver. Make sure you read the note below about sharing our calendar.

Moving Calendars to a Single Worksheet: Some people like to have all the monthly calendars on a single worksheet. That's easy to do, but make sure you cut and paste the calendars from the other worksheets (copying won't work).

Renaming Tabs: After you have chosen your starting month, you may want to rename the tabs to "Jan", "Feb", "Mar", etc.

Adding more Events - In the Year worksheet, the range $Z$10:$Z$44 is named events and the range $Y$10:$Y$44 is named event_dates. You can stretch these named ranges to include more events by inserting cells above Y44 and Z44. You may need to redefine the print area if you want to show all the events when you print the yearly calendar.

Due to the request of people wanting to add birthdays and other recurring annual events using formulas, you can now copy the formula below and replace month and day with the correct values.

For holidays, birthdays, and anniversaries occuring on a specific date like Christmas (month=12,day=25):

=DATE($A$4+IF($E$4>month,1,0),month,day)

For holidays occuring on a specific day of the week like "first Monday of September" (month=9,dayofweek=2,weekno=1) or "last Sunday in March" (month=4,dayofweek=1,weekno=0):

=( DATE($A$4+IF($E$4>month,1,0),month,1) + (weekno-1)*7) + dayofweek - WEEKDAY(DATE($A$4+IF($E$4>month,1,0),month,1),1) + IF(dayofweek<WEEKDAY(DATE($A$4+IF($E$4>month,1,0),month,1),1),7,0 )