Create a Dynamic Calendar in Excel For Any Month in Any Year

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

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

50% 1

In this tutorial you'll learn how to create a very useful Dynamic Calendar in Excel: For any Month in Any Year. You'll be able to switch from one month to another by using 2 drop list, and have formatting update on the fly. You'll learn about Dates and Date Math in Excel. Along the way we'll use VLOOKUP & IF functions as well.
You can download the Start and Finished version of this Project to follow along and then do the activity on your own, by clicking on the link:
officeinstructor.com/public/

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

9 окт 2018

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

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

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

@davidc8717 3 года назад

So many terrible videos on RU-vid. It was really nice watching your video, so clear and knowledgeable. Thanks for posting this!!!

@Officeinstructor 3 года назад

You're very welcome David, Here is a more recent video on creating a calendar with Power Query, don't miss it ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-GK29EBbyjXo.html Hope you subscribe to my channel.

@wliu330 4 года назад

What a brilliant video, this has helped me create my own version of a fiscal year calendar that I can reuse in the future!! Thank you!

@nickylove5158 3 года назад

I love your style of training. Direct and so clear. Also, you have great taste in formatting.

@seesterran3822 4 года назад

Wow. Wow. Wow. You are an Excel-lent teacher! You explained very quickly and didn’t miss a step. (But, I’m so thankful that I was able to pause and rewind.) I love how you also taught keyboard shortcuts as you went. I wish I could give more than one thumbs up. 👍👍👍👍👍

@Officeinstructor 3 года назад

Thank you Rhonda

@cwjr1968 4 года назад

Thank you very much for the video! I was able to use the information to build a dynamic training calendar for my employer. Great information and very clearly explained.

@agisz1559 5 лет назад

This is the really well explained tutorial. Thank you. I hope to see more.

@sanjayTECH04 4 года назад

Sir ! You are the best Excel teacher in Entire RU-vid channel..

@Officeinstructor 4 года назад

Great you liked it Show your support to my channel by sharing the video and watching my other videos

@tushya11 3 года назад

THANK YOU SO MUCH, You can't believe how long I've been doing this manually, I really appreciate your style of tutoring as well, concise and to the point.

@Officeinstructor 3 года назад

You're welcome! I have another tutorial about creating a Dynamic Calendar using Power Query. Check it out on my channel

@danielgergely3853 5 лет назад

Wow!! Now that is a good tutorial. I have seen hundreds before, but this one is in the top 5 in my opinion. He not only explains what to type but also explains the logic behind it. Awesome job!!

@Officeinstructor 5 лет назад

Thank you so much for the nice comment. I invite you to watch my latest tutorial by clicking on the link: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-jEr3uj1ljQ8.html

@milo-1001 3 года назад

Was searching exactly for this. Never imagined that someone would have all of it in a single video, what I really needed. That too with such details. Thank you Nabil. You are a good teacher.

@Officeinstructor 3 года назад

Glad I could help!

@simonbello6185 2 года назад

God bless, I am fulfilled creating this Calendar by myself on your instructions. You are good!

@dalsheel 4 года назад

Best calendar creation tutorial on youtube. Tyvm!

@Officeinstructor 4 года назад

Wow, thank you!

@pravuchaudhary3904 4 года назад

You really are a nice teacher!! No words to describe.

@michaelvernonsing7228 4 года назад

Great Explanation! Thanks!

@mat7739 3 года назад

Brillaint Video. I could create a calendar in less than 20 minutes. You are a great tutor

@Officeinstructor 3 года назад

Glad it was helpful!

@vanawang8967 4 года назад

An incredible video, thanks Mr. Mourad for this detailed tutorial.

@Officeinstructor 4 года назад

You're welcome Vana

@newtoytube7399 5 лет назад

Really I was enjoyed, no boring and clearly understood. Thank you so much.☺

@3abersabeel357 4 года назад

One of the best channels on the RU-vid!

@Officeinstructor 4 года назад

I truly appreciate your comment

@Sattaar5El 5 лет назад

Thank you Mr. Mourad! I was thinking about doing this myself for years for our meal calendars. What a comprehensive video tutorial! I am keeping it, and sharing.

@Officeinstructor 5 лет назад

Thank you Miranda... Don't miss my new Tutorials... Since you mentioned "Meals" then watch the Pizza Tutorial

@Sattaar5El 5 лет назад

@@Officeinstructor Will do!

@Up4Excel Год назад

Great stuff 👍 Packed full of great tips and formulas

@Officeinstructor Год назад

Great to hear!

@explorgrande6511 5 лет назад

This is another useful tool that displays the Excel genius of Nabil.

@Officeinstructor 5 лет назад

Thank you

@stevennovella378 2 года назад

Thank you for the information. Excellent job.

@dianamadrid671 2 года назад

I loved your tutorial! It was very well explained and very easy to understand, exactly what I was looking for!

@mohamedfarah1939 5 лет назад

Oh great, thank you for your good explanation, this is well prepared video.

@hankgrimes1806 5 лет назад

This is one of the best videos I've seen. Clear explanations and formulas that WORK! Excellent job!

@Officeinstructor 5 лет назад

Stay tuned... The best is yet to come

@andregroenewald2852 2 года назад

Your tutoring is such a blessing. Thank you, Sir

@sasavienne 5 лет назад

Great, useful and nicely explained.. 🌟 🌟 🌟 Thanks a lot. 👍 🌟

@anillpatel 5 лет назад

Excellent video. Very useful. Thank you.

@deepakpatro4346 2 года назад

Great explanation, never thought it could be done this way...simple use of your own formula ...thanks a lot 😊😊😊

@umairzubairsahiwal 3 года назад

Its a very informative and detailed presentation on making a calendar. I appreciate the Teacher efforts.

@Officeinstructor 3 года назад

Glad you found it useful

@BenkiVasu777 3 года назад

Sir, what a fabulous way to teach and express ,not many have this . Thanks a Ton

@Officeinstructor 3 года назад

Thanks and welcome

@1gopalakrishnarao 5 лет назад

Thanks a lot. Excellent to my Excel Teacher.

@melcomc19 5 лет назад

Very clear and logical explanation, look forward to more videos especially Excel with words and Outlook, if possible.

@Officeinstructor 5 лет назад

Thank you... Did you watch my latest videos? awaiting your comments on them

@matthiasskulason7070 5 лет назад

Thank you Mr. Mourad. Very clear explanation. This is a perfect video :-)

@Officeinstructor 5 лет назад

Thank you ... Did you watch my latest videos? would love to hear from you Matthias

@binsalimnationalenterprise9106 4 года назад

Very useful function and easy to mind explanation.. تحية لك من عُمان بلاد الأمن والأمان..

@Officeinstructor 4 года назад

Thank you for the nice comment... Please share with your friends on Social Media

@ibekwechidithompson4853 Год назад

I love this training video, wanting more, thanks

@edkent6872 4 года назад

This is great and informative. Thank you, very much!

@Officeinstructor 3 года назад

Thank You

@sandeepsingh9910 3 года назад

Excellent pace of teaching with relevant visual content

@Officeinstructor 3 года назад

Glad you liked it. Did you watch the newer version created by Power Query? Here it is: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-GK29EBbyjXo.html

@jonchalk3855 2 года назад

Thank you for this tutorial. I made a small change. I just changed the date font size and the cells (conditional formatting) so that the grey in the previous and next month dates be in a lighter shade of grey and the numbers in those cells be light grey (that is a darker shade of grey than the cell colour). I made the changes to accommodate the dates to be seen by visually impaired people (aka: large print).

@mitchellswint847 2 года назад

Really great presentation.

@zuhairalmutawa9941 2 года назад

Excellent tutorial, Thank you Mr. Nabil.

@felipemtrevelin 3 года назад

Great work man. Works perfectly

@joyandgroove 2 года назад

Namasté and Great Day! This was a fantastic tutorial! You rock!

@alexmakini173 4 года назад

This is just beautiful. thank you so much

@Officeinstructor 4 года назад

You are so welcome!

@KNLP23 5 лет назад

Brilliant explanation, thanks!

@Officeinstructor 5 лет назад

Thank you for the motivating comment. Please share on your Social Media

@Luciano_mp 5 лет назад

Thank you!

@KarimBenabd 5 лет назад

Thank you Sir !

@sandipmakwana6462 4 года назад

Super very easy technik sir by you...

@Officeinstructor 4 года назад

Thanks and welcome

@theodoreblumberg9022 3 года назад

Very good, nicely done.

@Officeinstructor 3 года назад

Thank you very much!

@Jchriswalker71 5 лет назад

Very well done thank you for sharing.

@Officeinstructor 5 лет назад

Glad you liked it

@ashishkumartripathi2788 4 года назад

Very nicely and easy way to teach with login behind all formula used and objectivity....Great Sir..

@Officeinstructor 4 года назад

Thanks and welcome

@johnborg6005 5 лет назад

Very helpful and informative. Thanks Nabil.

@Officeinstructor 5 лет назад

Thank you for the encouraging feedback

@NareshKumar-tk5gp 4 года назад

Superb...sir.. Thank you very much.

@Officeinstructor 4 года назад

Most welcome

@coolag74 4 года назад

Fantabulous...

@sonarfleet2375 2 года назад

Easy to do with a good teacher. 🎉 congrats. It’s just missing make a complete calendar, with all months.

@r.i.4259 5 лет назад

Top man!

@jerry171460 4 года назад

Good video. After I watched this video, I subscribed the channel. Hopefully, all other videos also offer a lot of tips.

@Officeinstructor 4 года назад

Great welcome to the OfficeInstructor Channel The Best Is Yet to come

@yes4me145 4 года назад

Thanks a lot Sir.

@diraviams9717 3 года назад

It is marvellous,

@Officeinstructor 3 года назад

Thank you for the encouraging comment. By the way there is a more recent video for creating a calendar with Power Query, I encourage you to watch it. here is the link: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-GK29EBbyjXo.html

@pinoyapollo9637 Год назад

Thank you I made mine.

@carlantaya175 3 года назад

Fantastic tutorial.

@Officeinstructor 3 года назад

Thank You

@everwelwisher 3 года назад

very useful and great idea sir .. thanks you very much

@jawadtcs 5 лет назад

Such a nice tutorial.

@Officeinstructor 5 лет назад

Thank you Jawad. I'm glad you liked it. Share it with your friends. Did you watch my other tutorials?

@18minato 3 года назад

Thank you for the video.. very helpful !! Add a one more function weeknum 🤗

@Officeinstructor 3 года назад

Thanks for watching

@mausie1967 2 года назад

Great video

@santoshmore8016 5 лет назад

Very nice video for learning

@jaipalrana. 4 года назад

superb knowledge !!!!!!

@Officeinstructor 4 года назад

Thank you

@yuanhildreth3433 4 года назад

Clear instructions, I learned a lot from this video. Thank you very much!

@Officeinstructor 4 года назад

Stay tuned... The best is yet to come

@SUR-NI-VARA 5 лет назад

Nicely explained, Thank you Mr.Nabil, please also show how can we add week to be displayed against the date

@KamranMumtazAhmed 3 года назад

Just add a colum before the date and type this formula =WEEKNUM(CELL_REFERENCE)

@duelena09 2 года назад

Thank you so much for this great tutorial. Very useful. Do you have a tutorial on how to add events on the calendar automatically?

@HARPREETSINGH-nh4fv 5 лет назад

Thanks sir its really helpful.

@Officeinstructor 5 лет назад

I'm glad you liked it

@hajjialy2413 5 лет назад

VERY NICE... SHUKRAN ..MOURAD

@Officeinstructor 5 лет назад

Glad you liked it

@majidsiddique8227 4 года назад

Execellent

@mahendransomu6177 4 года назад

nice video

@abdoutawfik5668 Год назад

Verry good

@mahaboobhossain5076 4 года назад

excellent video

@Officeinstructor 4 года назад

Glad you liked it! here is the file: 1drv.ms/x/s!AkP-Crku8UuBuHZiSiS4Aw5ZmdhI?e=Rlj4Ek

@Udaykaujiya 4 года назад

Very super video

@Officeinstructor 4 года назад

Thank you

@MikeDaily 3 года назад

Just watched and created the calendar per your video. I also downloaded your calendar. Down side is that February does not have 31 days. In fact all months have 31 days. Yet on screen with your video, it appears you added a step to make it work right but never corrected your download. Did I miss something?

@sunilkotyan4443 5 лет назад

Thanks alot sir

@Officeinstructor 5 лет назад

You're welcome Sunil

@thisaintsomethingelse.this165 3 года назад

Thank you for your fantastic video. Great explanation throughout. Only problem I have is with the formula at the end in Conditional Formatting. I enter the formula as in the video but it always returns an error message stating "There's a problem with this formula. " I can't understand why this is/any ideas? Thanks again!

@richardhay645 2 года назад

To have number in D1 make the list for the month data validation 1 through 12 and provide the month name by custom number formatting.

@tuncaygungor833 5 лет назад

This is a perfect video. But I have a questions. For example, I want to select any cell. How i know "which choosed"

@bhwmxeeb1101 5 лет назад

Well explain Sir! So my question is: How do you duplicate this to a 12-Months Calendar year with other events link to the cell in the calendar and other Conditional Formatting rules such as Holidays on the calendar of each month? Can you do a video about that if possible. Thanks in advance.

@ocean99a 4 года назад

Explanation is very great,,, thanks a lotSir .... I have a question please I have a monthly horizontal calendar, there are months with 31, 30 or 29 ... days. How to hide blank cells completely when changing month. Thank you

@Officeinstructor 4 года назад

You may need to use an EOMONTH function

@bestlearnings4265 10 месяцев назад

Nice

@davehansel9715 Год назад

Very comprehensive tutorial. I use a dynamic calendar template that is ready to use that I downloaded from someka. But this one is also nice. How can I get this one to download? Many thanks

@jeromee.8542 4 года назад

Hello, is there anyway to include below column with a comment? thank you.

@m.c.4458 5 лет назад

great

@CryptoIncursion 2 года назад

Hello! Thank you for the video but I have a question. I am attempting to do this in a 5 or 6 day format, for example Monday - Friday, or Monday through Saturday. Could you explain how I could alter the formula's to accommodate this? It has to be very simple I am just missing it. I suppose I could just create the Sunday and ignore it or hide it. Thanks again for the tutorial! Excel is so flexible.

@sathyanarayanansatagopan9069 2 года назад

Thank You, Sir! Please explain how to create a date or calemdar picker in MS Excel.

@gerardvaneggermond2067 Год назад

Hello sir, I tried it myself and I succeeded, thank you, now a question if I want to have and show only Tuesday and Thursday, how do I do it?

@zeekanow9536 4 года назад

Excellent video. Dates and Vlookup comes in real handy!!. In your tutorial your date in K1 is formatted as day, month, year and with dashes. 01-06-20. How did you do that? All I came up with is month,day, year and with /. 06/01/20 Oh, and I downloaded your tutorial to use while watching the video. Everything went great up until I used the Start Label. I spent a couple of hours fooling with it. Your Finished spreadsheet also uses Start as a Label. So you can't use it elsewhere in the workbook. Every time I tried using it in my spread sheet, I ended up in the Finished one. Anyway, thanks for the tutorial. I really learned a lot!!

@Officeinstructor 4 года назад

CTRL + SHIFT + 3

@zeekanow9536 4 года назад

Hmm. I'm using Excel 2013 and CTRL + SHIFT + 3 does not do anything. Could it be a version thing? @@Officeinstructor

@zeekanow9536 4 года назад

Correction. I end up with 1-Jun-20. Sorry bout that.

@sinamen516 4 года назад

Thank you!! Is there a way to add appointments or activities? Do you have a video on it??

@Officeinstructor 4 года назад

Not yet!

@matthiasskulason7070 5 лет назад

I´m not an Excel expert. I have already created the Calendar You explained so well in this video. Now I'm trying to show national holidays in the calendar. I tied to use conditional forwarding rules to show national holiday like I have done before but this dose not seems to work in this case. Is it possible to show national holidays in this type of calendar ?

@matthiasskulason7070 5 лет назад

I have found solution for this using COUNTIF fuction and then create new conditional forwarding rule

@1gopalakrishnarao 5 лет назад

Please publish how to monitor the loan accounts, sanctioned on different dates, at different rates of interest, for a different period.

@barodiintaada8161 4 года назад

iam watching aug 2020 what a coincidence

@Officeinstructor 3 года назад

Hope you enjoy it

@SolomonKinyanjui_sk 4 года назад

Nice Dynamic Calendar. How can I automate the highlighting of important days like holidays,birthdays and the active day?

@Officeinstructor 4 года назад

That's all conditional Formatting

@SolomonKinyanjui_sk 4 года назад

@@Officeinstructor Thank you sir. How? I tried conditional formatting for active date it didn't work.

@SolomonKinyanjui_sk 4 года назад

Well I got it for active day and for holidays I entered the date number in the conditional formatting.

@santhoshs2736 4 года назад

How do you color cell value based on the value of pivot table

@Officeinstructor 4 года назад

That's conditional Formatting

@waleedaljuhaishe7591 5 лет назад

It’s very useful but I have a problem how to add information to the date Let say I need to do a training each Monday so how am going to add that to the dynamic sheet?

@Officeinstructor 5 лет назад

Hi Waleed, I understand your concern but I cannot add TWO different contents (Typing Text + Having a dynamic Formula) in the same exact cell. So to make it look like an Outlook Calendar that requires a whole different setup. I invite you to watch my latest tutorial "Valentine's Chart" and looking forward to reading your comments...Salam

@waleedaljuhaishe7591 5 лет назад

Officeinstructor thanks can you help me as am trying to do a training for 019 each Monday and Thursday

@Officeinstructor 5 лет назад

So why don't you use Conditional formatting for these 2 days? However if you want to have text corresponding to each day in the dynamic calendar, Then after doing everything as you learned in my tutorial, add a New worksheet and you need to adjust column widths and Row heights in a way that you 3 cells appearing as one (2 columns & 2 rows. The cell in the upper right = a cell in the dynamic calendar in the same relative position, while the remaining 3 cells (one in the top row & 2 in the next row will be all merged together and you get a Dynamic calendar with Outlook functionality. I can't explain more in a comment... May be I create a video on that in the future.

@waleedaljuhaishe7591 5 лет назад

Officeinstructor thanks but am lost, ;( can I get your email and email you the thing that I want done so you can help me, please.