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
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?
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!
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)
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!
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 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..
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?
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 TH-cam "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.
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!...
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 !!
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.
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.
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.
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
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
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.
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
2nd comment??? (from Maryland 5:15AM) Thursdays with Leila and coffee (no cake … 😟) As usual, the presentation is flawless … the content intriguing … but I would need a pot of black coffee and several re-runs to figure out how I would use this very clever trick in my spreadsheets. Thank you Leila for more thought provoking content …
Hi Leila - I have learned so much from your channel!! Could you do a tutorial on adding a date picker for each row in a whole column and then format the date in the cell
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.
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..🙏
Leila, you deserve all the money in the world for helping so many people!!
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
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
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 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 am going to start binge watching your Excel and PowerPoint videos! You are awesome! Thank you!
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 😊
It is just so simple to understand after playing it over 20 times slowly. Thank you.
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.
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!
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....
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!
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
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!
You explain very clear and at pace anyone can keep up with, thank you from a Newbie.
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!
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..
Whenever I watch your video, whenever I learn something new. Thanks a lot Leila.
Happy to hear that!
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!
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?
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 TH-cam "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.
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.
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!
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.
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!
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.
You change my life on a weekly basis!
Awesome, Awesome, Awesome. Your videos demonstrate platinum standards of simplicity !!
Thank you very much!
You're the best Leila! Thank you for teaching us in a great way 🙏
I am humbled by your knowledge and motivated to learn more. Thank you!
Genius.
Such brilliantly constructed videos too.
Leila your videos are truly great and are helping me a lot. You make everything super easy.... A big thank you
Love it Leila. What a difference from creating calendars dynamically in the past. Thanks for the great video as always.
Thanks so much! 😊
Truly you are amazing tutor
Nicely done, Leila! Easy to follow, logical, and articulatley presented. Thank you.
Glad you enjoyed it, Nathan!
I'm lucky to find you today on TH-cam, thanks for all your beautiful videos, you are amazing, wow! Hello to you from Australia
Wow, thank you!
Love how this is all done with one formula and that gif of the girl frustrated is awesome!😂
😁
Wow, I just discovered your videos and already watched 4 today - super helpful and extremely well done! Thank you!
Why is this woman not the leader of the world! Thank you for all your hard work to bring us such wonderful videos.
Brilliant Trick!
Many Thanks
Wish you be always Healthy & Happy:)
Thank you very much. I learn a lot from your tutorials.
Thanks! Really great information as usual - added this calendar hack to my bag of tricks.
Great! Hope it'll come in handy.
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.
Leila Ma'am you are brilliant...
I never heard of the Sequence function until a week ago and now I see it everywhere. Its sooooo useful.
It really is :)
as usual you teaching me a lot, thank you so much.
My pleasure!
Teaching Level is Excellent
Excel is a tool very powerful and you are a very powerful teacher :-)
Great video it works, I have added Week No. by entering in cell I5 =WEEKNUM(F5,21) then paste in to column
Brilliant. Every day I learn a new things from u. You are best teacher. Keep it up. Best luck 🤞
Thank you so much 😀
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.
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)
Amazing .. watch any number of times ... your learning skill is excellent ... a big thank you again
thanks Leila, smart info from a beautiful teacher!!
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
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
👍
Very cool! I liked the conditional formatting at the end. Nice touch.
Thank you! Cheers!
Awesome. And your video effects are getting better and better.
Glad you think so, Felipe!
every time the videos teach me something! good work!
Thanks for your lessons
It's my pleasure.
Simply amazing! Subscribed!
Welcome aboard!
So handy and step by step explanation.
Thanks for sharing
The money shot is at 9:33. Very cool!
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!
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.
Great stuff. Dynamic arrays are great
Yes they are, Patrick!
Excellent
Great Teacher in TH-cam
just love the way you explain. cheers
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).
Fantastic tutorial. I learned several new things on this one. Thanks again Leila!
Glad it was helpful!
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
Your videos have been exeptionly helpfull, thank you. Only one comment on this video is you pronounciation of the word divisor..
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
This is so easy and useful. Thank you. You are the best excel tutor👍
Glad you think so!
Very good and useful. Thanks a lot.
It’s really nice and time saving, Tank you Leila
Fab video. Very useful. Thanks.
Mam Really You are Genius
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
I still need to feel more comfortable with it....
@@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
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.
This was actually more of a conditional formatting discovery for me!
Glad you found something useful :)
It was awsum learning, thanks Leila
You're welcome 😊
Thanks Leila. That's a great use of formulas to achieve the desired result..
Glad you like it Matt.
Thanks Leila . I got an idea to prepare the report significant
Great to hear!
THANK YOU SO MUCH!!!! Excellent presentation...!!! You are a great teacher!!!
So clearly for teaching. You got a new subscribed, congrats👏🏻👏🏻
Welcome aboard!
Nice use of SEQUENCE, love these dynamic array functions!
Many thanks Doug!
2nd comment??? (from Maryland 5:15AM) Thursdays with Leila and coffee (no cake … 😟)
As usual, the presentation is flawless … the content intriguing … but I would need a pot of black coffee and several re-runs to figure out how I would use this very clever trick in my spreadsheets.
Thank you Leila for more thought provoking content …
No wonder if there is no cake to go along with that black coffee. You need some sugar to get the thinking process going 😁
Je vais essayer, Merci Leila !
Hi Leila - I have learned so much from your channel!! Could you do a tutorial on adding a date picker for each row in a whole column and then format the date in the cell
I'd love to learn how to do this, too.
@@RaxLakhani me too
These tutorials are amazing, thank you.
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!
So helpful!
Awesome, didn't know that this is possible with only one function.
Thanks for sharing and for your amazing teaching skills!