Uploaded September 2024 | Updated November 2026, 4 days ago
An Excel calendar that rebuilds itself for any year you click to.
β¬οΈ Download the example file here and follow along: bit.ly/calendar24file
π©βπ« Learn Excel with my courses: bit.ly/calendar24courses
π¬ MORE FREE EXCEL TEMPLATES, full playlist here: bit.ly/templates26playlist
You'll need Microsoft 365 or Excel 2021 and later. The SEQUENCE function is what fills in the dates, and it doesn't exist in Excel 2019 or 2016.
Building it takes about ten minutes. Set the column widths, type your month and day labels, then write one formula that uses SEQUENCE, DATEVALUE and WEEKDAY to spill a six by seven grid of dates. A custom number format of "d" strips each date back to just the day number.
The rest is conditional formatting. A COUNTIF against your table of holidays colours those days brown, a second COUNTIF against your birthdays turns them blue, and TODAY highlights the current date in orange. One more rule uses MONTH to make the leftover days from the neighbouring months white, so they vanish.
A spin button from the Developer tab is linked to the cell holding the year. Click it and every month recalculates, which is why this works for any year instead of just the one you built it in. Add a birthday to the dates sheet and it appears in the calendar straight away.
The video also covers the Insert - Icons gallery for the season and gift markers, and how to start your week on Monday by changing the return_type argument in WEEKDAY from 1 to 2.
LEARN MORE
===========
π° EXCEL NEWSLETTER - join 450K+ subscribers here: myonlinetraininghub.com/excel-newsletter-sign-up
π― FOLLOW me on LinkedIn: linkedin.com/in/myndatreacy
π¬ EXCEL QUESTIONS: Get help on our Excel Forum: myonlinetraininghub.com/excel-forum
β² TIMESTAMPS
============
0:00 What this calendar does
0:49 Setting up the calendar grid
1:38 A spin button to change the year
2:23 Adding the title, months and day names
3:55 The formula that fills in all the dates
5:05 Showing just the day number
5:39 Highlighting your holidays and birthdays
7:12 Making today's date stand out
7:50 Hiding the days from other months
9:07 Adding icons for seasons and birthdays
11:03 Adding your own dates later
11:18 Starting your week on Monday instead
#Excel #DynamicCalendar #ExcelTips
An Excel calendar that rebuilds itself for any year you click to.
β¬οΈ Download the example file here and follow along: bit.ly/calendar24file
π©βπ« Learn Excel with my courses: bit.ly/calendar24courses
π¬ MORE FREE EXCEL TEMPLATES, full playlist here: bit.ly/templates26playlist
You'll need Microsoft 365 or Excel 2021 and later. The SEQUENCE function is what fills in the dates, and it doesn't exist in Excel 2019 or 2016.
Building it takes about ten minutes. Set the column widths, type your month and day labels, then write one formula that uses SEQUENCE, DATEVALUE and WEEKDAY to spill a six by seven grid of dates. A custom number format of "d" strips each date back to just the day number.
The rest is conditional formatting. A COUNTIF against your table of holidays colours those days brown, a second COUNTIF against your birthdays turns them blue, and TODAY highlights the current date in orange. One more rule uses MONTH to make the leftover days from the neighbouring months white, so they vanish.
A spin button from the Developer tab is linked to the cell holding the year. Click it and every month recalculates, which is why this works for any year instead of just the one you built it in. Add a birthday to the dates sheet and it appears in the calendar straight away.
The video also covers the Insert - Icons gallery for the season and gift markers, and how to start your week on Monday by changing the return_type argument in WEEKDAY from 1 to 2.
LEARN MORE
===========
π° EXCEL NEWSLETTER - join 450K+ subscribers here: myonlinetraininghub.com/excel-newsletter-sign-up
π― FOLLOW me on LinkedIn: linkedin.com/in/myndatreacy
π¬ EXCEL QUESTIONS: Get help on our Excel Forum: myonlinetraininghub.com/excel-forum
β² TIMESTAMPS
============
0:00 What this calendar does
0:49 Setting up the calendar grid
1:38 A spin button to change the year
2:23 Adding the title, months and day names
3:55 The formula that fills in all the dates
5:05 Showing just the day number
5:39 Highlighting your holidays and birthdays
7:12 Making today's date stand out
7:50 Hiding the days from other months
9:07 Adding icons for seasons and birthdays
11:03 Adding your own dates later
11:18 Starting your week on Monday instead
#Excel #DynamicCalendar #ExcelTips










