Create Easy Yearly Calendar in Excel and Sheets with a SINGLE Formula

No video

Create Easy Yearly Calendar in Excel and Sheets with a SINGLE Formula

Рет қаралды 1,104,130

Leila Gharani

Күн бұрын

Пікірлер: 742

@LeilaGharani Жыл бұрын

Grab the file I used in the video from here 👉 pages.xelplus.com/dynamic-calendar-file

@elvisosei9766 3 жыл бұрын

Anyone ever told you.... "You r the best teacher on the globe?" Keep up the good work. Really appreciate all your efforts.

@LeilaGharani 3 жыл бұрын

That's really kind! Thank you for watching and commenting.

@somesh2345 3 жыл бұрын

Liela create a channel on VBA..🙏

@jeffdea5278 2 жыл бұрын

Your channel is the first and only place I go for Excel help. Everything is so clear and concise. Stuff that used to take me hours to figure out takes ten minutes now. You're a hero in my book!

@gim190 2 жыл бұрын

I've learnt more about date management in excel in these 10 minutes than in 10 years of no-brain use of it. Thanks!

@wrexxy49 3 жыл бұрын

I’ve never learned from someone who explains themselves so clearly. Thank you Leila!!

@LeilaGharani 3 жыл бұрын

Glad it was helpful! Thank you.

@sawaimalhi8439 3 жыл бұрын

I have some questions about excel and power bi what is carrier level of excel and power bi ..What is level required to got job on these two technology's iam already 1 year experience in excel but I idea what I can do for got job in Karachi Pakistan or others country please suggestions me leela or any one

@somesh2345 3 жыл бұрын

Liela create a channel on VBA..🙏

@navjotsingh2251 3 жыл бұрын

Leila, you deserve all the money in the world for helping so many people!!

@gracemark5995 3 жыл бұрын

this is incredible . i never knew that excel can have a calendar with just a formula o.my.god thank you so much leila . you put a lot of effort on these tutorials proud of you . wow 👍👍

@LeilaGharani 3 жыл бұрын

Glad you like it, Mark!

@gracemark5995 3 жыл бұрын

@@LeilaGharani i'm of couse leila

@larslooperman6400 3 жыл бұрын

Oh my. Your beautiful brain is solving so many problems that I use to solve by excruciating sessions trying to learn special skills in Excel.

@selianboy8508 2 жыл бұрын

I have soooooo much to learn in life... and only fewer than about 30 years to learn it in! Every time I watch your videos and other such on You Tube, I realise that I know nothing! Many thanks...

@leonardoggy Жыл бұрын

Instead of hardcoding 53, use WEEKNUM(END_OF_YEAR)-WEEKNUM(START_OF_YEAR)+1 in the first SEQUENCE parameter. It will give the correct rows (53 or 54) for the year. The same concept can be applied for a single month, where you can use START_OF_MONTH and END_OF_MONTH to get the correct rows.

@winxcelbliss Жыл бұрын

Sir, is the Harcoding of 53 Wrong?

@tabbycat6802 Жыл бұрын

@@winxcelbliss Try to output the year 2028... in this case (a leap year starts on saturday) you need 54 weeks.

@blanchsb Жыл бұрын

I like this idea but I'm not an excel fashionista and couldn't figure out how to incorporate the START_OF_YEAR or END_OF_YEAR (not sure what to assign those to), so I just hardcoded 54.

@leonardoggy 7 ай бұрын

@@blanchsb Sorry, I never got a notification that someone had replied to my comment. I also tried posting a longer reply, but it seems like KZfaq "ate" it. :/ Let's see if I can redo my comment. START_OF_YEAR is DATE($D$2, 1, 1). I am using $D$2 as following the example of the video. That cell contains the year of the calendar. END_OF_YEAR is DATE($D$2, 12, 31). The complete SEQUENCE formula becomes thus: =SEQUENCE(WEEKNUM(DATE($D$2, 12, 31)) - WEEKNUM(DATE($D$2, 1, 1)) + 1, 7, DATE($D$2, 1, 1) - WEEKDAY(DATE($D$2, 1, 1) + 1, 1) If you use LET(), you can write it as: =LET(dStartOfYear, DATE($D$2, 1, 1), dEndOfYear, DATE($D$2, 12, 31), SEQUENCE(WEEKNUM(dEndOfYear) - WEEKNUM(dStartOfYear) + 1, 7, dStartOfYear - WEEKDAY(dStartOfYear) + 1, 1)) I hope this helps.

@andrewmoss6449 3 жыл бұрын

Yeah, it’s amazing how much you can do in one cell now. With the addition of the LET function as well, you can do a lot more with a lot less.

@LeilaGharani 3 жыл бұрын

Very true Andrew....

@joshuadooley181 2 жыл бұрын

I am going to start binge watching your Excel and PowerPoint videos! You are awesome! Thank you!

@theosalt1 2 жыл бұрын

It is just so simple to understand after playing it over 20 times slowly. Thank you.

@ramakrishnabalijepalli3721 3 жыл бұрын

This is simply a great way to create a calendar which was frustrating till now. Your presentation is awesome. Thank you so much!

@LeilaGharani 3 жыл бұрын

Most welcome 😊

@ManojKumar-sk3fw 3 жыл бұрын

Thanks for this channel. I recently started learning excel in.my old age. And I am sure I will soon become pro in excel by seeing your channel tips and sessions on excel. This is helping me a lot in excelling in my job. Thanks for becoming an idol for old age learning students who somewhere struggle with new generation on these new tricks.. AGAIN THANK YOU VERY MUCH FROM THE CORE OF MY HEART..YOUR SUGGESTIONS ARE HELPING ME IN EARNING MONEY..

@mytwocents9974 3 жыл бұрын

I added two features. =B5:H57 < TODAY() Strikethrough - all past dates =B5:H57 = TODAY() Font Color Red - Makes today's date Red As always you give me great ideas.

@jacquiambrose 3 жыл бұрын

I am a super novice...but managed to do the calendar following the steps closely. Please may I know where in the formula to add your formula for this extra feature please?

@talibhussainraja7767 2 жыл бұрын

@@jacquiambrose conditional formatting

@edgy1rocks 2 жыл бұрын

OMG, I'm an old guy and this was like drinking from a fire hydrant! It really helped when I updated my version of MS Office! This stuff is fantastic! Thanks so much!

@gerardlunow567 3 жыл бұрын

Why does anyone give it is thumb's down. I should stop by once a week. I needed this calendar. Thank you!

@LeilaGharani 3 жыл бұрын

Thanks for stopping by now!

@Oprimaita 3 жыл бұрын

Whenever I watch your video, whenever I learn something new. Thanks a lot Leila.

@LeilaGharani 3 жыл бұрын

Happy to hear that!

@bobjunior3421 Жыл бұрын

I have been watching many excel help videos, your teaching is the most clear and understandable! I really appreciated! From now on, I will keep on watching your videos! Love it!

@yoliedepaz3340 3 жыл бұрын

Your voice is very soothing and pleasant to the ears! You’re a very good teacher and trainer. Wow! More power! 👏👏👏

@yoliedepaz3340 3 жыл бұрын

Hi Leila, do you have videos which may be helpful in monitoring stocks in excel? Thanks a lot in advance.

@grantstemmet606 Жыл бұрын

You explain very clear and at pace anyone can keep up with, thank you from a Newbie.

@jeceva09 3 жыл бұрын

I always thought it was very complicated to make an excel calendar. But you showed in this video that there is a very simple and easy way to create the calendar. Thank you Leila.

@LeilaGharani 3 жыл бұрын

You're very welcome, Jean!

@shizmosaic 2 жыл бұрын

You change my life on a weekly basis!

@rollingpanda4198 3 жыл бұрын

WOW! This is awesome! So many calendars I've made over the years that were a lot more manual. Thank you!

@LeilaGharani 3 жыл бұрын

You are so welcome!

@63Hash 2 жыл бұрын

This is brilliant, Thanks... They say "imitation is the highest form of flattery", so I used your ideas here to create a single month view where the user inputs the year but also selects the month (in a separate cell) using a simple form spinner set from 1 to 12. I also used conditional formatting to get it to place a border around / make bold today's date. I'm very pleases with the result

@notorioussamurai1671 7 ай бұрын

WOW! This seems like what I am looking for, could you guide me if I can do weekly planning of resources of vehicles like this? So the user could click between weeks but still input information mainly in current week?

@AP-eb8hd 3 жыл бұрын

What I really like about this & other video of yours is that the learning one gets. More than the main concepts there are minor things e.g. conditional formating nuances etc.. in this one. Very well done !!

@LeilaGharani 3 жыл бұрын

Awesome, thank you!

@BetterSheets 3 жыл бұрын

I never heard of the Sequence function until a week ago and now I see it everywhere. Its sooooo useful.

@LeilaGharani 3 жыл бұрын

It really is :)

@RZA12 8 ай бұрын

You're the best Leila! Thank you for teaching us in a great way 🙏

@tonyalas2103 2 жыл бұрын

I am humbled by your knowledge and motivated to learn more. Thank you!

@ashishmishra2246 3 жыл бұрын

Stumbled upon this video today. It didn't take much time after watching the video to subscribe. Thanks for this so interesting and informative video.

@TylerStanczak 3 жыл бұрын

Love how this is all done with one formula and that gif of the girl frustrated is awesome!😂

@LeilaGharani 3 жыл бұрын

😁

@angufibodavid4031 2 жыл бұрын

Thanks a lot. May you live longer. I have learned a new trick from you and I'm going to try it with my students coz I'm an IT Trainer at Saipali Institute of Technology and Management here in Uganda.

@richardralph 2 жыл бұрын

Genius. Such brilliantly constructed videos too.

@dlep9221 3 жыл бұрын

Excel is a tool very powerful and you are a very powerful teacher :-)

@ppst817 Жыл бұрын

Thank you very much. I learn a lot from your tutorials.

@kgbadariprasad1 3 жыл бұрын

Awesome, Awesome, Awesome. Your videos demonstrate platinum standards of simplicity !!

@LeilaGharani 3 жыл бұрын

Thank you very much!

@winxcelbliss Жыл бұрын

Leila Ma'am you are brilliant...

@uncgoalie 3 жыл бұрын

Wow, I just discovered your videos and already watched 4 today - super helpful and extremely well done! Thank you!

@ehteshamshah 2 жыл бұрын

Teaching Level is Excellent

@Co-Co1 2 жыл бұрын

I'm lucky to find you today on KZfaq, thanks for all your beautiful videos, you are amazing, wow! Hello to you from Australia

@LeilaGharani 2 жыл бұрын

Wow, thank you!

@rahulsosonawane 3 жыл бұрын

Brilliant Trick! Many Thanks Wish you be always Healthy & Happy:)

@wayneedmondson1065 3 жыл бұрын

Hi Leila. Fun example.. very clever. I used =ISODD(MONTH(B5)) for the conditional formatting instead of MOD. Thanks for sharing. Thumbs up!!

@LeilaGharani 3 жыл бұрын

Great alternative. Thanks for sharing, Wayne!

@CampfireEditor 3 жыл бұрын

@@LeilaGharani How did you do this? My version of 365 does not have capability to create New rule.

@Mymusicfmw33 3 жыл бұрын

Wish I found your videos a lonnng time ago. I’ll be retiring soon but always want learn. Your video are great for everyone-non power user and power user alike. Stay well.

@krn14242 3 жыл бұрын

Love it Leila. What a difference from creating calendars dynamically in the past. Thanks for the great video as always.

@LeilaGharani 3 жыл бұрын

Thanks so much! 😊

@ExcellentDude 3 жыл бұрын

Thanks! Really great information as usual - added this calendar hack to my bag of tricks.

@LeilaGharani 3 жыл бұрын

Great! Hope it'll come in handy.

@amaher79 2 жыл бұрын

Leila your videos are truly great and are helping me a lot. You make everything super easy.... A big thank you

@anandgajjar1618 2 жыл бұрын

Truly you are amazing tutor

@danh.4380 3 жыл бұрын

Why is this woman not the leader of the world! Thank you for all your hard work to bring us such wonderful videos.

@HenrikTham 3 жыл бұрын

Very instructive! Had some troubles adding a "week number" column, very common in Sweden. Looked very easy until I realized the complications of the ISO-standard most countries use. First week is not always 1, could be 52 or 53. Came up with following code: (NB! we use semicolon instead of comma in all formulas...) US version: SEQUENCE(54;;WEEKNUM(DATE(y;1;1))) ISO version: IF(WEEKNUM(DATE(y;1;1))=ISOWEEKNUM(DATE(y;1;1));SEQUENCE(54;;WEEKNUM(DATE(y;1;1)));SEQUENCE(54;;WEEKNUM(DATE(y;1;1))-1)) Added conditional format to hide the zero (couldn't come up with a smart solution to add 52 or 53)

@Caboladk 2 жыл бұрын

thanks for the formula, I had thought that I wanted to add weeknumber in a column too

@marcpi8381 7 ай бұрын

This was actually more of a conditional formatting discovery for me!

@LeilaGharani 7 ай бұрын

Glad you found something useful :)

@mohamedaboobackersiddique7322 3 жыл бұрын

Thanks! Btw i dont click thumbs up button during the end of video,because i always first hit thumbs up button and i watch.i m confident that your videos never dissappoint.

@LeilaGharani 3 жыл бұрын

Thank you for your trust Mohamed.

@manuelquintino3822 3 жыл бұрын

Excelent Teacher Ms Leila. I am a Teacher too and i would love to be as good as you. With your simple explanation i made a automated sheet that each monthly took +- 30min to configure. Now I only need less them 10 sec. Wonderfull!...

@LeilaGharani 3 жыл бұрын

Wow, thank you Manuel!

@micheldevost 3 жыл бұрын

The money shot is at 9:33. Very cool!

@nathanbowers1761 3 жыл бұрын

Nicely done, Leila! Easy to follow, logical, and articulatley presented. Thank you.

@LeilaGharani 3 жыл бұрын

Glad you enjoyed it, Nathan!

@jamesstubbins3510 2 жыл бұрын

Great video it works, I have added Week No. by entering in cell I5 =WEEKNUM(F5,21) then paste in to column

@super69man 3 жыл бұрын

Brilliant. Every day I learn a new things from u. You are best teacher. Keep it up. Best luck 🤞

@LeilaGharani 3 жыл бұрын

Thank you so much 😀

@alcxander 2 жыл бұрын

every time the videos teach me something! good work!

@mahmoudam 3 жыл бұрын

as usual you teaching me a lot, thank you so much.

@LeilaGharani 3 жыл бұрын

My pleasure!

@meseahunt 2 жыл бұрын

thanks Leila, smart info from a beautiful teacher!!

@signofshinejobs6391 3 жыл бұрын

Excellent Great Teacher in KZfaq

@venkatiyer7459 3 жыл бұрын

Amazing .. watch any number of times ... your learning skill is excellent ... a big thank you again

@knowledgeseeker97 Жыл бұрын

Thanks Leila . I got an idea to prepare the report significant

@LeilaGharani Жыл бұрын

Great to hear!

@stevet3331 3 жыл бұрын

Thank you Leila. A great formula update to create a yearly calendar, with a nice CF for the different years.

@LeilaGharani 3 жыл бұрын

You're very welcome, Steve!

@NandaKumar-vg9oy 3 жыл бұрын

So handy and step by step explanation. Thanks for sharing

@rjgittuwala 3 жыл бұрын

Mam Really You are Genius

@AbhishekTripathidce 3 жыл бұрын

It’s really nice and time saving, Tank you Leila

@danzirulez 2 жыл бұрын

Loved the music in the end - Relive uses exactly the same tune :) Joke aside, Leila taught me to use excel more efficiently in a few videos than what I learned since Office 2.0 in 1996 or something! Another excellent and simple video! Thank you

@felipelandim2881 3 жыл бұрын

Awesome. And your video effects are getting better and better.

@LeilaGharani 3 жыл бұрын

Glad you think so, Felipe!

@nancyguzman8976 3 жыл бұрын

Very cool! I liked the conditional formatting at the end. Nice touch.

@LeilaGharani 3 жыл бұрын

Thank you! Cheers!

@growwmore1205 Жыл бұрын

Great learning. Thanks for the video.

@cocoy4783 3 жыл бұрын

Je vais essayer, Merci Leila !

@urbaneplanner Жыл бұрын

interesting video - works as described - and I think an additional step in terms of making a calendar would simply be to add a blank row after each row of dates - then it would look exactly like a calendar

@LeilaGharani Жыл бұрын

👍

@shashaip3977 3 жыл бұрын

Thanks for your lessons

@LeilaGharani 3 жыл бұрын

It's my pleasure.

@pxnx 3 жыл бұрын

whoa! Didn't think it was that easy. Thanks for the clear explanation✨

@LeilaGharani 3 жыл бұрын

You're welcome 😊

@andreibutucaru7660 3 жыл бұрын

Very nice video. We can add the week number with this formula: =SEQUENCE(53,1,IF(WEEKDAY(DATE(D2,1,1))>5,0,1),1)

@patrickschardt7724 3 жыл бұрын

Great stuff. Dynamic arrays are great

@LeilaGharani 3 жыл бұрын

Yes they are, Patrick!

@ApoorvaDixit1488 3 жыл бұрын

This is so easy and useful. Thank you. You are the best excel tutor👍

@LeilaGharani 3 жыл бұрын

Glad you think so!

@user-fi6sb6ey1c 3 жыл бұрын

I’ve always loved Excel really , but all the tricks I learned from you add much flavours to it!!! Thank you

@LeilaGharani 3 жыл бұрын

Glad it was helpful! Thank you.

@elliehs 3 ай бұрын

Thanks a lot! My company does not allow us to use templates from external resources so doing my self is the only solution. It worked! Just one question, if I want to change font to red for a holiday, which option should I use to change format in the calendar? I have the list of holidays with a array. Thanks and I love your wayteacher, Thanks for making our lives easier

@DanKnight 3 жыл бұрын

Brilliant! I honestly had my doubts that you could do it with one formula....but I had never used the Sequence function before: thanks for introducing me to that function. Plus the use of multiple conditional formatting rules to add dynamic formatting was a very sweet touch.

@francois830 3 жыл бұрын

Leila you're the Best!!!

@JeremyMcMahan 3 жыл бұрын

Fantastic tutorial. I learned several new things on this one. Thanks again Leila!

@LeilaGharani 3 жыл бұрын

Glad it was helpful!

@drbahsha 3 жыл бұрын

THANK YOU SO MUCH!!!! Excellent presentation...!!! You are a great teacher!!!

@e-learningeverything6741 3 жыл бұрын

Dear sis, Thanks you so much for your shard this lesson, I always learn from you video. It help me alot.

@LeilaGharani 3 жыл бұрын

Glad to hear that

@superexplosiva2001 3 ай бұрын

So helpful!

@theedspage 2 жыл бұрын

These tutorials are amazing, thank you.

@Stef1955 3 жыл бұрын

Leila, Have you considered teaching a course on Office Script? It would be a big help to me and hopefully a lot of other people. Thanks, Mark

@LeilaGharani 3 жыл бұрын

I still need to feel more comfortable with it....

@Stef1955 3 жыл бұрын

@@LeilaGharani I’ll be the first to signup if you do! You have a certain knack for teaching that nobody else in the whole world has

@pipinmels 2 жыл бұрын

Your videos have been exeptionly helpfull, thank you. Only one comment on this video is you pronounciation of the word divisor..

@mattschoular8844 3 жыл бұрын

Thanks Leila. That's a great use of formulas to achieve the desired result..

@LeilaGharani 3 жыл бұрын

Glad you like it Matt.

@XcelGuruSayantan 3 жыл бұрын

This is a very complex thing to do. But you have explained it so well. Thank you.

@LeilaGharani 3 жыл бұрын

Glad to hear that :)

@DougHExcel 3 жыл бұрын

Nice use of SEQUENCE, love these dynamic array functions!

@LeilaGharani 3 жыл бұрын

Many thanks Doug!

@lalitbhandary8457 10 ай бұрын

just love the way you explain. cheers

@TheCob86 2 жыл бұрын

Hello there, thanks for the tutorials you post on youtube for us who wants to learn and learned a lot from you. Great channel, as a subscriber i am looking forward for every new excel tutorial you make in the future. In the mean time , i will watch all the tutorials u made so far, always excited to learn something new.

@LeilaGharani 2 жыл бұрын

Our pleasure! Hope you'll enjoy the videos!

@stanfordgibson 2 жыл бұрын

Very cool...I did this in a much more complicated way for years.

@LeilaGharani 2 жыл бұрын

👍

@tabbycat6802 2 жыл бұрын

Thats fine. The sequence function was also not available for years...

@mouhakk 2 жыл бұрын

Thank you for your nice and informative videos

@andyangus6894 3 жыл бұрын

You can calculate the number of rows by using weeknum function rather than hardcoding 54 rows. =SEQUENCE(WEEKNUM(DATE(E2,12,31)),7,DATE(E2,1,1)-WEEKDAY(DATE(E2,1,1))+1,1)

@deependragupta7679 3 жыл бұрын

Happy diwali to all my indian friends who are subscriber of this channel. Great to see a full dynamic calander in just 1 formulae. nesting date fn n logical calculations n mode fn in sequence is best part of the video.

@LeilaGharani 3 жыл бұрын

Many thanks Deependra.