Create a Dynamic Monthly Calendar in Excel 365 or Online

No video

Create a Dynamic Monthly Calendar in Excel 365 or Online | Dynamic Calendar Excel Formula

Рет қаралды 42,824

Күн бұрын

Download the featured file here: www.bluepecant...
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.
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
------------------------

Пікірлер: 26

@v.lo_ Ай бұрын

mr tugwell you really saved my life with this one 😭 thank you so much!!

@BecBec2015 Жыл бұрын

thank you! ive watched so many calendar videos and yours is the only one that made actual sense!!! THANK YOU

@ChesterTugwell Жыл бұрын

Glad it was helpful!

@cajinguy218 Жыл бұрын

Chester… can’t give you more than 1 thumbs up and I can’t subscribe more than once. If I could, I WOULD!! LOVE LOVE LOVE your videos.

@ChesterTugwell Жыл бұрын

Very generous!

@abbaslokhandwala8445 9 ай бұрын

Thanks a ton Chester for your efforts and easy and clear english and logic

@patconvey2687 5 ай бұрын

Really useful tutorial, your explanation was excellently delivered, it must have been as it worked first time for me. I also wondered if the calendar you had on the right used similar formatting but I did further wonder about the directional up and down function to the right of the month field.

@front_spice Жыл бұрын

This one is perfect and simple to understand

@IvanCortinas_ES Жыл бұрын

Another exceptional explanation. Complicated concepts explained in a way that is understood. Great Chester!!!

@colinknaggs7440 6 ай бұрын

Very interesting and informative. Thank you. My question is: How can I make the cells which are not part of the month return a blank? Not use cell formatting as that just hides the value.

@rogerwillis7692 9 ай бұрын

Fantastic! Thanks for the information!

@karlhazzard2593 4 ай бұрын

cracking video, really made sense. one question, how do I write in the date box? do I need to put it on the sheet list and then add it like the bank holidays??

@soumiamoujane3695 5 ай бұрын

My conditional formatting for highlighting weekends and holidays and noe work

@giocordero3263 7 ай бұрын

Thank you!!!

@user-js7js6ny3k Ай бұрын

Hello.How to calculate working hours per month in dynamic calendar

@Frank-si2jd 8 ай бұрын

Great tutorial! Slightly rhetorical question; Is there a way to integrate this calendar on top of an existing spreadsheet with its own widths of the columns and heights of the rows, without changing these original column widths and row heights? I would thinks something like an insert on top of the existing spreadsheet and the insert stays in place on that position.

@dandelion1164 8 ай бұрын

great tutorial! thanks! is there a possibility to add weeks as well?

@adnanabbasi9482 Жыл бұрын

Good one!👏👍

@wayneedmondson1065 Жыл бұрын

Great! Thanks Chester!!

@anyandeveryistaken Жыл бұрын

Superb.

@arunkumarjain9476 11 ай бұрын

Super

@barksennui8255 Жыл бұрын

How can I use this dynamic calendar and make it populate when I click on a cell to then select a date to then print that into the selected cell?

@eduardo_yuzo Жыл бұрын

How can i do the same without the SEQUENCE formula? My office is the professional plus 2019.

@ladydaeli6801 Жыл бұрын

i tried this in spreadsheet, but i can't apply the holiday formatting. any suggestion? spreadsheet conditional formatting keep saying "the formula is not valid"