Say Goodbye to Manual Calendars with This Excel Trick (File Included)

  Рет қаралды 58,403

MyOnlineTrainingHub

MyOnlineTrainingHub

Күн бұрын

Пікірлер
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
❓What calendar feature would save you the most time? Tell me below. Learn Excel with my courses: bit.ly/calendar24courses
@stephenbaxter4833
@stephenbaxter4833 4 ай бұрын
How to groups of dates such as annual leave or tasks over multiple days for a team. That would be so great
@ThomTydeman
@ThomTydeman 3 ай бұрын
How to use this with Excel's new checkbox functionality so the calendar highlights uncompleted tasks. When you hover over a date with an uncompleted task, can you display the task description? Thank you again for sharing your expertise in such an effective manner.
@petemininni5671
@petemininni5671 3 ай бұрын
Hi, Love your videos. I haven't seen ALL your videos relating to calendars, and you may already have posted something I'm thinking about. I used your video to set up a "Construction Bid Date" calendar, but I'm wondering if you know a formula that when hovering over the date the project name will appear. That would be very helpful for me type calendar.
@xavierbourguignon37220
@xavierbourguignon37220 3 ай бұрын
I'd love to see the ability to turn on or off certain calendars, forexample I might only be interested to look at holidays, tomorrow I might to look only at delivery dates, or I ight want to see all the calendars displayed also.
@ilhanschen
@ilhanschen 2 ай бұрын
Great! Your videos are not just technically informative but also aesthetic details included with details. I found it very valuable, which also means you built an empathy with the viewers, at least I felt so because I always put some personal touches into my works in Excel by customizing the tables, charts etc. to my liking as long as it is applicable of course, along with the streamlined standards of the corporation I work for. I really appreciate your effort. Thank you!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Thanks so much! Glad you enjoyed it 😊
@andre6543
@andre6543 4 ай бұрын
Didn't now you could click the Format option twice so that you can apply the same font options to multiple areas! Going to save me so much time in the future. Thanks for sharing the dynamic multi-year calendar!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Great to hear you discovered something new!
@rw881
@rw881 4 ай бұрын
I love your lessons and always learn something new! I'll have to explore how to create the day for holidays like Thanksgiving that are always the 4th Thursday of the month, or most US holidays that are always on Monday. Keep up the great work!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Yes, that's a great idea. I was going to do that in this video, but it turned out way too long because there are lots of different scenarios to cover and then you have to consider other parts of the world... If you go to the File tab in Excel > New and then type in the search "any year calendar with holidays" you'll find a template calendar you can download with these formulas.
@markclifton9524
@markclifton9524 3 ай бұрын
That's brilliant - it never ceases to amaze me how you know all these things!!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Thanks so much, Mark!
@KittKatPawz
@KittKatPawz 4 ай бұрын
I was just talking about this with my co workers this morning about keeping track of days when we’re off cause our systems are very limited. Thank you for this!!!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
So glad you can make use of it!
@ThomTydeman
@ThomTydeman 3 ай бұрын
Brilliant. Your content is extraordinarily helpful to me. Can you teach us how to emulate your cool, calm demeanor so I can adopt it when I help my daughter with her math homework?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
😁 thank you! Unfortunately, I am not calm when it comes to homework either!
@brookeanderson662
@brookeanderson662 3 ай бұрын
You are a genius, Mynda. I learn so much from your videos! Thank you.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
That's wonderful to hear 🙏
@jnev785
@jnev785 4 ай бұрын
I have spent the last eight years annually creating an Excel calendar to show a working week beginning on a Saturday, manually entering each depressing day, lol. Thank you so much for this. One new last calendar to create....forever!!!!.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
So pleased this will be helpful to you 😁
@hanozt
@hanozt 3 ай бұрын
I have been building a crude calendar formula for years, were i simply use the "preceding cell" +1 as the formula and then manually enter the 1st days of the month. THIS certainly saves me a lot of time!!!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Glad it will be useful 😊
@CaptainCap-p8h
@CaptainCap-p8h 7 сағат бұрын
Thanks for sharing your knowledge. I have another question would you share your color team?
@learnitinstructor5792
@learnitinstructor5792 3 ай бұрын
WOW! Just WOW! I've been wondering how to build one of these for a very long time. I have been downloading, but couldn't understand the formulas that were being used. I'm going to do this from now on!!! Thank you!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
So pleased this was helpful!
@malikastar9265
@malikastar9265 Ай бұрын
Thank you, your channel is a must, straightforward as usual and very clear, I really appreciate it !
@MyOnlineTrainingHub
@MyOnlineTrainingHub Ай бұрын
So pleased you find it helpful 🙏
@hemant749
@hemant749 3 ай бұрын
It is a beautiful method to create a dynamic calendar. I have a simple question: if I want to add the holiday name to a date, how can it be done? Your teaching is very simple, and anyone can understand it. Thank you.
@brandankeel1855
@brandankeel1855 3 ай бұрын
Great video! Even learned some other shortcuts that I wasn't aware of. Plan to make one to track recurring deadlines for my work. Thanks for sharing and I look forward to learning from your other Excel videos 😊
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
So glad it was helpful!
@kiwim3p587
@kiwim3p587 4 ай бұрын
Very cool. As a suggestion, for the year and month labels you could use the correct date serial with formatting to give you the correct label text, then your conditional formatting would be global.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Yes, I did consider that, but decided against it in the end. Thanks for mentioning it as an alternative though 🙏
@kiwim3p587
@kiwim3p587 4 ай бұрын
@@MyOnlineTrainingHub Nice one, what were the reasons not to?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Just that it would be more complicated to make and it was already quite a long video. It's always a balance between quality and quantity 😅
@sahralsahri7180
@sahralsahri7180 4 ай бұрын
Great calendar, smart vba controls and best of all it updates automatically. Let's try it. Thanks a lot Madeleine.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Thank you!
@jcwillette
@jcwillette 4 ай бұрын
Thanksgiving date correction would be nice for each year (4th Thursday of the month). Would also like to be able to save meetings either weekly, bi-weekly or monthly on a certain day of the week. Great video and instruction...favorite teacher!!!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Great ideas. I did consider making the holiday dates like Thanksgiving update, but it became a very long video. If you go to the File tab in Excel > New and then type in the search "any year calendar with holidays" you'll find a template calendar you can download with these formulas.
@jcwillette
@jcwillette 4 ай бұрын
Thank you. I will check them out as it adds a lot to your calendar.
@nadzirahfarhanah4470
@nadzirahfarhanah4470 3 ай бұрын
Great video ! Learnt so much, thank you ! A question - how do I add the week number to the calendar ?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
You can use the WEEKDAY function to return the week number in a column to the left of each month's dates.
@andrewo.a5381
@andrewo.a5381 3 ай бұрын
Simply Scintillating!!!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Thank you!
@chrism9037
@chrism9037 4 ай бұрын
So cool thanks Mynda!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Cheers, Chris! Thanks for watching.
@daleanderson5258
@daleanderson5258 4 ай бұрын
Great video! So full of useful information. Thank you for sharing.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Thank you!
@Robert8455
@Robert8455 3 ай бұрын
That was very slick and eye appealing too. Thank you
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Glad you liked it, Robert!
@sbatsia
@sbatsia 4 ай бұрын
Practical, Useful with simple formulas. Thank you, Mynda,.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Thank you!
@taypac1775
@taypac1775 4 ай бұрын
Love it! Now I’m just wondering how to best add in other key dates/ periods like each quarter and half year, etc.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Awesome to hear!
@theoneandonlymattsmith
@theoneandonlymattsmith 2 ай бұрын
Really useful video - thank you! I am guessing that there is no way to deal with school holidays which change on an annual basis?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
If you can predict them reliably then you could write a formula to update them, but where I come from, they aren't consistent from year to year, so I have to manually enter the dates.
@-flabber6663
@-flabber6663 4 ай бұрын
Excellent! I used to make calendars. You can certainly name your calendar, add some pictures and colors for printing. It would be very useful to mark the days with time to adjust.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Great idea 🙏
@NagyElAlfy-v7v
@NagyElAlfy-v7v 4 ай бұрын
fantastic, really helpful, many thanks
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Great to hear!
@JustAnotherPassenger
@JustAnotherPassenger 4 ай бұрын
I really like the pace of this video. On a side note, took a little while but I've come around to Aptos now, still not sure about the new theme palette though.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Thank you! Yes, Aptos is still growing on me too. I used the Median colour theme in this video. The default one is a bit harsh still for my liking.
@DingusBatus
@DingusBatus 4 ай бұрын
I done something very similar. Only my formula are longer and more complicated? Yours look a lot simpler. Interesting.🤩🤩😍
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Glad you discovered a new approach. Hope you can make use of these formulas.
@martinargimon730
@martinargimon730 4 ай бұрын
Excellent tip. Thx once again. U r an expert. !!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
So nice of you 🙏
@missamo80
@missamo80 3 ай бұрын
6:57 I noticed the list of birthdays formula uses a specific cell range. What happens if you add more birthdays? Would it be better to use a table column reference instead?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
You can't use table structured references in conditional formatting, so the best thing to do is just select more rows in the COUNTIF formula to allow for more rows to be added.
@izzybizzybee72
@izzybizzybee72 4 ай бұрын
Another great video! Is there a way to highlight a date range, for example to show employee annual leave instead of birthdays?
@izzybizzybee72
@izzybizzybee72 3 ай бұрын
Woo hoo, I worked it out myself using the AND function - =AND(B7>=$AK$5,B7
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Well done! 🥳
@-massudin.7488
@-massudin.7488 3 ай бұрын
very interesting, to add completeness to the calendar, can information be added to the cells that contain holiday and birthday information if the cell is selected or highlighted?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Yes, you could insert a comment for the relevant date.
@NagyElAlfy-v7v
@NagyElAlfy-v7v 2 ай бұрын
شكرا You always have wonderful things to explain them to us
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Thank you! 😊
@frsnkph
@frsnkph 2 ай бұрын
this is very helpful. would this still work if it is a range of dates (start and end). Example would be days a person will be away?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
You'd have to modify the conditional formatting rule to look at ranges.
@frsnkph
@frsnkph 2 ай бұрын
@@MyOnlineTrainingHub thank you. I'll have a play around with it.
@Oldan-Enuar
@Oldan-Enuar 3 ай бұрын
Amazing, can you please do it adding calendar week numbers
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Thank you! You can add week numbers using the WEEKDAY function in the column to the left of each month.
@heinzthedutch
@heinzthedutch 4 ай бұрын
Excellent stuff thanks! Haven't seen this SEQUENCE function yet. I would probably have used some old-fashioned matrix formula to get there...
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Great you discovered something new! Thanks for watching.
@harjith.d.bubber
@harjith.d.bubber 3 ай бұрын
Thank you so much for this
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
You're so welcome!
@yesiunderstand6044
@yesiunderstand6044 4 ай бұрын
Excellent, very useful
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Glad to hear that!
@aeonturnip2
@aeonturnip2 4 ай бұрын
Very nice! Is there a way to make the birthday icon dynamic based on whether there is a birthday in that month, rather than the manual way you did it?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Not using the icons, but you could use an emoji or symbol in an IF formula in the header of each month. e.g. =IF(calendar month is in birthday table, then emoji/symbol, "")
@aeonturnip2
@aeonturnip2 4 ай бұрын
@@MyOnlineTrainingHub Great - thanks. I've done this in a slightly clunky way =IF(COUNTIF(Birthdays[Month],MONTH(Z5&1))>0,"🎁","")
@jorgepaz22
@jorgepaz22 4 ай бұрын
Great tip! Thank you ❤
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Glad it was helpful!
@kenfong8580
@kenfong8580 3 ай бұрын
I added a column to the birthdays to calculate the current year's birthday value, and inputted an actual birthdate (the year does not really matter), so you don't have to re-enter the birthdays every year.. =DATE(YEAR(TODAY()), MONTH(A1), DAY(A1)) where A1 is where the actual birthdate is entered. And then used the values in that new column for the conditional formatting rule. Will do the say for the holidays for those holidays that don't change dates each year.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
If you download my file, you'll see I did something similar but instead I reference the year values in the calendar in the 'year' argument of the DATE function, so that as you change the year using the spin button, the conditional formatting automatically updates too.
@VENKATRAMANIYER-g7b
@VENKATRAMANIYER-g7b 23 күн бұрын
@@MyOnlineTrainingHub It took a while for me to understand your referencing of the year values in calendar with date function... outstanding way ...excellent stuff ... I'll try to put this into use wherever possible... thanks a lot mam. By the way the calendar is mind blowing .... explanations for making a beautiful calendar is awesome ... thanks a lot again
@juanbon5996
@juanbon5996 3 ай бұрын
Spectacular!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Thank you!
@RogerStocker
@RogerStocker 4 ай бұрын
Hi Mynda nice Video, there have been some takeaways for me, for sure.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Great to hear, Roger!
@sidbaishya17
@sidbaishya17 2 ай бұрын
Brilliant videos its awesome. I have been learning regularly by watching your tutorials which are full of information and detailed. btw I been struggling this part on the same video. hope you can redirect me there. After you format all the calendars to white and only make the current dates visible you toggled the up and down arrow and the dates and the colors changed in the calendar. I have downloaded the excel template and I checked that when I toggle the up and down arrow in the calendar the dates changes in both the sheets, the annual calendar and the dates calendar which has the public holidays and the birthdays. I am struggling to make that auto switch. I see there is a formula in the dates sheet on those tables. can you guide me to that section. Still learning excel after having been in this industry for quite sometime. its awesome and thanks.
@sidbaishya17
@sidbaishya17 2 ай бұрын
Oh I figured out the formula. A little hard work but thanks Traecy. Now the sheet looks more dynamic and all the dates are changing automatically. Awesome.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Glad you found this video helpful. Well done figuring out the dates issue 💪
4 ай бұрын
As always, excelent content! Thank you Mynda for sharing your invaluable knowledge and experience . One note, if you do not use English regional settings, after changing the year the calendar will not work (#ARG) unless you replace the month names with those used by your computer, which was my case :-)
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Ah, yes good point for non-English versions of Excel.
@6ugggg
@6ugggg 4 ай бұрын
nice work thanks for your efforts
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
It's my pleasure 🙏
@ChristinaHarper-m8g
@ChristinaHarper-m8g 2 ай бұрын
Hi! Thank you so much for your time to share your videos! Your videos are amazing!!! I followed your step to create a calendar, but I don't know why the holidays and birthdays didn't apply to year 2025, 2026 going forward? Is it because the dates we entered as "2024", so it makes sense it won't show up on other years. But how can we make the dates (like New Year, Christmas, Birthdays) these unchanged dates show up every year? Thank you so much!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Sounds like you hard keyed the year for the dates. If you look at my dates, you'll see I have a formula that picks up the year from the Calendar sheet.
@ChristinaHarper-m8g
@ChristinaHarper-m8g 2 ай бұрын
@@MyOnlineTrainingHub I am so sorry I didn't see the formula that picks up the year from the Calendar sheet? If you have time to kindly let me know the formula, that would be much appreciated.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
You can download me completed file from the link in the video description and see all the formulas.
@Tradingnoobs
@Tradingnoobs 2 ай бұрын
Great video, I tried creating a Macro for a reminder notification pop up box but my company wouldn't allow it. Can you build a reminder pop for important dates on the calendar?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
You could use conditional formatting to 'reveal' a reminder when certain conditions are met, so maybe this is an option for you.
@laurasol4565
@laurasol4565 3 ай бұрын
Hi! Is there a way use the sequence to auto populate dates for the weekdays to be in a column rather than in a row as per your example? Thanks!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Yes, just wrap the formula in TRANSPOSE e.g. =TRANSPOSE( SEQUENCE(...) )
@jimpoland3302
@jimpoland3302 4 ай бұрын
Hi Mynda, another great lesson, thanks. Can this, or similar be done in Excel 2016 = without sequence function?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Not easily. I recommend getting a calendar template from the File tab > New, and download a template.
@jimpoland3302
@jimpoland3302 3 ай бұрын
@@MyOnlineTrainingHub I didn't think so. Thanks, template it is.
@janicedarroch4747
@janicedarroch4747 2 ай бұрын
This looks great - I'm trying to build a payroll calendar - but I don't seem to have the SEQUENCE function - is there an alternative?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
There's no simple alternative to the sequence function. I recommend you download a calendar template from Excel. Go to the File tab > New and search for "Any year calendar with holidays" and you'll find a calendar that works in older versions of Excel.
@BoyvanHeugten
@BoyvanHeugten 4 ай бұрын
OMG I've never used double click format painter. Will try this out tomorrow!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
😁great to hear!
@PaulEBrownbill
@PaulEBrownbill 3 ай бұрын
thanks
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Welcome 😊
@al3xj
@al3xj 4 ай бұрын
Hi Mynda - have you played with excel on a Mac? Hard to see any good videos on this, new Macbook Pro's hardware performance was the reason for my test - but mac excel is so poor, with no power query and many short cuts that can't be replicated - then even using Mac Parallels (a virtual windows experience on Mac) gave an excellent windows performance (better than in windows on a pc) and excel is as windows, yet still issues with shortcuts and multi-monitors, so still testing - thanks as always
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
I don't have a Mac, but good to know the Windows version runs well on Parallels.
@wesw8821
@wesw8821 3 ай бұрын
Hi Mynda! Would you please help me understand how the calendar is dynamic when referenced to the birthday and holiday tables? I added a date to the birthday table in your sample spreadsheet and discovered that date did not appear in the annual calendar as I would have expected without my opening the "Conditional Formatting Rules Manager" and editing / expanding the range where the format should apply. I appreciate any insights you can provide.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
You just need to edit the COUNTIF formula used in the conditional formatting rule to extend past the end of the table to allow you to add more dates.
@AdvisorsDesk
@AdvisorsDesk 4 ай бұрын
Loved it 😊
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Thank you!
@YamaHoyes
@YamaHoyes 2 ай бұрын
Hello Mynda...Everything was going great through hiding the overflow dates...I thought I'd click the spin button to see all that formatting happen...it didn't. Do I need to specifically do the conditional formatting for each year for the holidays and birthdays to appear? Thank you.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
I suspect your holidays and birthday dates aren't being entered with the dynamic date field. If you download my file, you''ll see how I created them.
@glennhargett2243
@glennhargett2243 2 ай бұрын
For the conditional formatting, was the rule set to spreadsheet or to a range? I cannot get the Today Highlight rule to apply unless I copy the formula from B7 to today.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
The conditional formatting ranges vary. For the 'today' rule, it's applied to B7:AF30. You can download the example file to see the other rules from this page: www.myonlinetraininghub.com/dynamic-excel-calendar
@vishaalhassan5122
@vishaalhassan5122 2 ай бұрын
Hey @MyOnlineTrainingHub great video I just wanted to ask how could I insert dates in dates sheet so that every time I use spin button my highlighted dates carry on the next year because right now they are not highlighted if I change my year to some other than 2024 ?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
Thanks for watching my video. If you download the example file from the link in the video description, you can see the formulas I used to ensure the dates update with the spin button.
@kokabkhalid3181
@kokabkhalid3181 2 ай бұрын
I am new to this type calander , kindly let me know where and for what we can use it ....
@MyOnlineTrainingHub
@MyOnlineTrainingHub 2 ай бұрын
I like to use this type of calendar to plan my year so that I can see when holidays and other occasions fall when planning. You could use it for staff vacation planning and other similar events.
@MrTubertub
@MrTubertub 4 ай бұрын
Thanks. But what about the weeknumbers?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
You can add another formula with WEEKDAY to insert the week numbers to the left of each month's dates.
@jfgabito1987
@jfgabito1987 3 ай бұрын
Can we use this as a drop down calendar?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
No, Excel doesn't have that functionality in the desktop yet, but if you open your file in Excel Online, date fields will automatically have a date picker pop out tool.
@UghUgh1234
@UghUgh1234 4 ай бұрын
What will the formatting be if there’s two things happening on the same date?
@tsalb96
@tsalb96 4 ай бұрын
Whichever item is higher on the list has priority. You can however have multiple rules display if they change different parts of format, 1 changing fill, 1 changing font etc.
@TheMarkGross
@TheMarkGross 4 ай бұрын
I had the same thought, this is the real trick. The formatting idea for dates that have multiple conditions is all I could think of as well. I suppose you could have a drop down for birthdays and holidays and have the conditional formatting check what is in the drop down, but that's not so ideal either. Ideally you'd want a signal view of all holidays, birthdays etc.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
If you expect/know dates will clash then use a different type of format e.g. fill colours for holidays and cell borders for birthdays, that way both formats can display.
@Roots222
@Roots222 3 ай бұрын
Great video and I picked up some new things that will help me in other areas. Thank you. One question.. on the Dates tab, all the dates are 2024, but when you click through 2025 and 2026 on the Calendar tab, the calendar still keeps the conditional formatting.. are the special dates on the Dates tab also linked to the '2024' on the first tab? (you click the arrow to 2025 and the special dates change from 01/01/2024 to 01/01/25?)
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Great to hear! Yes, the special dates are linked to the year on the calendar tab. This might not work for some dates, like Thanksgiving, so you may need to recalculate those differently. For formulas that will automate this, in Excel, go to the File tab > New and type “Any year calendar with holidays” you’ll find an example calendar that does this.
@Roots222
@Roots222 3 ай бұрын
@@MyOnlineTrainingHub thank you! 🙂
@zakhall7680
@zakhall7680 3 ай бұрын
@@MyOnlineTrainingHub The video doesn't show how to link the special dates to the year. Can you describe how to do that?
@zakhall7680
@zakhall7680 3 ай бұрын
Never mind, downloaded the spreadsheet. Thanks!
@datingdave1310
@datingdave1310 4 ай бұрын
I got lost at 3:00 - how do you get the months to autofill, and a given spacing, from just typing January to April...?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
The month names aren't auto filled. I just typed them in as you saw me do for January, February etc. and then fast forwarded so you didn't have to sit through me typing in the rest. It's the dates that autofill.
@datingdave1310
@datingdave1310 4 ай бұрын
​@@MyOnlineTrainingHubThanks for clearing up that confusion...
@jnev785
@jnev785 4 ай бұрын
Is there a way to add dynamic financial week numbers to each week?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Sure, I guess you could do this in an extra column for each month.
@ayyappanusa
@ayyappanusa 3 ай бұрын
Brilliant
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Thank you!
@navaarunbhattacherjee4511
@navaarunbhattacherjee4511 3 ай бұрын
Kindly, make another video on this same topic for "Google Sheet". Thanks for creating such a valuable content! Also, I'd like to admit, I might have a bit of a crush on you.. 😅 Your expertise in Excel are really impressive! 😊
@potatocraftd6629
@potatocraftd6629 3 ай бұрын
Whats the practical use/s for this?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
I use this type of calendar to plan my work at a high level and our promotions, while taking into account school and family holidays.
@biserkabajecleskovar1167
@biserkabajecleskovar1167 23 сағат бұрын
how you made the video - first sheet? video?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 11 сағат бұрын
Not sure that you mean, sorry.
@AssinaturaUnica
@AssinaturaUnica 21 сағат бұрын
Parabéns pelo vídeo. Você é espetacular! Parabéns.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 10 сағат бұрын
Obrigado!
@joewilson4599
@joewilson4599 4 ай бұрын
Hi Mynda. This is very good. Is it me or does the conditional formatting for Holidays & Birthdays need adjusting to look at the Tables rather than fixed data (=COUNTIF(Dates!$B$5:$B$24,B7)) so that when you add extra dates in both tables they show in the calendar.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Thank you! Conditional Formatting doesn't recognise table structured references. You can however, extend the range being referenced in the COUNTIF formula to allow for more rows to be added to the tables.
@kevinmc2000YT
@kevinmc2000YT 3 ай бұрын
I just named the table columns as named ranges and used those names within the conditional formatting formulas. It works and allows for table rows to be added. Thanks for teaching us! I just finished a calendar i will be using to track my airbnb reservations!
@johnjacobjinglehimerschmid3555
@johnjacobjinglehimerschmid3555 4 ай бұрын
I just made the same thing in Google Sheets ( what a POS spreadsheet that is ) that is quite the same. I'll have to look into your formula looks way simpler than mine. However, your holidays as a table seems to be limited to just 1 year would have to find out how to calculate the dates of all holidays when the selected year changes.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
If you download my example file you can see how I make the birthday dates update. The holidays can be a bit more complex because they aren't always the same date, some are the last Thursday in the month etc. so I didn't cover that in this video.
@pierrejeames
@pierrejeames 3 ай бұрын
Hello, thank you for your work. My excel doesn't have the sequence formula. what should i do?
@potatocraftd6629
@potatocraftd6629 3 ай бұрын
Update to a newer version
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
There's not a simple answer to this. You might be better off searching for a free calendar template via the File tab in Excel > New.
@JUANETEMTY
@JUANETEMTY Ай бұрын
I could not continue after 5:38, my conditional formatting looks different.
@MyOnlineTrainingHub
@MyOnlineTrainingHub Ай бұрын
Please download the example file and inspect and compare my formats to yours to see where the issue is.
@ScienceGuyRoy
@ScienceGuyRoy 4 ай бұрын
Thanks, unfortunately, I couldn't get the dates for the birthdays to be dynamic. When I add a date for the Observed Holidays, it shows up fine, but when I do the same for the birthdays, it does not.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
The conditional formatting formula for the birthdays range just needs to be extended to allow for more dates to be added. The observed holidays range goes to row 24, but the birthdays only goes to row 7. Just change the birthday's formula to =COUNTIF(Dates!$G$5:$J$24,B7) or whatever row you need.
@ScienceGuyRoy
@ScienceGuyRoy 4 ай бұрын
I see. Makes sense. Thank you again!
@gregmitchell1671
@gregmitchell1671 3 ай бұрын
How did you auto fill the calendar months into the format of 8 cells by 8 cells?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
The SEQUENCE function is a dynamic array formula that spills the results: www.myonlinetraininghub.com/excel-functions/excel-sequence-function
@johngamiles2541
@johngamiles2541 4 ай бұрын
Is there any way to split the highlighting if a date has two activities (holiday AND birthday) on the same day? I'm not aware of any way to create formatting to accommodate that.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
You can set different types of formatting if you expect a conflict. e.g. cell fill colour for one format and cell borders for another. That way both formats can appear on the same cell.
@johngamiles2541
@johngamiles2541 4 ай бұрын
@@MyOnlineTrainingHub Good idea....thank you for the suggestion!
@火盧
@火盧 4 ай бұрын
Very nice! Boxing day 🤣
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Yep, we get two days off for Christmas. 💪😁
@火盧
@火盧 4 ай бұрын
@@MyOnlineTrainingHub my Canadian wife misses the Boxing Day sometimes, now that she's in the U States. I really could careless about the Black Friday. 😄 Please keep those awesome videos coming! 😊
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Boxing day is a recovery day for the Christmas indulgence 😅
@danchristiansen8853
@danchristiansen8853 4 ай бұрын
Is there anyway, other than manually changing the table, to adjust floating holidays? US Holidays like Labor day (the 1st Monday in Sept), Thanksgiving (the 4th Thursday of Nov), Easter (How every that is calculated?) change dates every year. So when you change years the holiday is wrong.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Yes, but each floating date will require a different formula, so I didn't cover them in this video. However, if you go to the File tab in Excel > New and search for "Any year calendar with holidays” you’ll find a calendar that has these formulas.
@pass-the-juice
@pass-the-juice 4 ай бұрын
finally someone else who says "yooey"
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
😁
@balazserdesz
@balazserdesz 4 ай бұрын
How should I modify your formula if I want Monday to be the first day of the week?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
I show that at the end of the video - see here 11:25. You can also download the example file to see it
@balazserdesz
@balazserdesz 4 ай бұрын
@@MyOnlineTrainingHub thanks
@hanozt
@hanozt 3 ай бұрын
We use a custom 4-4-5 wk calendar at work. So non qtr-end months are 4wks and the qtr-end months are 5wks. So what tweaks can i do to this solution to make it work for our 4-4-5 wk calendar format ?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
I don't have a tutorial on this that I can point you to, so you're best to search for a 4-4-5 week calendar template.
@hanozt
@hanozt 3 ай бұрын
@@MyOnlineTrainingHub but is there a small tweek that I could do to this above process? It's basically just grouping of the 52wks of the year into groups of 4-4-5 weeks
@bobburke8727
@bobburke8727 4 күн бұрын
Everything works except the holidays and birthdays don't transfer to the next year via the spinner. Could it be because we are specifying the year (2024)? Love your videos, BTW.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 күн бұрын
Hi Bob, if you look at my file that you can download from the link in the video description, you'll see how I've inserted the birthday dates so they update with the year spinner.
@bobburke8727
@bobburke8727 4 күн бұрын
@ I used your formula; I’ll go back and check it again ( for the nth time!).
@FKKTB
@FKKTB 3 ай бұрын
Why is not highlighting the days when I did the same thing or the conditional formatting formula
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Check the conditional formatting COUNTIF formula is picking up all the rows in the table. You can extend it past the end of the table to allow for growth as Conditional Formatting doesn't recognise table structured references.
@telstraroenssel3701
@telstraroenssel3701 3 ай бұрын
is it possible to make this not formatted like a printed calendar with rows and tables but infinitely going to right direction? jan 1 2 3 4 5 6 and so on empty rows empty rows empty rows to fill with my sales data i still make manual calendar for my sales note, so i can just update input what was sold into the calendar and my sales data will update (profit, gross profit, product quantity, etc) this calendar part is still not automated and probably 3 more automation that i want to make 1. today's date colored column in that calendar 2. a fixed spot column that lets me enter an existing product name and a quantity beside it, and when i press certain button, the product name will be reset and the quantity will be added to the products row at today's date column 3. profit and gross profit count of a day rather than total basically it is a sales report with calender going to right direction rather than the date of each sales data going down, so each product only need 1 row
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Sure, you can reconfigure it to suit. 👍
@RTN925
@RTN925 3 ай бұрын
If you put the year in A1 you can use this formula to list the entire year in one row. =LET(yr,A1,f,DATEVALUE("Jan"&yr),dpy,IF(DAY(EOMONTH(f,1))=29,366,365),SEQUENCE(1,dpy,f)) The variables in the LET formulas are: yr = Current Year (specified in cell A1) f = The date value of 1-Jan of the current year dpy = Days per Year - either 365 or 366 for leap years You will have to format that row to show the date information you want to see. If you use a custom format of "ddd mmm d" the 1st of January will be listed as "Mon Jan 1". Mynda provided the steps to highlight the current day in her video.
@kathmcleod8373
@kathmcleod8373 3 ай бұрын
Hiya i have followed along but had an issue with the dates not copying properly for November i get a value error in the first Sunday and the whole month is blank. Dec has copied fine. No dates passed November show up though. Also my formula worked to white out the dates for January but then the next month i whited out Sunday to thurs as Feb 1 was on Friday and Sunday to thursday whited out for January as well. please help and advise my error. Kath
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
Hard to say without seeing the file. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum Or you can just download my file from the link in the video description.
@Quidisi
@Quidisi 4 ай бұрын
@ 8:30 Today I learned that you can use MONTH() and change the reference value from text, to number, by appending a 1 So "& 1" must be similar to the old, multiply by 1 trick!? I gotta go try this! Great video. Thanks!!!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
So pleased you can make use of this 😉
@cherryh24
@cherryh24 4 күн бұрын
this looks more like a cal for a private use. is there a way to create a cal for business purposes with all the appointments from outlook? there are hundreds of meetings in a business year, i would need to create the yearly cal in excel with all the appointments from the outlook. is there a way to do it please? i use excel very seldom and my knowledge is minimal.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 күн бұрын
If you have Outlook, then I'd leave it all in Outlook.
@cherryh24
@cherryh24 4 күн бұрын
@@MyOnlineTrainingHub I would, but my boss wants to have a yearly overview of all of his appointments... and he is not happy with a paper cal on the wall. I didn't find a way to see a full year in outlook, hence I thought some kind of interactive excel sheet that is pulling data from outlook might do the trick. So far I have spent quite a few days trawling internet for a solution, 0 points.
@jareksvoboda1257
@jareksvoboda1257 4 ай бұрын
Hello I followed your Video, the control button change the year but calendar with dates from sheet Dates are not showing any dates for year higher (such as 2025) or below (2023), it shows only 2024 corectly.... what I did wrong please?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Sounds like you didn't construct your important dates table dynamically. If you download my file you'll see how I created them.
@jareksvoboda1257
@jareksvoboda1257 4 ай бұрын
@@MyOnlineTrainingHub yes you were corect, I did not spotted this one. Range for dates is hard coded by cells. I know I can make it Dynamic with Tables. Need to look how to adjust the Conditional Formating with name of Table (Dates;Birthday). Thanks Mynda
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Conditional formatting can't take table ranges, so the easiest thing is to make the range being referenced by COUNTIF big enough to allow for growth in your table.
@FrankChacha-y3d
@FrankChacha-y3d 3 ай бұрын
I'm using the older version of the Microsoft excel (2019) where the sequence function is not available so what can be the alternative
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
There's no easy alternative. I recommend you download a calendar template from the File tab > New.
@awschamp
@awschamp Ай бұрын
Hi Mynda I’m trying something similar , I have my roaster for my team. When I update leaves for my employees it should update my calendar in excel. Can you help here
@MyOnlineTrainingHub
@MyOnlineTrainingHub Ай бұрын
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@chrisclark6769
@chrisclark6769 Күн бұрын
Hi been following your steps but February returns a value error
@MyOnlineTrainingHub
@MyOnlineTrainingHub Күн бұрын
Not sure why that would be. If you download my file, do you see the same error? If not, compare the formulas to find where your formula has the mistake.
@byteseq
@byteseq 4 ай бұрын
Did this 20 years ago, and better. Made it a calendar where holidays and custom user days are actually pulled into the calendar. We use it to visualize vacation days and similar for my team at work.
@TP014563
@TP014563 4 ай бұрын
That what I was thinking about tbh!
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Great use case!
@GeertDelmulle
@GeertDelmulle 4 ай бұрын
What does that statement mean “pull into the calendar”? (Not a native English speaker)
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
@GeertDelmulle, it means automatically inserted into the calendar, typically from anther system.
@GeertDelmulle
@GeertDelmulle 3 ай бұрын
@@MyOnlineTrainingHub aha, like ‘pulling’ a table into Power Query. Well, we can easily do that here, too, of course. :-)
@davidcoghlan9332
@davidcoghlan9332 Ай бұрын
This is brilliant, but could get the remaining months after January to hind dates which aren’t needed😢
@MyOnlineTrainingHub
@MyOnlineTrainingHub Ай бұрын
There'll be an issue with your conditional formatting. You can download my file and compare my rules to yours to identify the issue.
@runningman682
@runningman682 4 ай бұрын
I downloaded the template but could not get the dates to work. If I entered additional dates they didn't format right in the table and didn't show up in the calendar.
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
You need to edit the conditional formatting range to pick up the new rows in the table if you add more dates.
@DuriBae
@DuriBae 3 ай бұрын
I have applied every step exactly as you, but I get a wrong "Today" conditional formatting. Today is September 7, but August 31 and the S above September 7 (MondaySunday week) show up in the color of today, not the 7 of September 7. What did I do wrong?
@MyOnlineTrainingHub
@MyOnlineTrainingHub 3 ай бұрын
I suspect you didn't select the date cells correctly. You must select the very first date in the calendar through to the last, then apply the conditional format.
@DuriBae
@DuriBae 3 ай бұрын
@@MyOnlineTrainingHub The S was selected as I did not copy over the whole formula for Monday-Sunday weeks (only had that in the first month). After I fixed that, the S is not formatted anymore, but August 31st is still the formatted date. Edit: I started over and now it works somehow. Just how do you get your dates to be dynamic as well? When I move to 2025, it’s all blank…
@omprakashthakur4515
@omprakashthakur4515 4 ай бұрын
How to make an annual calendar based on employee monthly shift change
@MyOnlineTrainingHub
@MyOnlineTrainingHub 4 ай бұрын
Thanks for the suggestion.
7 Excel Features EVERYONE Should Know
13:53
MyOnlineTrainingHub
Рет қаралды 35 М.
Auto create calendar from event data in Excel or Google Sheets
16:56
Сестра обхитрила!
00:17
Victoria Portfolio
Рет қаралды 958 М.
99.9% IMPOSSIBLE
00:24
STORROR
Рет қаралды 31 МЛН
10 Excel Formulas That Will Set You Apart (+Cheat Sheet)
18:04
MyOnlineTrainingHub
Рет қаралды 360 М.
How to Make an Event Calendar in Excel - Part 1
11:53
Spreadsheet Life
Рет қаралды 33 М.
The Excel Functions Almost Everyone Overlooks (Better Than SUMIFS)
10:29
MyOnlineTrainingHub
Рет қаралды 118 М.
Say Goodbye to IF Statements in Excel with SWITCH
7:45
Teacher's Tech
Рет қаралды 597 М.
5 Conditional Formatting Hacks That Will Blow Your Mind
11:16
Mike’s F9 Finance
Рет қаралды 10 М.
The Excel Tool That Does What PivotTables Can’t (File Included)
13:59
MyOnlineTrainingHub
Рет қаралды 212 М.
5 Excel Tools Most Users Never Think to Use (Files Included)
12:34
MyOnlineTrainingHub
Рет қаралды 271 М.
Convert a List of Events into a Graphical Calendar in Excel
9:20
Excel University
Рет қаралды 16 М.