In this video I demonstrate how to create a dynamic monthly calendar in Excel 365 or Excel Online. The calendar uses the following functions DATEVALUE, WEEKDAY and SEQUENCE. Using conditional formatting the calendar highlights bank holidays and the current date.
Click here to download the featured file.
Video Table of Contents
00:00 Introduction
00:22 Create the drop-downs for month and year
01:58 Use DATEVALUE to return the first day of the selected month
03:15 Use WEEKDAY to return the weekday number of the first day of the selected month
03:58 Use SEQUENCE to populate the calendar with dates
06:40 Combine all formulas into a single formula
07:53 Format the calendar
08:55 Grey out dates that don’t belong to the selected month
11:28 Display bank holidays in a different colour
14:01 Highlight the current date