Creating Dynamic Monthly Calendar in Excel (Interactive Calendar)

[@trumpexcel]

If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos. Also, I have made all of my Excel courses available for free. You can check these out using the below links: ✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/ ✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course ✅ Free VBA course - bit.ly/excel-vba-course ✅ Free Power Query Course - bit.ly/power-query-course

[@RogerBertrand]

I have written a few calendars using a different approach. I create a Pivot Table where the date is the COLUMN A and then to the right of it I make columns for any topic corresponding to that day, be it menu, activity , taks... whatever you like. Then I run an extract from that Table with DATE RANGE. It loads automatically in the calendar and the weekdays are identified from Monday to Sunday, using the DATE to convert it in the WEEKDAY. There can be as many rows for each days as there columns where information is itemized. Cheers.

[@shivsharma6982]

Hi thanks for this video...your all video are very good for understanding,well explained. One of my request please make a video for VBA in which we can work on text file using VBA.. I mean some unstructured data store In text file now I want to put this data in macro then automatically required data collected in Excel which is I required.

[@Jim-zm6fw]

Hello Sumit. Thank you for (another) great video, clear and to the point. A great introduction to the SEQUENCE function for me. I can already think of other ways to use this approach.

[@vksofficial7377]

I dont have office 365 so sequence formula i cant use.. what is another way to create this calender in excel 2013

[@vksofficial7377]

I dont have office 365 so sequence formula i cant use.. what is another way to create this calender in excel 2013

[@rhysborders4558]

I came into this video having absolutely no idea how to use excel outside of grouping information, and you explained it to the point where a novice like me could understand easily, thank you!

[@RogerBertrand]

EXCELLENT! Quite a nice way to use Excel. You could also in a cel under each row the LOOKUP value for that particular Holiday, so it could show the HOLIDAY NAME also. You can also universalize by adding a language selector from the drop down and your calendar would come up in any language that you want to use. I have introduced this method in all my Wb since I generate information in English, French and Spanish. You just need to flag the language you need and the whole calendar or report appears in that language.

[@wayneedmondson1065]

Great one Sumit! Thanks for demonstrating how to create this dynamic calendar. Thumbs up!!

[@wallrobertkal]

Excellent. Thanks for sharing. I suspected there was an easier way of acheiving this. We like to provide a calendar to our clients that list payrun dates, supplier pay dates, tax lodgement and pay dates and this will be a great time saver to create that calendar. Thanks again.

[@danielleshelbourne220]

Thank you so much for making this calendar a downloadable file, I really appreciate it. 😊

[@RaxLakhani]

You've made this incredibly easy to follow along. Thanks for the video!

[@Devad2007]

Another great video and well explained by showing step by step how each part of the formula is used and then putting it altogether

[@IvanCortinas_ES]

Awesome. I love the use of the SEQUENCE function inside the formula. Thank you Sumit Bansal!!!

[@trumpexcel]

Yeah, SEQUENCE function is amazing. It was a pain earlier to generate a sequence of numbers. I used to use ROW/ROWS and COLUMN/COLUMNS functions for it earlier, but that always made the formulas too complex

[@kokshong230]

Hi, I have an older version of Excel that does not have the SEQUENCE function, Sumit Bansal mentioned he would introduce it in the video...but I did not see it...or did I overlook it?

[@عبداللهمسعود-خ2ش]

Very informative video, I apricate your work.

[@cosmicslop1272]

Very good video. Nicely explained

[@trumpexcel]

Thanks... Glad you found the video useful :)

[@geearel]

Thank you so much for sharing!

[@slotguy777]

Great video! Please do a more in-depth video on the Sequence function. Thanks

[@nadermounir8228]

Thank you trump for this great video. This is very useful 👌

[@Curic-Ivan]

Great work! Keep doing the good work!

[@rebrahim81]

Excellent effort. Thank you

[@ahmedmokhtar190]

Really great work thanks

[@trumpexcel]

Thanks... Glad you liked the video :)

[@jordan676]

Can you use this concept to create a weekly schedule? Like have the weeks separated to add information(appointment times/descriptions) and change the drop downs to select weekly ranges?

[@justinlane3088]

Thank you for this video! Is there anyway to create this calendar but also make it fillable to add appointments or important events?

[@shamithasargunalan3080]

hi, thanks for the wonderful guide and for sharing I tried to create the calendar as guided, but not able to apply conditional formatting is not applying for the following months ..please advice?????

[@browniemushroom]

Thank you so much for this!!! Quick question! Is there a way to have the calendar updated with wkly 'to-dos'? Like every Monday to do task A, B, C, every Tuesday to do task D, E, and so on...

[@securitychannel1671]

Good job. Please, which office version do you use in this tutorial?

[@safetycrst826]

Great video! What if I have an event that spans multiple days? How would I make the calendar highlight those days without having to add an event for each day on the task list?

[@emilyruppel8738]

For the yearly calendar version, is there a way to format the month names so the year is beside the month name and have that year update with the rest of the days in the month when you change the main year?

[@LarsIngeHolen]

Very nice 👍 Can you show us how to make recurring intervals for a maintenance program? After job is done it should be saved in order to view history.

[@russnorcup]

excellent Sumit

[@alaabdullah1254]

Great and simple. What if holydays given as multi dates range, like from jan 13 to jan 16.

[@sweetaliena]

What would be the formula if we don't want to hide the dates but change the colour only? Great tutorial btw!

[@ortegar5]

Amazing video! Just subscribed! Quick question: what if there is an extra column between each day, how can I adjust the formula to work? I used the older formula in the description but because I have an extra column between each day, it retrieves every other day. Thank you!

[@corinnewilliams1323]

I don't see the other option if I don't have sequence formula

[@joesweeny3928]

How do I use this if I have rows and columns in between where I want to place the numbers? or can this only be done if the rows are in exact sequence?

[@mariahmaye1522]

Love this calendar works great! However, I tried to import it to google sheets and lost the conditional formatting for holidays. When I try to follow the same steps in sheets I get an invalid formula error in the conditional formatting window. Any suggestions?

[@Trippadipp]

Hello, If I have two events happening and they overlap on a day. How can I make that a different color, so I would be able to recognize the overlap?

[@benpratt9973]

Can you add wording to certain dates, ie like a diary.

[@nielporter3765]

Sumit, I very much enjoyed your tutorial on XLOOKUP and found it useful. During the video you alluded to a scenario where you may have two values that are the same in the data set (i.e. two different people with the same score in math for example) and how to deal with it using XLOOKUP (you indicated that this would be a subject of a different video). Have you produced the video yet? I'm dealing with a data set with this scenario. I can use XLOOKUP to identify the first occurrence and the last occurance (by searching last to first) but is there a way to fetch all of the records with my given criteria? I'd appreciate any insight you might have.

[@ankitagrawal7133]

Hi, i need quick help, i have over 200 unique items (we says controls). I want to make a effective plan which we are going to test over the years (March to December). There are 2 type of activitis. Can each item would be cover multiple times. COuld you pls help?

[@coolishujain]

Is there a way to display the name of holiday when we hover over the cell ?

[@mirrrvelll5164]

Very nice, but it could be even better if you can add todays day there, with maybe =NOW function, so that you have complete calendar.

[@artistartacademy3732]

Where is the formula in description

[@motivationwithflexstory]

excelent

[@yummymellon4058]

I have excel 2019 Tried using the older formula instead of sequence. But it doesn't work?

[@manishbhandari1636]

how to convert time like @ into ninteen-twenty word for now function in excel

[@sauravdas3658]

Missing the videos from you

[@asadkhanLoL]

Calendar inside formula can't see, not good and clear

[@renedanos8877]

How to make it so it starts on Sunday and ends on Saturday?

[@trumpexcel]

You can use the formula =WEEKDAY(DATE(2022,1,1),17), where if the result is 1, it would mean a Sunday, 2 would mean Monday and so on

[@redhaakhund1271]

👍👍👍👍👍

[@syedtanveerp]

Hi Unable to Open File After Downloaded Error is Coming as Can't Open File

[@trumpexcel]

Try this - www.dropbox.com/s/l8n2u4sjbmxca6h/Dynamic%20Monthly%20Calendar%20in%20Excel.xlsx?dl=1

[@mysteryrgo]

*** PRO TIP *** Good formula in the beginning, but you over-complicated it with the if section. Let's go back to your original formula: =DATE(B1,MATCH(B2,A6:A17,0),1)+SEQUENCE(6,7)-WEEKDAY(DATE(B1,MATCH(B2,A6:A17,0),1),2) That's it! Don't use the IF in the cell. Now add this as a NEW Conditional Formatting rule: =MONTH(D5)MONTH(DATE($B$1,MATCH($B$2,$A$6:$A$17,0),1)) Click on Format then Custom and change the value to ;;; This is MUCH cleaner than the final version in the if statement. I did not put the month names on a separate sheet. Modify MATCH($B$2,$A$6:$A$17,0) accordingly

[@thakkarsushma1718]

Hindi me video milega

[@yendlurivenkatrao1984]

We unable to see the formula bcz your vedio is less zoom..

[@trumpexcel]

I have also added a link to the download file in the description. Hope that will help

[@simonetrell614]

What a collosal waste of time. You can't edit this calendar or put text in it (you know, what the main functions of calendars are?) without it being a huge pain in the ass. Just use the Excel calendar templates.