Excel Calendar Template Date Formulas Explained

There are many Excel Calendar templates available and if you look at them closely, you’ll see they have some mind-boggling date formulas.

Adam, one of our members, sent me in a calendar he has been using for 10+ years and asked if I could explain how some of the formulas worked. The original source of the calendar appears to be a Microsoft Template (I use a similar one), but Adam has enhanced it by adding a table for holiday dates that conditionally format the calendar cells with a fill colour.

The calendar allows you to choose the year in cell A3 which updates the calendar and holiday dates accordingly:

Download Workbook

Download the Excel file and follow along:

Calendar Formulas

There are 3 main formulas that generate the calendar:

Formula 1: Month Sundays

There are 12 named formulas (JanSun1, FebSun1 etc.)  that find the first day of each month, minus the weekday number of that date, plus 1. You can see them in the name manager:

September and December are the only two months in 2019 where the first of the month is a Sunday. Let’s look at the SepSun1 named formula more closely:

=DATE('Any Year Calendar'!$A$3,9,1) - WEEKDAY(DATE('Any Year Calendar'!$A$3,9,1))+1

Which evaluates as follows:

=DATE(2019,9,1) - WEEKDAY(DATE(2019,9,1)) +1

=September 1, 2019 - WEEKDAY(September 1, 2019) +1

=September 1, 2019 - 1 + 1

=September 1, 2019

English Translation

Remember, cell A3 contains the year, which currently has 2019 selected. Therefore, the DATE function simply returns the date; September 1, 2019. The WEEKDAY function returns a 1 if the date returned by DATE is a Sunday, whereas if the day is Monday it will return a 2 and so on. September 1, 2019 is a Sunday. +1 is then added to the result.

If we take January’s named formula, JanSun1 as another example we get the following:

=DATE('Any Year Calendar'!$A$3,1,1) - WEEKDAY(DATE('Any Year Calendar'!$A$3,1,1)) +1

=DATE(2019,1,1) - WEEKDAY(DATE(2019,1,1)) +1

=January 1, 2019 - WEEKDAY(January 1, 2019) +1

=January 1, 2019 - 3 +1

=December 30, 2018

January 1 is a Tuesday, which is the third day in the week according to the WEEKDAY function.

Formula 2: Calendar Formulas

Let’s set those named formulas aside for a moment and look at the next formula in the calendar cells. We’ll call these the Calendar Formulas.

Looking at cell C6 in the image below you can see the calendar formula uses the IF function and references the JanSun1 named formula.

The objective of these formulas is to determine what day of the week each date falls on. Starting in the first cell (C6) it tests if JanSun1 returns the first of the month, if true it would return the result of JanSun1, otherwise it returns a blank as denoted by "":

Let’s step through the formula:

=IF(AND(YEAR(JanSun1) = $A$3, MONTH(JanSun1) = 1), JanSun1, "")

Looking at the JanSun1 named formula first, it evaluates as follows:

=DATE('Any Year Calendar'!$A$3,1,1) - WEEKDAY(DATE('Any Year Calendar'!$A$3,1,1)) +1

=January 1, 2019 - WEEKDAY(January 1, 2019) +1

=January 1, 2019 - 3 +1

=December 30, 2018

When we insert the JanSun1 result into the IF formula we get:

=IF(AND(YEAR(December 30, 2018) = $A$3, MONTH(December 30, 2018) = 1), December 30, 2018, "")

Which evaluates as follows:

=IF(AND(2018 = 2019, 12 = 1), December 30, 2018, "")

=IF(AND(FALSE, FALSE), December 30, 2018, "")

="" i.e. blank

And we can see that a blank has been returned in the calendar image above.

Looking at the next cell, D6 for Monday, the formula is:

=IF(AND(YEAR(JanSun1+1)=$A$3,MONTH(JanSun1+1)=1),JanSun1+1, "")

Notice the formula is the same as the formula in cell C6 except it has +1 added to the JanSun1 result, as shown in the formula bar of the image below:

Let’s step through how it evaluates:

=IF(AND(YEAR(December 30,2018+1)=$A$3,MONTH(December 30,2018+1)=1), December 30,2018+1, "")

Note: The +1 adds one day to the date, as shown below.

=IF(AND(YEAR(December 31,2018)=$A$3,MONTH(December 31,2018)=1), December 31,2018, "")

=IF(AND(2018=2019, 12=1), December 31,2018, "")

=IF(AND(FALSE, FALSE), December 31,2018, "")

=""

And lastly, let’s look at the formula in cell E6 for Tuesday:

=IF(AND(YEAR(JanSun1+2)=$A$3,MONTH(JanSun1+2)=1),JanSun1+2, "")

Notice the formula is now adding 2 days to the result of JanSun1, as shown in the image below:

It evaluates like so:

=IF(AND(YEAR(December 30,2018+2)=$A$3,MONTH(December 30,2018+2)=1), December 30,2018+2, "")

=IF(AND(YEAR(January 1,2019)=$A$3,MONTH(January 1,2019)=1), January 1,2019, "")

=IF(AND(2019=2019, 1=1), January 1,2019, "")

=IF(AND(TRUE, TRUE), January 1,2019, "")

= January 1,2019

Finally, we have a date that returns TRUE for the year and month logical tests.

Tip: The date returned by the formula has a custom number format applied that only displays the day portion of the date:

The formula in each subsequent cell has an extra day added to the JanSun1 result. For example, cell F6 for Wednesday contains this formula:

=IF(AND(YEAR(JanSun1+3)=$A$3,MONTH(JanSun1+3)=1),JanSun1+3, "")

If you inspect the formulas in the cells for the other months, you’ll see they follow the same pattern and use a named formula specific to that month. For example, here is February’s formula in cell C16:

=IF(AND(YEAR(FebSun1)=$A$3,MONTH(FebSun1)=2),FebSun1, "")

Tip: The Month Sundays named formulas (JanSun1, FebSun1 etc.) are used to simplify the IF formulas, but they could just as easily be inserted in the IF formulas instead e.g. this formula (with line breaks so it’s easier to see the pattern):

=IF(
AND(
YEAR(
JanSun1) = $A$3,
MONTH(
JanSun1) = 1),
JanSun1, "")

Is the same as this formula:

=IF(
AND(
YEAR(
DATE('Any Year Calendar'!$A$3,1,1) - WEEKDAY(DATE('Any Year Calendar'!$A$3,1,1)) +1) = $A$3, MONTH(
DATE('Any Year Calendar'!$A$3,1,1) - WEEKDAY(DATE('Any Year Calendar'!$A$3,1,1)) +1) = 1), DATE('Any Year Calendar'!$A$3,1,1) - WEEKDAY(DATE('Any Year Calendar'!$A$3,1,1)) +1, "")

I think you’ll agree it’s more succinct to use the named formulas. Named formulas can also yield efficiency improvements, although it’s not an issue in this file.

Formula 3: Finding Holiday Dates

The other clever formulas in this file derive the holiday dates, many of which fall on different days depending on the year. You’ll see them in column AK of the HolidayDates table in the file.

If we take Mother’s Day as an example, in some countries it always falls on the second Sunday in May. To find the date of the second Sunday in May in any give year, you can use this formula:

=DATE(A3,5,1)+7+CHOOSE(WEEKDAY(DATE(A3,5,1)),0,6,5,4,3,2,1)

Where cell A3 contains the year, which is currently set to 2019.

It evaluates like so:

=DATE(A3,5,1)+7+CHOOSE(WEEKDAY(DATE(A3,5,1)),0,6,5,4,3,2,1)

=May 1, 2019 +7+CHOOSE(WEEKDAY(May 1, 2019),0,6,5,4,3,2,1)

=May 8, 2019 +CHOOSE(4,0,6,5,4,3,2,1)

=May 8, 2019 + 4

=May 12, 2019

The logic here is that the second Sunday will be at least 7 days after the first of the month, hence the +7 in the formula.

WEEKDAY then tells us what day number the first of the month falls on. In this case it’s a Wednesday, which is the 4^th day of the week. We pass this value to the CHOOSE function, which returns 4 i.e. the 4^th item in the list {0,6,5,4,3,2,1}. i.e. Sunday is 4 days after Wednesday, which takes it to May 12.

The image below illustrates the steps taken in the formula:

Conditional Formatting

The Holiday Dates in column AK are then used in Conditional Formatting rules to automatically set fill colour in the calendar cells where they are the same (duplicates) as the dates in the ‘HolidayDates’ table.

Thanks

A big thank you to Adam for sharing the calendar file and inspiration for this post.