In this tutorial you will learn how to create a dynamic annual calendar in Microsoft Excel. You can change the year and Excel will automatically update the calendar.
Chapters:
00:00 Intro
00:38 Display today's date
03:11 Display year's and days of the week
04:30 Year drop down list
06:44 Date function for 1/1 of selected year
07:38 Text function to manually determine what day of the week is 1/1
08:18 Weekday function for Excel to figure out what day of the week 1/1 falls on
10:09 Find all dates in selected year
14:12 Format every other odd month (1,3,5,7,9,11)
15:47 Format every other even month (2,4,6,8,10,12)
16:27 Format the first day of each month
18:15 Highlight today's date in the calendar
19:07 Spill over from previous or future years
21:47 Final formatting
22:22 Wrapup
FUNCTIONS AND FORMULAS
Get 1/1 date
=DATE(C3,1,1)
Determine what day of the week 1/1 falls on manually
=TEXT(C5,"DDDD")
Determine what day of the week 1/1 falls on automatically
=DATE(C3,1,1)WEEKDAY(DATE(C3,1,1),1)+1
Get each date in the selected year
=SEQUENCE(53,7,DATE(C3,1,1)WEEKDAY(DATE(C3,1,1),1)+1,1)
Determine odd months (1,3,5,7,9,11)
=ISODD(MONTH(A5))
Determine even months (2,4,6,8,10,12)
=ISEVEN(MONTH(A5))
Format the first day of each month
=AND(YEAR(A5)=$C$3,DAY(A5)=1)
Format today's date in the calendar
=A5=$A$1
Format any spill over from previous or future years
=YEAR(A5)**$C$3 the ** needs to be replaced with less than and greater than signs with no spaces. YouTube won't let me put those in the description.
Support me with your amazon purchases: https://melcompton.com/recommends/ama...
Check out these programs I use for YouTube
Bluehost discount: https://melcompton.com/recommends/blu...
Canva Pro Trial: https://melcompton.com/recommends/can...
Adobe: https://melcompton.com/recommends/adobe/
Filmora: https://melcompton.com/recommends/won...
Follow me on social media:
Facebook: / therealmelco. .
Twitter: @melissaecompton
Instagram: @melissaacompton
LinkedIn: / melissac. .
This description contains affiliate links and I may be paid a small commission should you purchase using these links.
#melissacompton #msexcel #calendar #excel