• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Calendar Template Date Formulas Explained

You are here: Home / Excel Formulas / Excel Calendar Template Date Formulas Explained
calendar_template_formulas_thumb
June 25, 2019 by Mynda Treacy

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:

any year calendar

Download Workbook

Download the Excel file and follow along:

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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:

month sundays

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 "":

calendar formulas

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:

JanSun1+1

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:

JanSun1+2

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:

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:

finding holiday dates 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.

calendar_template_formulas_thumb

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel Formulas
Previous Post:Excel Freeze Panes vs Split PanesExcel Freeze Panes vs Split Panes
Next Post:Custom Data Types in VBAcustom data types in vba

Reader Interactions

Comments

  1. Adrianne Meldrum

    January 29, 2023 at 4:52 pm

    Does Formula 2 work with Google Sheets?

    Reply
    • Mynda Treacy

      January 29, 2023 at 7:49 pm

      Not sure. I don’t use Google Sheets. You’d have to give it a go.

      Reply
  2. Marcus

    September 11, 2021 at 7:17 am

    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

    Reply
    • Mynda Treacy

      September 11, 2021 at 8:51 pm

      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

      Reply
  3. xavi

    August 24, 2021 at 11:50 pm

    Es posible hacer que el lunes sea el primer dia de la semana

    Reply
    • Mynda Treacy

      August 25, 2021 at 3:03 pm

      It wouldn’t be straight forward to change this calendar to start on Monday. You’d probably need to rebuild it.

      Reply
  4. Colleen Poole

    March 15, 2021 at 11:33 am

    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?

    Reply
    • Philip Treacy

      March 15, 2021 at 9:32 pm

      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

      Reply
  5. Katja

    October 9, 2020 at 8:27 pm

    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?

    Reply
    • Mynda Treacy

      October 10, 2020 at 1:59 pm

      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

      Reply
  6. David Mary

    May 22, 2020 at 2:20 pm

    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!

    Reply
    • Philip Treacy

      May 22, 2020 at 7:08 pm

      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

      Reply
  7. skip bryczek

    December 7, 2019 at 3:46 am

    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

    Reply
    • Mynda Treacy

      December 7, 2019 at 1:46 pm

      Hi Skip,

      There’s no easy way to change the template so that Saturday is the first day of the week, sorry.

      Mynda

      Reply
  8. Derek

    October 29, 2019 at 2:41 am

    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

    Reply
    • Mynda Treacy

      October 29, 2019 at 8:42 am

      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

      Reply
  9. Choua Yang

    June 27, 2019 at 3:21 am

    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?

    Reply
    • Mynda Treacy

      June 27, 2019 at 5:37 am

      Hi Choua,

      Not even the great Excel minds like John Walkenbach can explain how that Easter formula calculates.

      Mynda

      Reply
  10. Marcos Correia

    June 26, 2019 at 10:39 pm

    Great tool and explanation, my congratulation for this post, I’m sure that it will be very handy to many of the readers

    Reply
    • Mynda Treacy

      June 27, 2019 at 5:31 am

      Great to know you found it useful 🙂

      Reply
  11. Stefan Johansson

    June 26, 2019 at 10:52 am

    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

    Reply
    • Catalin Bombea

      June 27, 2019 at 1:31 pm

      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.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.