Grab the file I used in the video from here 👉 pages.xelplus.com/dynamic-calendar-file
@elvisosei97663 жыл бұрын
Anyone ever told you.... "You r the best teacher on the globe?" Keep up the good work. Really appreciate all your efforts.
@LeilaGharani3 жыл бұрын
That's really kind! Thank you for watching and commenting.
@somesh23453 жыл бұрын
Liela create a channel on VBA..🙏
@jeffdea52782 жыл бұрын
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!
@gim1902 жыл бұрын
I've learnt more about date management in excel in these 10 minutes than in 10 years of no-brain use of it. Thanks!
@wrexxy493 жыл бұрын
I’ve never learned from someone who explains themselves so clearly. Thank you Leila!!
@LeilaGharani3 жыл бұрын
Glad it was helpful! Thank you.
@sawaimalhi84393 жыл бұрын
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
@somesh23453 жыл бұрын
Liela create a channel on VBA..🙏
@navjotsingh22513 жыл бұрын
Leila, you deserve all the money in the world for helping so many people!!
@gracemark59953 жыл бұрын
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 👍👍
@LeilaGharani3 жыл бұрын
Glad you like it, Mark!
@gracemark59953 жыл бұрын
@@LeilaGharani i'm of couse leila
@larslooperman64003 жыл бұрын
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.
@selianboy85082 жыл бұрын
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.
@leonardoggy7 ай бұрын
@@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.
@andrewmoss64493 жыл бұрын
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.
@LeilaGharani3 жыл бұрын
Very true Andrew....
@joshuadooley1812 жыл бұрын
I am going to start binge watching your Excel and PowerPoint videos! You are awesome! Thank you!
@theosalt12 жыл бұрын
It is just so simple to understand after playing it over 20 times slowly. Thank you.
@ramakrishnabalijepalli37213 жыл бұрын
This is simply a great way to create a calendar which was frustrating till now. Your presentation is awesome. Thank you so much!
@LeilaGharani3 жыл бұрын
Most welcome 😊
@ManojKumar-sk3fw3 жыл бұрын
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..
@mytwocents99743 жыл бұрын
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.
@jacquiambrose3 жыл бұрын
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?
@talibhussainraja77672 жыл бұрын
@@jacquiambrose conditional formatting
@edgy1rocks2 жыл бұрын
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!
@gerardlunow5673 жыл бұрын
Why does anyone give it is thumb's down. I should stop by once a week. I needed this calendar. Thank you!
@LeilaGharani3 жыл бұрын
Thanks for stopping by now!
@Oprimaita3 жыл бұрын
Whenever I watch your video, whenever I learn something new. Thanks a lot Leila.
@LeilaGharani3 жыл бұрын
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!
@yoliedepaz33403 жыл бұрын
Your voice is very soothing and pleasant to the ears! You’re a very good teacher and trainer. Wow! More power! 👏👏👏
@yoliedepaz33403 жыл бұрын
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.
@jeceva093 жыл бұрын
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.
@LeilaGharani3 жыл бұрын
You're very welcome, Jean!
@shizmosaic2 жыл бұрын
You change my life on a weekly basis!
@rollingpanda41983 жыл бұрын
WOW! This is awesome! So many calendars I've made over the years that were a lot more manual. Thank you!
@LeilaGharani3 жыл бұрын
You are so welcome!
@63Hash2 жыл бұрын
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
@notorioussamurai16717 ай бұрын
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-eb8hd3 жыл бұрын
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 !!
@LeilaGharani3 жыл бұрын
Awesome, thank you!
@BetterSheets3 жыл бұрын
I never heard of the Sequence function until a week ago and now I see it everywhere. Its sooooo useful.
@LeilaGharani3 жыл бұрын
It really is :)
@RZA128 ай бұрын
You're the best Leila! Thank you for teaching us in a great way 🙏
@tonyalas21032 жыл бұрын
I am humbled by your knowledge and motivated to learn more. Thank you!
@ashishmishra22463 жыл бұрын
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.
@TylerStanczak3 жыл бұрын
Love how this is all done with one formula and that gif of the girl frustrated is awesome!😂
@LeilaGharani3 жыл бұрын
😁
@angufibodavid40312 жыл бұрын
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.
@richardralph2 жыл бұрын
Genius. Such brilliantly constructed videos too.
@dlep92213 жыл бұрын
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.
@kgbadariprasad13 жыл бұрын
Awesome, Awesome, Awesome. Your videos demonstrate platinum standards of simplicity !!
@LeilaGharani3 жыл бұрын
Thank you very much!
@winxcelbliss Жыл бұрын
Leila Ma'am you are brilliant...
@uncgoalie3 жыл бұрын
Wow, I just discovered your videos and already watched 4 today - super helpful and extremely well done! Thank you!
@ehteshamshah2 жыл бұрын
Teaching Level is Excellent
@Co-Co12 жыл бұрын
I'm lucky to find you today on KZfaq, thanks for all your beautiful videos, you are amazing, wow! Hello to you from Australia
@LeilaGharani2 жыл бұрын
Wow, thank you!
@rahulsosonawane3 жыл бұрын
Brilliant Trick! Many Thanks Wish you be always Healthy & Happy:)
@wayneedmondson10653 жыл бұрын
Hi Leila. Fun example.. very clever. I used =ISODD(MONTH(B5)) for the conditional formatting instead of MOD. Thanks for sharing. Thumbs up!!
@LeilaGharani3 жыл бұрын
Great alternative. Thanks for sharing, Wayne!
@CampfireEditor3 жыл бұрын
@@LeilaGharani How did you do this? My version of 365 does not have capability to create New rule.
@Mymusicfmw333 жыл бұрын
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.
@krn142423 жыл бұрын
Love it Leila. What a difference from creating calendars dynamically in the past. Thanks for the great video as always.
@LeilaGharani3 жыл бұрын
Thanks so much! 😊
@ExcellentDude3 жыл бұрын
Thanks! Really great information as usual - added this calendar hack to my bag of tricks.
@LeilaGharani3 жыл бұрын
Great! Hope it'll come in handy.
@amaher792 жыл бұрын
Leila your videos are truly great and are helping me a lot. You make everything super easy.... A big thank you
@anandgajjar16182 жыл бұрын
Truly you are amazing tutor
@danh.43803 жыл бұрын
Why is this woman not the leader of the world! Thank you for all your hard work to bring us such wonderful videos.
@HenrikTham3 жыл бұрын
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)
@Caboladk2 жыл бұрын
thanks for the formula, I had thought that I wanted to add weeknumber in a column too
@marcpi83817 ай бұрын
This was actually more of a conditional formatting discovery for me!
@LeilaGharani7 ай бұрын
Glad you found something useful :)
@mohamedaboobackersiddique73223 жыл бұрын
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.
@LeilaGharani3 жыл бұрын
Thank you for your trust Mohamed.
@manuelquintino38223 жыл бұрын
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!...
@LeilaGharani3 жыл бұрын
Wow, thank you Manuel!
@micheldevost3 жыл бұрын
The money shot is at 9:33. Very cool!
@nathanbowers17613 жыл бұрын
Nicely done, Leila! Easy to follow, logical, and articulatley presented. Thank you.
@LeilaGharani3 жыл бұрын
Glad you enjoyed it, Nathan!
@jamesstubbins35102 жыл бұрын
Great video it works, I have added Week No. by entering in cell I5 =WEEKNUM(F5,21) then paste in to column
@super69man3 жыл бұрын
Brilliant. Every day I learn a new things from u. You are best teacher. Keep it up. Best luck 🤞
@LeilaGharani3 жыл бұрын
Thank you so much 😀
@alcxander2 жыл бұрын
every time the videos teach me something! good work!
@mahmoudam3 жыл бұрын
as usual you teaching me a lot, thank you so much.
@LeilaGharani3 жыл бұрын
My pleasure!
@meseahunt2 жыл бұрын
thanks Leila, smart info from a beautiful teacher!!
@signofshinejobs63913 жыл бұрын
Excellent Great Teacher in KZfaq
@venkatiyer74593 жыл бұрын
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!
@stevet33313 жыл бұрын
Thank you Leila. A great formula update to create a yearly calendar, with a nice CF for the different years.
@LeilaGharani3 жыл бұрын
You're very welcome, Steve!
@NandaKumar-vg9oy3 жыл бұрын
So handy and step by step explanation. Thanks for sharing
@rjgittuwala3 жыл бұрын
Mam Really You are Genius
@AbhishekTripathidce3 жыл бұрын
It’s really nice and time saving, Tank you Leila
@danzirulez2 жыл бұрын
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
@felipelandim28813 жыл бұрын
Awesome. And your video effects are getting better and better.
@LeilaGharani3 жыл бұрын
Glad you think so, Felipe!
@nancyguzman89763 жыл бұрын
Very cool! I liked the conditional formatting at the end. Nice touch.
@LeilaGharani3 жыл бұрын
Thank you! Cheers!
@growwmore1205 Жыл бұрын
Great learning. Thanks for the video.
@cocoy47833 жыл бұрын
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 Жыл бұрын
👍
@shashaip39773 жыл бұрын
Thanks for your lessons
@LeilaGharani3 жыл бұрын
It's my pleasure.
@pxnx3 жыл бұрын
whoa! Didn't think it was that easy. Thanks for the clear explanation✨
@LeilaGharani3 жыл бұрын
You're welcome 😊
@andreibutucaru76603 жыл бұрын
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)
@patrickschardt77243 жыл бұрын
Great stuff. Dynamic arrays are great
@LeilaGharani3 жыл бұрын
Yes they are, Patrick!
@ApoorvaDixit14883 жыл бұрын
This is so easy and useful. Thank you. You are the best excel tutor👍
@LeilaGharani3 жыл бұрын
Glad you think so!
@user-fi6sb6ey1c3 жыл бұрын
I’ve always loved Excel really , but all the tricks I learned from you add much flavours to it!!! Thank you
@LeilaGharani3 жыл бұрын
Glad it was helpful! Thank you.
@elliehs3 ай бұрын
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
@DanKnight3 жыл бұрын
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.
@francois8303 жыл бұрын
Leila you're the Best!!!
@JeremyMcMahan3 жыл бұрын
Fantastic tutorial. I learned several new things on this one. Thanks again Leila!
@LeilaGharani3 жыл бұрын
Glad it was helpful!
@drbahsha3 жыл бұрын
THANK YOU SO MUCH!!!! Excellent presentation...!!! You are a great teacher!!!
@e-learningeverything67413 жыл бұрын
Dear sis, Thanks you so much for your shard this lesson, I always learn from you video. It help me alot.
@LeilaGharani3 жыл бұрын
Glad to hear that
@superexplosiva20013 ай бұрын
So helpful!
@theedspage2 жыл бұрын
These tutorials are amazing, thank you.
@Stef19553 жыл бұрын
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
@LeilaGharani3 жыл бұрын
I still need to feel more comfortable with it....
@Stef19553 жыл бұрын
@@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
@pipinmels2 жыл бұрын
Your videos have been exeptionly helpfull, thank you. Only one comment on this video is you pronounciation of the word divisor..
@mattschoular88443 жыл бұрын
Thanks Leila. That's a great use of formulas to achieve the desired result..
@LeilaGharani3 жыл бұрын
Glad you like it Matt.
@XcelGuruSayantan3 жыл бұрын
This is a very complex thing to do. But you have explained it so well. Thank you.
@LeilaGharani3 жыл бұрын
Glad to hear that :)
@DougHExcel3 жыл бұрын
Nice use of SEQUENCE, love these dynamic array functions!
@LeilaGharani3 жыл бұрын
Many thanks Doug!
@lalitbhandary845710 ай бұрын
just love the way you explain. cheers
@TheCob862 жыл бұрын
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.
@LeilaGharani2 жыл бұрын
Our pleasure! Hope you'll enjoy the videos!
@stanfordgibson2 жыл бұрын
Very cool...I did this in a much more complicated way for years.
@LeilaGharani2 жыл бұрын
👍
@tabbycat68022 жыл бұрын
Thats fine. The sequence function was also not available for years...
@mouhakk2 жыл бұрын
Thank you for your nice and informative videos
@andyangus68943 жыл бұрын
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)
@deependragupta76793 жыл бұрын
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.