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.
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.
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.
God knows how many times I would have seen this video and every time I watch I am truly amazed the way in which you handle excel so swiftly as well as explain in a very simple way .... I followed your video and finally today I have created my calendar with Birthdays, Closed Holidays and Restricted Holidays with conditional formatting. Thanks a lot
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!
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!
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!!!
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!
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.
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!!!
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!!!!.
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?
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!
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.
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 😊
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!!!
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.
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.
My goodness, this takes me back... ±1994/5 the city I worked for kitted all the office staff with PCs with Word & Excel.... For some reason someone wanted a 'perpetual' - almost identical to what you've done here... The IT guys said it couldn't be done... Always up for a challenge, I created one like this... but not having then (or not knowing about it) the SEQUENCE function I had to improvise. It was also my first venture into conditional formatting... Everyone (except IT 😭) thought I was a flipping genius... I didn't get no raise nor promotion though... 😂
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.
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.
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.
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.
@@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
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 :-)
Thank you for sharing this video. For some reason my holidays dates only applied to the page I worked on, it did not carry to other years like yours did. Can you tell me what I did wrong? Thanks!
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.
@ 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!!!
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! 😊
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.
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.
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?
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!
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.
@@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.
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.
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.
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, "")
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?
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.
Great video, Mynda. You might enhance by using the INDIRECT function to use table references in the conditional formatting rules. That way, the formulas will pick up additions to the tables. :)
Please download my example file from the link in the video description to see how I constructed my key dates table so it updates when you change the year.
@@MyOnlineTrainingHub The example show me the same you have on the video that i followed and without any luck, unless I am not seeing which download you are talking about. Do you have a video of how to construct key days tables? Thank you again for your help
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?
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.
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.
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.
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
Thanks so much, just built it - wee question, can you actually not just highlight single dates from a list but all the dates between a "start date" and an "End date"? Until now I had built a Gantt chart for my projects, but showing them in a calendar like this nicely in one page would be so much more handy ... But I cannot think of a formula for the conditional formatting that would show that ...
@@MyOnlineTrainingHub ah, that's the gantt chart, I am already using something similar ... I was rather hoping for some "shortcut" conditional formatting tips to show the same but in an actual "calendar" look rather than a timeline look. But hey, I just need to get those braincells going and work it out myself ... that's the best way to figure something out, isn't it? ;)
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 ?
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.
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.
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
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.
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.
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 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! 😊
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.
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.
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.
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.
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.
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!
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.
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.
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.
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.
Is there a way by which i can add my tasks when i click on a date? For example: if I click on 25 Jan, it opens a window on the right where i can enter my tasks for the day!
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 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
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?)
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.
Hi, i say this and wanted to try on my own. However, i face a few issues which i hope you will be able to help resolve. (1) I need it to be only weekdays, start of week to be Mon, can this be done? If yes can please help with the formula? (2) When i use the conditional formatting to highlight the holiday dates, those dates that was made to be "invisible" gets highlighted as such making it very confusing. Please help to advice how can this error be avoided? Thank you so much for your kind attention.
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.
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.
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
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.
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.
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.
❓What calendar feature would save you the most time? Tell me below.
Learn Excel with my courses: bit.ly/calendar24courses
How to groups of dates such as annual leave or tasks over multiple days for a team. That would be so great
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.
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.
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.
God knows how many times I would have seen this video and every time I watch I am truly amazed the way in which you handle excel so swiftly as well as explain in a very simple way .... I followed your video and finally today I have created my calendar with Birthdays, Closed Holidays and Restricted Holidays with conditional formatting. Thanks a lot
You’re welcome! I’m so glad it worked for you!
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!
Great to hear you discovered something new!
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!
Thanks so much! Glad you enjoyed it 😊
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!!!
Glad it will be useful 😊
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!
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.
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!!!
So glad you can make use of it!
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!!!!.
So pleased this will be helpful to you 😁
You are a genius, Mynda. I learn so much from your videos! Thank you.
That's wonderful to hear 🙏
That's brilliant - it never ceases to amaze me how you know all these things!!
Thanks so much, Mark!
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?
😁 thank you! Unfortunately, I am not calm when it comes to homework either!
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!
So pleased this was helpful!
Thank you, your channel is a must, straightforward as usual and very clear, I really appreciate it !
So pleased you find it helpful 🙏
Excellent tip. Thx once again. U r an expert. !!
So nice of you 🙏
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.
Yes, I did consider that, but decided against it in the end. Thanks for mentioning it as an alternative though 🙏
@@MyOnlineTrainingHub Nice one, what were the reasons not to?
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 😅
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 😊
So glad it was helpful!
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!!!
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.
Thank you. I will check them out as it adds a lot to your calendar.
Great calendar, smart vba controls and best of all it updates automatically. Let's try it. Thanks a lot Madeleine.
Thank you!
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.
I done something very similar. Only my formula are longer and more complicated? Yours look a lot simpler.
Interesting.🤩🤩😍
Glad you discovered a new approach. Hope you can make use of these formulas.
Thanks for this!
My pleasure!
My goodness, this takes me back...
±1994/5 the city I worked for kitted all the office staff with PCs with Word & Excel.... For some reason someone wanted a 'perpetual' - almost identical to what you've done here...
The IT guys said it couldn't be done...
Always up for a challenge, I created one like this... but not having then (or not knowing about it) the SEQUENCE function I had to improvise. It was also my first venture into conditional formatting...
Everyone (except IT 😭) thought I was a flipping genius...
I didn't get no raise nor promotion though... 😂
SEQUENCE is a new function, so you would have had a much harder time building a calendar like this in the 90s!
Simply Scintillating!!!
Thank you!
fantastic, really helpful, many thanks
Great to hear!
Practical, Useful with simple formulas. Thank you, Mynda,.
Thank you!
That was very slick and eye appealing too. Thank you
Glad you liked it, Robert!
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.
Great idea 🙏
شكرا You always have wonderful things to explain them to us
Thank you! 😊
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.
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.
Great video! So full of useful information. Thank you for sharing.
Thank you!
Thanks for sharing your knowledge. I have another question would you share your color team?
My pleasure. The color theme is Median.
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.
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.
@@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
So cool thanks Mynda!
Cheers, Chris! Thanks for watching.
Great video ! Learnt so much, thank you ! A question - how do I add the week number to the calendar ?
You can use the WEEKDAY function to return the week number in a column to the left of each month's dates.
Excellent stuff thanks!
Haven't seen this SEQUENCE function yet. I would probably have used some old-fashioned matrix formula to get there...
Great you discovered something new! Thanks for watching.
Love it! Now I’m just wondering how to best add in other key dates/ periods like each quarter and half year, etc.
Awesome to hear!
Thank you so much for this
You're so welcome!
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 :-)
Ah, yes good point for non-English versions of Excel.
Brilliant🤩
Thanks so much!
Thank you for sharing this video. For some reason my holidays dates only applied to the page I worked on, it did not carry to other years like yours did. Can you tell me what I did wrong? Thanks!
Download my file and look at how I created my key dates table formulas.
Excellent, very useful
Glad to hear that!
Amazing, can you please do it adding calendar week numbers
Thank you! You can add week numbers using the WEEKDAY function in the column to the left of each month.
Great tip! Thank you ❤
Glad it was helpful!
Really useful video - thank you! I am guessing that there is no way to deal with school holidays which change on an annual basis?
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.
Hi Mynda nice Video, there have been some takeaways for me, for sure.
Great to hear, Roger!
@ 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!!!
So pleased you can make use of this 😉
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! 😊
Spectacular!
Thank you!
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.
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.
Glad you found this video helpful. Well done figuring out the dates issue 💪
OMG I've never used double click format painter. Will try this out tomorrow!
😁great to hear!
nice work thanks for your efforts
It's my pleasure 🙏
thanks
Welcome 😊
Another great video! Is there a way to highlight a date range, for example to show employee annual leave instead of birthdays?
Woo hoo, I worked it out myself using the AND function - =AND(B7>=$AK$5,B7
Well done! 🥳
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?
You'd have to modify the conditional formatting rule to look at ranges.
@@MyOnlineTrainingHub thank you. I'll have a play around with it.
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?
Yes, you could insert a comment for the relevant date.
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!
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.
@@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.
You can download me completed file from the link in the video description and see all the formulas.
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?
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.
Loved it 😊
Thank you!
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.
That what I was thinking about tbh!
Great use case!
What does that statement mean “pull into the calendar”?
(Not a native English speaker)
@GeertDelmulle, it means automatically inserted into the calendar, typically from anther system.
@@MyOnlineTrainingHub aha, like ‘pulling’ a table into Power Query.
Well, we can easily do that here, too, of course. :-)
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?
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, "")
@@MyOnlineTrainingHub Great - thanks. I've done this in a slightly clunky way =IF(COUNTIF(Birthdays[Month],MONTH(Z5&1))>0,"🎁","")
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?
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.
Great !! Thanks
I have one problem: when I add more items to the table, the date is not calculated.
What can I do about that ??
Download my example file and you can see how I constructed the key dates table.
Great video, Mynda. You might enhance by using the INDIRECT function to use table references in the conditional formatting rules. That way, the formulas will pick up additions to the tables. :)
Yes, good idea. Thanks for sharing.
Hello, great video. I followed your method step by step, however i just cant get my holidays to update or roll with the years, what am i doing right?
Please download my example file from the link in the video description to see how I constructed my key dates table so it updates when you change the year.
@@MyOnlineTrainingHub The example show me the same you have on the video that i followed and without any luck, unless I am not seeing which download you are talking about. Do you have a video of how to construct key days tables? Thank you again for your help
are you talking about downloading the excel Tables for a charge?
No, I'm talking about downloading the example file for this video and looking at the Dates sheet to see my formulas.
Hi Mynda, another great lesson, thanks. Can this, or similar be done in Excel 2016 = without sequence function?
Not easily. I recommend getting a calendar template from the File tab > New, and download a template.
@@MyOnlineTrainingHub I didn't think so. Thanks, template it is.
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?
You could use conditional formatting to 'reveal' a reminder when certain conditions are met, so maybe this is an option for you.
I am new to this type calander , kindly let me know where and for what we can use it ....
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.
Brilliant
Thank you!
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!
Yes, just wrap the formula in TRANSPOSE e.g. =TRANSPOSE( SEQUENCE(...) )
Can we use this as a drop down calendar?
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.
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.
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
Thanks so much, just built it - wee question, can you actually not just highlight single dates from a list but all the dates between a "start date" and an "End date"? Until now I had built a Gantt chart for my projects, but showing them in a calendar like this nicely in one page would be so much more handy ... But I cannot think of a formula for the conditional formatting that would show that ...
Yes you can! See this project management dashboard tutorial: www.myonlinetraininghub.com/excel-project-management-dashboard
@@MyOnlineTrainingHub ah, that's the gantt chart, I am already using something similar ... I was rather hoping for some "shortcut" conditional formatting tips to show the same but in an actual "calendar" look rather than a timeline look. But hey, I just need to get those braincells going and work it out myself ... that's the best way to figure something out, isn't it? ;)
Parabéns pelo vídeo. Você é espetacular! Parabéns.
Obrigado!
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 ?
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.
Great! Do you know a way with VBA to send events from excel straight to Google Calendar, is it possible?
No, it's not possible because Google Calendar doesn't recognise VBA. I'm not aware of a way to sync it, sorry.
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.
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.
Thanks. But what about the weeknumbers?
You can add another formula with WEEKDAY to insert the week numbers to the left of each month's dates.
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
I don't have a Mac, but good to know the Windows version runs well on Parallels.
Whats the practical use/s for this?
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.
How did you auto fill the calendar months into the format of 8 cells by 8 cells?
The SEQUENCE function is a dynamic array formula that spills the results: www.myonlinetraininghub.com/excel-functions/excel-sequence-function
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.
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.
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.
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.
Very nice!
Boxing day 🤣
Yep, we get two days off for Christmas. 💪😁
@@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! 😊
Boxing day is a recovery day for the Christmas indulgence 😅
What will the formatting be if there’s two things happening on the same date?
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.
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.
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.
Is there a way to add dynamic financial week numbers to each week?
Sure, I guess you could do this in an extra column for each month.
Hello, thank you for your work. My excel doesn't have the sequence formula. what should i do?
Update to a newer version
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.
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.
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.
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!
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.
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.
I see. Makes sense. Thank you again!
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.
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.
@ I used your formula; I’ll go back and check it again ( for the nth time!).
Is there a way by which i can add my tasks when i click on a date? For example: if I click on 25 Jan, it opens a window on the right where i can enter my tasks for the day!
You could create a user form with VBA to input your tasks.
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 ?
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.
@@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
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?)
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.
@@MyOnlineTrainingHub thank you! 🙂
@@MyOnlineTrainingHub The video doesn't show how to link the special dates to the year. Can you describe how to do that?
Never mind, downloaded the spreadsheet. Thanks!
Hi, i say this and wanted to try on my own. However, i face a few issues which i hope you will be able to help resolve. (1) I need it to be only weekdays, start of week to be Mon, can this be done? If yes can please help with the formula? (2) When i use the conditional formatting to highlight the holiday dates, those dates that was made to be "invisible" gets highlighted as such making it very confusing. Please help to advice how can this error be avoided? Thank you so much for your kind attention.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
This is brilliant, but could get the remaining months after January to hind dates which aren’t needed😢
There'll be an issue with your conditional formatting. You can download my file and compare my rules to yours to identify the issue.
how you made the video - first sheet? video?
Not sure that you mean, sorry.
@@MyOnlineTrainingHub I have sold the problem. Now I have a new one. Unknown synchronized holiday and birthdays with the year 2025, 2026 ...
Do you have an e-mail so I can send you my calander and you can see what is wrong with synchronization of holidays and birthdays?
I'm using the older version of the Microsoft excel (2019) where the sequence function is not available so what can be the alternative
There's no easy alternative. I recommend you download a calendar template from the File tab > New.
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.
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.
@@MyOnlineTrainingHub Good idea....thank you for the suggestion!
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
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I got lost at 3:00 - how do you get the months to autofill, and a given spacing, from just typing January to April...?
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.
@@MyOnlineTrainingHubThanks for clearing up that confusion...
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.
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.