Creating a festival and birthday calendar:

 
We start by first creating a selection menu for the dates.
Select cell C2 and click on the “Data” tab in the “Ribbon” and select “Data Validation”.
In the “Data Validation” dialog box, select the “List” option in the “Allow” text box(1) and, in the ” Source ” text box, type the years 2011 to 2020 consecutively, separated by semicolons, for eg.,(2) and click OK.

Our menu is in cell C2.

Take the data of the cells B2 to B18 from the image below, click “Merge and center” for the cells B2 and C2 , format the cells C2 to C19, C22 and C23 with the custom format code, i.e, dddd d mmmm.

In cell C5, we’re going to display the day of the New Year with the help of the formula, = DATE.
The function DATE (year, month, day) has 3 parameters, namely: year, month and day.
We take “year” as a parameter for the contents of cell C2.
For the month, we take 1(First month of the year).
And, for the day also, we take 1 (First day of the month).
The formula is = DATE (C2, 1,1).

In cell C6, we calculate the date for Easter.
Easter is not on the same day every year, except that it is on Sunday but, it is the first Sunday following the full moon, after the beginning of spring.  There are numerous written formulas for calculating Easter, ranging from 54 characters to more than 250 characters, where we use the shortest formula, of course.

=FIXED ((“4 /” &date) / 7 + MOD (MOD (date, 19) * 19-7, 30) * 14%, 0) * 7-6.
We replace the date with the cell C2, because we need to specify the year.
The formula for cell C6 is then
=FIXED ((“4 /” & C2) / 7 + MOD (MOD (C2, 19) * 19-7, 30) * 14%, 0) * 7-6

Cel C7 (2nd day of Easter): =C6+1
Cell C8 (Labour Day): = DATE (C2, 5, 1)
Cell C9 (Ascension Day): =C6 + 39
Cell C10 (Pentecost): =C6 + 49
Cell C11 (2nd Day of Pentecost): =C10 + 1
Cell C12 (National Holiday U.S.): = DATE (C2, 7, 21)
Cell C13 (Assumption): = DATE (C2, 8, 15)
Cell C14 (All Saints Day): = DATE (C2, 11, 1)
Cell C15 (All Souls Day): = C14 + 1
Cell C16 (Armistice): = DATE (C2, 11, 11)
Cell C17 (Christmas): = DATE (C2, 12, 25)
Cell C18 (Boxing Day): =C17 + 1
Cell C19 (Last day): = DATE (C2, 12, 31)

For Cell C22 (Daylight Saving Time, which begins on the last Sunday in March) we’ll use the Date and Weekday functions.
= DATE (C2, 3, 31) -WEEKDAY (DATE (C2, 3, 31)) +1.
This formula works as follows:
In this example, the content of cell C2 is = DATE (C2, 3, 31), which is Thursday, March 31, 2011(The last day of March).
WEEKDAY (DATE (C2, 3, 31)): the function WEEKDAY, assigns a number from 1-7 for the days of the week, Sunday = 1 Monday = 2 etc., so WEEKDAY (Thursday, March 31, 2011) is 5.
= DATE (C2, 3, 31) -WEEKDAY (DATE (C2, 3, 31)) +1 is Thursday, March 31 minus 5 days, which is = Saturday, March 26, 2011 plus 1 which is = Sunday, March 27 (the last Sunday in March).
Cell C23 (Early winter)= DATE (C2, 10, 31) -WEEKDAY (DATE (C2, 10, 31)) +1.

Of course, you can extend this list for birthdays and other special occasions.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats