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!
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
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 👍👍
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...
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!
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.
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?
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..
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!
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)
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.
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.
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 !!
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.
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!...
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
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?
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.
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.
These were two very helpful videos. two?🧐 Well, I needed to watch the mod-video too 🤣.- And now I understand completely what it does for me. Seriously thinking about joining as member to support Leila's videos and her content and definetly will check out her merch "I dont give a sheet" mug - great for offices 😂.
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.
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.
@@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 RUclips "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.
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.
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
We have a project month that ENDS the last Wednesday of each month. The next month naturally starts the next day regardless of the normal calendar. Seems like your design almost gets me there. Thanks.
Add another conditional format as the first rule. =and(weekday(B5)=4,day(B5)>23) that should highlight the last Wednesday of each month. (Works in sheets).
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
Grab the file I used in the video from here 👉 pages.xelplus.com/dynamic-calendar-file
Anyone ever told you.... "You r the best teacher on the globe?" Keep up the good work. Really appreciate all your efforts.
That's really kind! Thank you for watching and commenting.
Liela create a channel on VBA..🙏
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!
I've learnt more about date management in excel in these 10 minutes than in 10 years of no-brain use of it. Thanks!
I’ve never learned from someone who explains themselves so clearly. Thank you Leila!!
Glad it was helpful! Thank you.
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
Liela create a channel on VBA..🙏
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 👍👍
Glad you like it, Mark!
@@LeilaGharani i'm of couse leila
I am going to start binge watching your Excel and PowerPoint videos! You are awesome! Thank you!
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...
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.
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!
Genius.
Such brilliantly constructed videos too.
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.
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?
@@jacquiambrose conditional formatting
Thank you very much. I learn a lot from your tutorials.
Why does anyone give it is thumb's down. I should stop by once a week. I needed this calendar. Thank you!
Thanks for stopping by now!
This is simply a great way to create a calendar which was frustrating till now. Your presentation is awesome. Thank you so much!
Most welcome 😊
You change my life on a weekly basis!
I'm lucky to find you today on RUclips, thanks for all your beautiful videos, you are amazing, wow! Hello to you from Australia
Wow, thank you!
You explain very clear and at pace anyone can keep up with, thank you from a Newbie.
It is just so simple to understand after playing it over 20 times slowly. Thank you.
I am humbled by your knowledge and motivated to learn more. Thank you!
thanks Leila, smart info from a beautiful teacher!!
every time the videos teach me something! good work!
Leila your videos are truly great and are helping me a lot. You make everything super easy.... A big thank you
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..
You're the best Leila! Thank you for teaching us in a great way 🙏
This was actually more of a conditional formatting discovery for me!
Glad you found something useful :)
Whenever I watch your video, whenever I learn something new. Thanks a lot Leila.
Happy to hear that!
Your voice is very soothing and pleasant to the ears! You’re a very good teacher and trainer. Wow! More power! 👏👏👏
Hi Leila, do you have videos which may be helpful in monitoring stocks in excel? Thanks a lot in advance.
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.
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!
Excel is a tool very powerful and you are a very powerful teacher :-)
Awesome, Awesome, Awesome. Your videos demonstrate platinum standards of simplicity !!
Thank you very much!
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)
thanks for the formula, I had thought that I wanted to add weeknumber in a column too
Awesome. And your video effects are getting better and better.
Glad you think so, Felipe!
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.
Very true Andrew....
So handy and step by step explanation.
Thanks for sharing
Leila, you deserve all the money in the world for helping so many people!!
These tutorials are amazing, thank you.
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.
You're very welcome, Jean!
THANK YOU SO MUCH!!!! Excellent presentation...!!! You are a great teacher!!!
WOW! This is awesome! So many calendars I've made over the years that were a lot more manual. Thank you!
You are so welcome!
I never heard of the Sequence function until a week ago and now I see it everywhere. Its sooooo useful.
It really is :)
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.
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 !!
Awesome, thank you!
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.
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!...
Wow, thank you Manuel!
Amazing .. watch any number of times ... your learning skill is excellent ... a big thank you again
Thank you Leila. A great formula update to create a yearly calendar, with a nice CF for the different years.
You're very welcome, Steve!
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
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?
Very cool! I liked the conditional formatting at the end. Nice touch.
Thank you! Cheers!
Wow, I just discovered your videos and already watched 4 today - super helpful and extremely well done! Thank you!
Nicely done, Leila! Easy to follow, logical, and articulatley presented. Thank you.
Glad you enjoyed it, Nathan!
It’s really nice and time saving, Tank you Leila
Brilliant Trick!
Many Thanks
Wish you be always Healthy & Happy:)
Excellent
Great Teacher in RUclips
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.
Thank you for your trust Mohamed.
Great video it works, I have added Week No. by entering in cell I5 =WEEKNUM(F5,21) then paste in to column
Love it Leila. What a difference from creating calendars dynamically in the past. Thanks for the great video as always.
Thanks so much! 😊
Thank you for your nice and informative videos
Really appreciate your efforts
Thanks 👍
Great tutorial! Thanks for sharing this with us!
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.
Our pleasure! Hope you'll enjoy the videos!
This is so easy and useful. Thank you. You are the best excel tutor👍
Glad you think so!
Thanks! Really great information as usual - added this calendar hack to my bag of tricks.
Great! Hope it'll come in handy.
Always beautiful with good videos!!😍
Thank you! 😃
Hi Leila. Fun example.. very clever. I used =ISODD(MONTH(B5)) for the conditional formatting instead of MOD. Thanks for sharing. Thumbs up!!
Great alternative. Thanks for sharing, Wayne!
@@LeilaGharani How did you do this? My version of 365 does not have capability to create New rule.
These were two very helpful videos. two?🧐 Well, I needed to watch the mod-video too 🤣.- And now I understand completely what it does for me. Seriously thinking about joining as member to support Leila's videos and her content and definetly will check out her merch "I dont give a sheet" mug - great for offices 😂.
Teaching Level is Excellent
So clearly for teaching. You got a new subscribed, congrats👏🏻👏🏻
Welcome aboard!
as usual you teaching me a lot, thank you so much.
My pleasure!
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.
Sir, is the Harcoding of 53 Wrong?
@@winxcelbliss Try to output the year 2028... in this case (a leap year starts on saturday) you need 54 weeks.
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.
@@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 RUclips "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.
Truly you are amazing tutor
Brilliant as always - thanks :)
just love the way you explain. cheers
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)
Leila Ma'am you are brilliant...
Great learning. Thanks for the video.
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.
Nice use of SEQUENCE, love these dynamic array functions!
Many thanks Doug!
This is SO AWESOME!! Thank you for sharing these extremely useful tips!
You're so welcome!
Fantastic tutorial. I learned several new things on this one. Thanks again Leila!
Glad it was helpful!
I’ve always loved Excel really , but all the tricks I learned from you add much flavours to it!!! Thank you
Glad it was helpful! Thank you.
Thanks Leila. That's a great use of formulas to achieve the desired result..
Glad you like it Matt.
Awesome Leila! This is really helpful. Your style and explanations are excellent!
Thank you so much, Charles!
whoa! Didn't think it was that easy. Thanks for the clear explanation✨
You're welcome 😊
It was excellent! You are a brilliant teacher!
Thank you! 😃
Muchas gracias! era exactamente lo que estaba buscando :)
Absolutely brilliant. Thanks Leila you never disappoint
My pleasure 😊
Thanks for your lessons
It's my pleasure.
Nice job. Thanks for the formula.
Leila you're the Best!!!
wow! Brilliant!! Thank you so much
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
👍
We have a project month that ENDS the last Wednesday of each month. The next month naturally starts the next day regardless of the normal calendar. Seems like your design almost gets me there. Thanks.
Hope it will work out.
Add another conditional format as the first rule.
=and(weekday(B5)=4,day(B5)>23)
that should highlight the last Wednesday of each month. (Works in sheets).
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
Dear sis,
Thanks you so much for your shard this lesson, I always learn from you video. It help me alot.
Glad to hear that
Smart as usual, Leila
Thank you!
Leila, that's an amazing idea to apply dynamic arrays to generate calendar! Great great application of dynamic arrays!
Glad you think so!
Why is this woman not the leader of the world! Thank you for all your hard work to bring us such wonderful videos.
So helpful!