❓What calendar feature would save you the most time? Tell me below. Learn Excel with my courses: bit.ly/calendar24courses
@stephenbaxter48334 ай бұрын
How to groups of dates such as annual leave or tasks over multiple days for a team. That would be so great
@ThomTydeman3 ай бұрын
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.
@petemininni56713 ай бұрын
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.
@xavierbourguignon372203 ай бұрын
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.
@ilhanschen2 ай бұрын
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!
@MyOnlineTrainingHub2 ай бұрын
Thanks so much! Glad you enjoyed it 😊
@andre65434 ай бұрын
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!
@MyOnlineTrainingHub4 ай бұрын
Great to hear you discovered something new!
@rw8814 ай бұрын
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!
@MyOnlineTrainingHub4 ай бұрын
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.
@markclifton95243 ай бұрын
That's brilliant - it never ceases to amaze me how you know all these things!!
@MyOnlineTrainingHub3 ай бұрын
Thanks so much, Mark!
@KittKatPawz4 ай бұрын
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!!!
@MyOnlineTrainingHub4 ай бұрын
So glad you can make use of it!
@ThomTydeman3 ай бұрын
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?
@MyOnlineTrainingHub3 ай бұрын
😁 thank you! Unfortunately, I am not calm when it comes to homework either!
@brookeanderson6623 ай бұрын
You are a genius, Mynda. I learn so much from your videos! Thank you.
@MyOnlineTrainingHub3 ай бұрын
That's wonderful to hear 🙏
@jnev7854 ай бұрын
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!!!!.
@MyOnlineTrainingHub4 ай бұрын
So pleased this will be helpful to you 😁
@hanozt3 ай бұрын
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!!!
@MyOnlineTrainingHub3 ай бұрын
Glad it will be useful 😊
@CaptainCap-p8h7 сағат бұрын
Thanks for sharing your knowledge. I have another question would you share your color team?
@learnitinstructor57923 ай бұрын
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!
@MyOnlineTrainingHub3 ай бұрын
So pleased this was helpful!
@malikastar9265Ай бұрын
Thank you, your channel is a must, straightforward as usual and very clear, I really appreciate it !
@MyOnlineTrainingHubАй бұрын
So pleased you find it helpful 🙏
@hemant7493 ай бұрын
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.
@brandankeel18553 ай бұрын
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 😊
@MyOnlineTrainingHub3 ай бұрын
So glad it was helpful!
@kiwim3p5874 ай бұрын
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.
@MyOnlineTrainingHub4 ай бұрын
Yes, I did consider that, but decided against it in the end. Thanks for mentioning it as an alternative though 🙏
@kiwim3p5874 ай бұрын
@@MyOnlineTrainingHub Nice one, what were the reasons not to?
@MyOnlineTrainingHub4 ай бұрын
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 😅
@sahralsahri71804 ай бұрын
Great calendar, smart vba controls and best of all it updates automatically. Let's try it. Thanks a lot Madeleine.
@MyOnlineTrainingHub4 ай бұрын
Thank you!
@jcwillette4 ай бұрын
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!!!
@MyOnlineTrainingHub4 ай бұрын
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.
@jcwillette4 ай бұрын
Thank you. I will check them out as it adds a lot to your calendar.
@nadzirahfarhanah44703 ай бұрын
Great video ! Learnt so much, thank you ! A question - how do I add the week number to the calendar ?
@MyOnlineTrainingHub3 ай бұрын
You can use the WEEKDAY function to return the week number in a column to the left of each month's dates.
@andrewo.a53813 ай бұрын
Simply Scintillating!!!
@MyOnlineTrainingHub3 ай бұрын
Thank you!
@chrism90374 ай бұрын
So cool thanks Mynda!
@MyOnlineTrainingHub4 ай бұрын
Cheers, Chris! Thanks for watching.
@daleanderson52584 ай бұрын
Great video! So full of useful information. Thank you for sharing.
@MyOnlineTrainingHub4 ай бұрын
Thank you!
@Robert84553 ай бұрын
That was very slick and eye appealing too. Thank you
@MyOnlineTrainingHub3 ай бұрын
Glad you liked it, Robert!
@sbatsia4 ай бұрын
Practical, Useful with simple formulas. Thank you, Mynda,.
@MyOnlineTrainingHub4 ай бұрын
Thank you!
@taypac17754 ай бұрын
Love it! Now I’m just wondering how to best add in other key dates/ periods like each quarter and half year, etc.
@MyOnlineTrainingHub4 ай бұрын
Awesome to hear!
@theoneandonlymattsmith2 ай бұрын
Really useful video - thank you! I am guessing that there is no way to deal with school holidays which change on an annual basis?
@MyOnlineTrainingHub2 ай бұрын
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.
@-flabber66634 ай бұрын
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.
@MyOnlineTrainingHub4 ай бұрын
Great idea 🙏
@NagyElAlfy-v7v4 ай бұрын
fantastic, really helpful, many thanks
@MyOnlineTrainingHub4 ай бұрын
Great to hear!
@JustAnotherPassenger4 ай бұрын
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.
@MyOnlineTrainingHub4 ай бұрын
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.
@DingusBatus4 ай бұрын
I done something very similar. Only my formula are longer and more complicated? Yours look a lot simpler. Interesting.🤩🤩😍
@MyOnlineTrainingHub4 ай бұрын
Glad you discovered a new approach. Hope you can make use of these formulas.
@martinargimon7304 ай бұрын
Excellent tip. Thx once again. U r an expert. !!
@MyOnlineTrainingHub4 ай бұрын
So nice of you 🙏
@missamo803 ай бұрын
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?
@MyOnlineTrainingHub3 ай бұрын
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.
@izzybizzybee724 ай бұрын
Another great video! Is there a way to highlight a date range, for example to show employee annual leave instead of birthdays?
@izzybizzybee723 ай бұрын
Woo hoo, I worked it out myself using the AND function - =AND(B7>=$AK$5,B7
@MyOnlineTrainingHub3 ай бұрын
Well done! 🥳
@-massudin.74883 ай бұрын
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?
@MyOnlineTrainingHub3 ай бұрын
Yes, you could insert a comment for the relevant date.
@NagyElAlfy-v7v2 ай бұрын
شكرا You always have wonderful things to explain them to us
@MyOnlineTrainingHub2 ай бұрын
Thank you! 😊
@frsnkph2 ай бұрын
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?
@MyOnlineTrainingHub2 ай бұрын
You'd have to modify the conditional formatting rule to look at ranges.
@frsnkph2 ай бұрын
@@MyOnlineTrainingHub thank you. I'll have a play around with it.
@Oldan-Enuar3 ай бұрын
Amazing, can you please do it adding calendar week numbers
@MyOnlineTrainingHub3 ай бұрын
Thank you! You can add week numbers using the WEEKDAY function in the column to the left of each month.
@heinzthedutch4 ай бұрын
Excellent stuff thanks! Haven't seen this SEQUENCE function yet. I would probably have used some old-fashioned matrix formula to get there...
@MyOnlineTrainingHub4 ай бұрын
Great you discovered something new! Thanks for watching.
@harjith.d.bubber3 ай бұрын
Thank you so much for this
@MyOnlineTrainingHub3 ай бұрын
You're so welcome!
@yesiunderstand60444 ай бұрын
Excellent, very useful
@MyOnlineTrainingHub4 ай бұрын
Glad to hear that!
@aeonturnip24 ай бұрын
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?
@MyOnlineTrainingHub4 ай бұрын
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, "")
@aeonturnip24 ай бұрын
@@MyOnlineTrainingHub Great - thanks. I've done this in a slightly clunky way =IF(COUNTIF(Birthdays[Month],MONTH(Z5&1))>0,"🎁","")
@jorgepaz224 ай бұрын
Great tip! Thank you ❤
@MyOnlineTrainingHub4 ай бұрын
Glad it was helpful!
@kenfong85803 ай бұрын
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.
@MyOnlineTrainingHub3 ай бұрын
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-g7b23 күн бұрын
@@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
@juanbon59963 ай бұрын
Spectacular!
@MyOnlineTrainingHub3 ай бұрын
Thank you!
@RogerStocker4 ай бұрын
Hi Mynda nice Video, there have been some takeaways for me, for sure.
@MyOnlineTrainingHub4 ай бұрын
Great to hear, Roger!
@sidbaishya172 ай бұрын
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.
@sidbaishya172 ай бұрын
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.
@MyOnlineTrainingHub2 ай бұрын
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 :-)
@MyOnlineTrainingHub4 ай бұрын
Ah, yes good point for non-English versions of Excel.
@6ugggg4 ай бұрын
nice work thanks for your efforts
@MyOnlineTrainingHub4 ай бұрын
It's my pleasure 🙏
@ChristinaHarper-m8g2 ай бұрын
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!
@MyOnlineTrainingHub2 ай бұрын
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-m8g2 ай бұрын
@@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.
@MyOnlineTrainingHub2 ай бұрын
You can download me completed file from the link in the video description and see all the formulas.
@Tradingnoobs2 ай бұрын
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?
@MyOnlineTrainingHub2 ай бұрын
You could use conditional formatting to 'reveal' a reminder when certain conditions are met, so maybe this is an option for you.
@laurasol45653 ай бұрын
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!
@MyOnlineTrainingHub3 ай бұрын
Yes, just wrap the formula in TRANSPOSE e.g. =TRANSPOSE( SEQUENCE(...) )
@jimpoland33024 ай бұрын
Hi Mynda, another great lesson, thanks. Can this, or similar be done in Excel 2016 = without sequence function?
@MyOnlineTrainingHub4 ай бұрын
Not easily. I recommend getting a calendar template from the File tab > New, and download a template.
@jimpoland33023 ай бұрын
@@MyOnlineTrainingHub I didn't think so. Thanks, template it is.
@janicedarroch47472 ай бұрын
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?
@MyOnlineTrainingHub2 ай бұрын
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.
@BoyvanHeugten4 ай бұрын
OMG I've never used double click format painter. Will try this out tomorrow!
@MyOnlineTrainingHub4 ай бұрын
😁great to hear!
@PaulEBrownbill3 ай бұрын
thanks
@MyOnlineTrainingHub3 ай бұрын
Welcome 😊
@al3xj4 ай бұрын
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
@MyOnlineTrainingHub4 ай бұрын
I don't have a Mac, but good to know the Windows version runs well on Parallels.
@wesw88213 ай бұрын
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.
@MyOnlineTrainingHub3 ай бұрын
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.
@AdvisorsDesk4 ай бұрын
Loved it 😊
@MyOnlineTrainingHub4 ай бұрын
Thank you!
@YamaHoyes2 ай бұрын
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.
@MyOnlineTrainingHub2 ай бұрын
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.
@glennhargett22432 ай бұрын
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.
@MyOnlineTrainingHub2 ай бұрын
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
@vishaalhassan51222 ай бұрын
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 ?
@MyOnlineTrainingHub2 ай бұрын
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.
@kokabkhalid31812 ай бұрын
I am new to this type calander , kindly let me know where and for what we can use it ....
@MyOnlineTrainingHub2 ай бұрын
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.
@MrTubertub4 ай бұрын
Thanks. But what about the weeknumbers?
@MyOnlineTrainingHub4 ай бұрын
You can add another formula with WEEKDAY to insert the week numbers to the left of each month's dates.
@jfgabito19873 ай бұрын
Can we use this as a drop down calendar?
@MyOnlineTrainingHub3 ай бұрын
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.
@UghUgh12344 ай бұрын
What will the formatting be if there’s two things happening on the same date?
@tsalb964 ай бұрын
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.
@TheMarkGross4 ай бұрын
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.
@MyOnlineTrainingHub4 ай бұрын
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.
@Roots2223 ай бұрын
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?)
@MyOnlineTrainingHub3 ай бұрын
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.
@Roots2223 ай бұрын
@@MyOnlineTrainingHub thank you! 🙂
@zakhall76803 ай бұрын
@@MyOnlineTrainingHub The video doesn't show how to link the special dates to the year. Can you describe how to do that?
@zakhall76803 ай бұрын
Never mind, downloaded the spreadsheet. Thanks!
@datingdave13104 ай бұрын
I got lost at 3:00 - how do you get the months to autofill, and a given spacing, from just typing January to April...?
@MyOnlineTrainingHub4 ай бұрын
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.
@datingdave13104 ай бұрын
@@MyOnlineTrainingHubThanks for clearing up that confusion...
@jnev7854 ай бұрын
Is there a way to add dynamic financial week numbers to each week?
@MyOnlineTrainingHub4 ай бұрын
Sure, I guess you could do this in an extra column for each month.
@ayyappanusa3 ай бұрын
Brilliant
@MyOnlineTrainingHub3 ай бұрын
Thank you!
@navaarunbhattacherjee45113 ай бұрын
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! 😊
@potatocraftd66293 ай бұрын
Whats the practical use/s for this?
@MyOnlineTrainingHub3 ай бұрын
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.
@biserkabajecleskovar116723 сағат бұрын
how you made the video - first sheet? video?
@MyOnlineTrainingHub11 сағат бұрын
Not sure that you mean, sorry.
@AssinaturaUnica21 сағат бұрын
Parabéns pelo vídeo. Você é espetacular! Parabéns.
@MyOnlineTrainingHub10 сағат бұрын
Obrigado!
@joewilson45994 ай бұрын
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.
@MyOnlineTrainingHub4 ай бұрын
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.
@kevinmc2000YT3 ай бұрын
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!
@johnjacobjinglehimerschmid35554 ай бұрын
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.
@MyOnlineTrainingHub4 ай бұрын
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.
@pierrejeames3 ай бұрын
Hello, thank you for your work. My excel doesn't have the sequence formula. what should i do?
@potatocraftd66293 ай бұрын
Update to a newer version
@MyOnlineTrainingHub3 ай бұрын
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Ай бұрын
I could not continue after 5:38, my conditional formatting looks different.
@MyOnlineTrainingHubАй бұрын
Please download the example file and inspect and compare my formats to yours to see where the issue is.
@ScienceGuyRoy4 ай бұрын
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.
@MyOnlineTrainingHub4 ай бұрын
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.
@ScienceGuyRoy4 ай бұрын
I see. Makes sense. Thank you again!
@gregmitchell16713 ай бұрын
How did you auto fill the calendar months into the format of 8 cells by 8 cells?
@MyOnlineTrainingHub3 ай бұрын
The SEQUENCE function is a dynamic array formula that spills the results: www.myonlinetraininghub.com/excel-functions/excel-sequence-function
@johngamiles25414 ай бұрын
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.
@MyOnlineTrainingHub4 ай бұрын
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.
@johngamiles25414 ай бұрын
@@MyOnlineTrainingHub Good idea....thank you for the suggestion!
@火盧4 ай бұрын
Very nice! Boxing day 🤣
@MyOnlineTrainingHub4 ай бұрын
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! 😊
@MyOnlineTrainingHub4 ай бұрын
Boxing day is a recovery day for the Christmas indulgence 😅
@danchristiansen88534 ай бұрын
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.
@MyOnlineTrainingHub4 ай бұрын
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-juice4 ай бұрын
finally someone else who says "yooey"
@MyOnlineTrainingHub4 ай бұрын
😁
@balazserdesz4 ай бұрын
How should I modify your formula if I want Monday to be the first day of the week?
@MyOnlineTrainingHub4 ай бұрын
I show that at the end of the video - see here 11:25. You can also download the example file to see it
@balazserdesz4 ай бұрын
@@MyOnlineTrainingHub thanks
@hanozt3 ай бұрын
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 ?
@MyOnlineTrainingHub3 ай бұрын
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.
@hanozt3 ай бұрын
@@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
@bobburke87274 күн бұрын
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.
@MyOnlineTrainingHub4 күн бұрын
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.
@bobburke87274 күн бұрын
@ I used your formula; I’ll go back and check it again ( for the nth time!).
@FKKTB3 ай бұрын
Why is not highlighting the days when I did the same thing or the conditional formatting formula
@MyOnlineTrainingHub3 ай бұрын
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.
@telstraroenssel37013 ай бұрын
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
@MyOnlineTrainingHub3 ай бұрын
Sure, you can reconfigure it to suit. 👍
@RTN9253 ай бұрын
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.
@kathmcleod83733 ай бұрын
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
@MyOnlineTrainingHub3 ай бұрын
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.
@Quidisi4 ай бұрын
@ 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!!!
@MyOnlineTrainingHub4 ай бұрын
So pleased you can make use of this 😉
@cherryh244 күн бұрын
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.
@MyOnlineTrainingHub4 күн бұрын
If you have Outlook, then I'd leave it all in Outlook.
@cherryh244 күн бұрын
@@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.
@jareksvoboda12574 ай бұрын
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?
@MyOnlineTrainingHub4 ай бұрын
Sounds like you didn't construct your important dates table dynamically. If you download my file you'll see how I created them.
@jareksvoboda12574 ай бұрын
@@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
@MyOnlineTrainingHub4 ай бұрын
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-y3d3 ай бұрын
I'm using the older version of the Microsoft excel (2019) where the sequence function is not available so what can be the alternative
@MyOnlineTrainingHub3 ай бұрын
There's no easy alternative. I recommend you download a calendar template from the File tab > New.
@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Ай бұрын
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@chrisclark6769Күн бұрын
Hi been following your steps but February returns a value error
@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.
@byteseq4 ай бұрын
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.
@TP0145634 ай бұрын
That what I was thinking about tbh!
@MyOnlineTrainingHub4 ай бұрын
Great use case!
@GeertDelmulle4 ай бұрын
What does that statement mean “pull into the calendar”? (Not a native English speaker)
@MyOnlineTrainingHub4 ай бұрын
@GeertDelmulle, it means automatically inserted into the calendar, typically from anther system.
@GeertDelmulle3 ай бұрын
@@MyOnlineTrainingHub aha, like ‘pulling’ a table into Power Query. Well, we can easily do that here, too, of course. :-)
@davidcoghlan9332Ай бұрын
This is brilliant, but could get the remaining months after January to hind dates which aren’t needed😢
@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.
@runningman6824 ай бұрын
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.
@MyOnlineTrainingHub4 ай бұрын
You need to edit the conditional formatting range to pick up the new rows in the table if you add more dates.
@DuriBae3 ай бұрын
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?
@MyOnlineTrainingHub3 ай бұрын
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.
@DuriBae3 ай бұрын
@@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…
@omprakashthakur45154 ай бұрын
How to make an annual calendar based on employee monthly shift change