How to Create a Dynamic Annual Calendar in Microsoft Excel

Подписаться 6 тыс.

Просмотров 24 тыс.

50% 1

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 - Wrap-up
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. RU-vid won't let me put those in the description.
Support me with your amazon purchases: melcompton.com/recommends/ama...
Check out these programs I use for RU-vid
Bluehost discount: melcompton.com/recommends/blu...
Canva Pro Trial: melcompton.com/recommends/can...
Adobe: melcompton.com/recommends/adobe/
Filmora: melcompton.com/recommends/won...
Follow me on social media:
Facebook: / therealmelco. .
Twitter: @melissaecompton
Instagram: @melissaacompton
LinkedIn: / melissa-c. .
This description contains affiliate links and I may be paid a small commission should you purchase using these links.
#melissacompton #msexcel #calendar #excel

Хобби

Опубликовано:

5 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 14

@jillianrisse 25 дней назад

This was a super helpful tutorial. Thank you Melissa. Any suggestions on how to add/track important dates in this annual dynamic calendar - based on a separate tab list (holidays, PTO, task names on due dates, etc). I'm not able to add in rows in between/under each week to bring in key notes on specific dates.

@NelsonFranks-xk1jw 4 месяца назад

Very nice and thorough example. I'm trying to make a calendar, but below the day, I would like to have a row where I could display data for that day calculated from another sheet. Are you aware of any way to skip a row using the SEQUENCE function? Thanks!

@biggc49042 4 месяца назад

I am trying to build a calendar that is just for the current month and it switch based on the drop down I choose. I was able to set up a Today, Year and Month drop downs. Think your typical daily calendar with squares. I would like to change the drop down and the dates dynamically change on the headers of the calendar.

@markowen8725 4 месяца назад

Thanks for the Tutorial, I am using an old version of EXCEL(2007 to be precise) so i don't have a sequence option but I did find a work around by manually typing the first 3 rows of dates and dragging them down. My question is can each month be separated into it's own titled box

@aldomema6939 3 месяца назад

This was great - I am now looking to link my daily data into this. any particular help for that?

@phillips2976 Год назад

It sure would be nice if we could download a copy of the final spreadsheet.

@Luran07 7 месяцев назад

Nice guide. Is there any way to apply the sequence command, if I want to have weekdays presented in rows, instead of columns? Example: First day is in A1 and I need day 2 to be in A2, instead of B1, and so forth.

@zero310578 6 месяцев назад

With this Worksheet Im trying to create a shift rota for 3 Shifts, how to you create rotational patterns using conditional formatting or is that not gonna work?