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:
Enter your email address below to download the sample workbook.
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 4th day of the week. We pass this value to the CHOOSE function, which returns 4 i.e. the 4th 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.
Adrianne Meldrum
Does Formula 2 work with Google Sheets?
Mynda Treacy
Not sure. I don’t use Google Sheets. You’d have to give it a go.
Marcus
I have a problem understanding the formula in section one. So you say =DATE(Year, Month number, day number)-WEEKDAY(DATE(Year, Month number, day number))+1. All this does is give me a date 5 days earlier than the date I entered as opposed to to the day of the week for that date. It seems like the sticking point is around the WEEKDAY function, but what do i know – other than this doesn’t work the way it clearly has for you
Mynda Treacy
Hi Marcus,
The purpose of this formula is to find the date that occupies the first cell of the calendar. Most months it’s the previous month’s Sunday, with the exception of September and December 2019, which is the 1st of the current month.
It’s designed to have the 1st of each month entered into the formula, not any date you choose to test it with. Hope that clarifies things.
Mynda
xavi
Es posible hacer que el lunes sea el primer dia de la semana
Mynda Treacy
It wouldn’t be straight forward to change this calendar to start on Monday. You’d probably need to rebuild it.
Colleen Poole
I NEED A CALANDER IN MY CLASSROOM THAT WILL SHOW WHAT SPECIALS MY KIDS GO TO USING A 6 DAY ROTATION (A-F)- STARTING APRIL 6 – MAY 26. HOW DO I DO THAT?
Philip Treacy
Hi Colleen,
You’ll need to provide more information so we can understand what it is you need.
Please start a topic in our forum and explain the issue in detail there.
Regards
Phil
Katja
Very nice template that I would like to use to show recurrent meetings. However, on some dates there are 2 or even 3 meetings. I have 1 color for every meeting and in case of e.g. 2 meetings, would like to show a mix of the 2 colors. How to amend the conditional formating to show multiple events on the same day?
Mynda Treacy
Hi Katja,
You can’t show multiple colours in a single cell. The best you could do is either apply a different format, e.g. font and or border colour. Or you need to spread the dates over 3 cells so there are multiple cells to set the fill colour for.
Mynda
David Mary
I have two problems to solve, that have proven beyond my abilities.
How could I code the date for the Feast of St. Matthias, when it is the 24 of February, except during leap years when it is the 25th?
How could I code the date for the First Sunday in Advent? It’s always the fourth Sunday before Christmas Day, but I can’t figure out how to code it for each year.
Thanks!
Philip Treacy
Hi David,
To calculate the Feast of St Matthias you just need to know if it’s a leap year and then it’s either 24 or 25, so this formulas will do that
=DATE($A$3,2,IF(OR(MOD($A$3,400)=0,AND(MOD($A$3,4)=0,MOD($A$3,100)<>0)),25, 24))
and this formula will work out what day of the week Christmas day is and then calculate 4 Sundays back
=IF(WEEKDAY(AK19)=1,AK19-28,AK19-28+(7-WEEKDAY(AK19)+1))
Regards
Phil
skip bryczek
This is a great calendar very impressive for a middle excel user. How would you change this so that Saturday is the 1st day of the week
Skip Bryczek
Mynda Treacy
Hi Skip,
There’s no easy way to change the template so that Saturday is the first day of the week, sorry.
Mynda
Derek
Adam,
How do you pick a date of a month, example the 10th of each month. Like to make appointment for that date, to reoccur the 10th of each month
Mynda Treacy
Hi Derek,
Excel doesn’t have this functionality built in. You might be able to use the Conditional Formatting technique to colour cells where =DAY(date)=10
Mynda
Choua Yang
Wow – this is most impressive! Much kudos to Adam! With this template in hand, I know enough about Excel to add other holidays (Good Friday), to add that to either the March or April calendar where the holidays are listed underneath the dates based on which month it falls within (and move Easter accordingly when it falls in March), and update my conditional formatting because some of my paid holidays are different. However, I would have never been able to come up with what’s in C4:AG31 – never in a million years! But I must ask – what is the logic behind the formula for Easter? I can step through the formula and understand what it’s doing and when, but I can’t understand WHY. With the western Easter falling the first Sunday after the first spring full moon, I can’t understand how that formula is derived from this information. I double checked and the Easter dates are accurate to at least 2039. Are you able to share some insight?
Mynda Treacy
Hi Choua,
Not even the great Excel minds like John Walkenbach can explain how that Easter formula calculates.
Mynda
Marcos Correia
Great tool and explanation, my congratulation for this post, I’m sure that it will be very handy to many of the readers
Mynda Treacy
Great to know you found it useful 🙂
Stefan Johansson
Yet another great post Mynda, thank you!
Please see if you also have this error in your Excel app:
The Gregorian calendar was introduced in 1582, and according to it the years 1600, 2000, 2400 etc. should be leap years, but not 1700, 1800, 1900, 2100 etc.
Nevertheless, my Excel 365 for Mac says that February 29 and March 1, year 1600 AD, is the same weekday, meaning that the 1600 leap year is not recognised.
Could you please check this?
Best regards,
Stefan
Catalin Bombea
Hi Stefan,
Day 1 in Excel is 01/01/1900, unless you choose the option from Excel options to : “Use 1904 date system”.
This means that excel will not be able to calculate and display dates before 1900, not sure what you’re Mac is saying, it should not say anything.