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.
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!
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!
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 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!!!!.
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!!!
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!
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 😊
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.
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.
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.
Glad you discovered a new approach. Hope you can make use of these formulas.
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 :-)
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.
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
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?
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! 😊
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.
@ 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!!!
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, "")
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?
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.
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! 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.
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
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.
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.
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.
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. :)
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
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.
@@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! 😊
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.
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.
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.
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.
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.
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
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.
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!
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
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.
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
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 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.
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 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
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.
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.
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 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
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.
❓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.
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 😊
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!
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 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 😁
That's brilliant - it never ceases to amaze me how you know all these things!!
Thanks so much, Mark!
You are a genius, Mynda. I learn so much from your videos! Thank you.
That's wonderful to hear 🙏
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!
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!
Thank you, your channel is a must, straightforward as usual and very clear, I really appreciate it !
So pleased you find it helpful 🙏
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!
Thanks for this!
My pleasure!
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!
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 calendar, smart vba controls and best of all it updates automatically. Let's try it. Thanks a lot Madeleine.
Thank you!
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.
Thanks for sharing your knowledge. I have another question would you share your color team?
My pleasure. The color theme is Median.
That was very slick and eye appealing too. Thank you
Glad you liked it, Robert!
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.
شكرا You always have wonderful things to explain them to us
Thank you! 😊
Great video! So full of useful information. Thank you for sharing.
Thank you!
Simply Scintillating!!!
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... 😂
SEQUENCE is a new function, so you would have had a much harder time building a calendar like this in the 90s!
Excellent tip. Thx once again. U r an expert. !!
So nice of you 🙏
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 🙏
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.
Thank you so much for this
You're so welcome!
Practical, Useful with simple formulas. Thank you, Mynda,.
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.
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.
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!
Great tip! Thank you ❤
Glad it was helpful!
fantastic, really helpful, many thanks
Great to hear!
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.
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.
Excellent, very useful
Glad to hear that!
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! 🥳
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.
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.
nice work thanks for your efforts
It's my pleasure 🙏
Hi Mynda nice Video, there have been some takeaways for me, for sure.
Great to hear, Roger!
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 💪
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
Loved it 😊
Thank you!
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.
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.
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! 😊
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.
@ 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 😉
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,"🎁","")
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.
OMG I've never used double click format painter. Will try this out tomorrow!
😁great to hear!
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.
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.
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.
thanks
Welcome 😊
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.
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.
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(...) )
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
Brilliant
Thank you!
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.
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.
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. :-)
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.
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.
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.
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.
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 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?
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.
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 😅
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.
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
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.
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!
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!
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.
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!
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
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.
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!
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
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.
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'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.
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.
If you have Outlook, then I'd leave it all in Outlook.
@@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.
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 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
Sure, you can reconfigure it to suit. 👍
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.
Why is not highlighting the days when I did the same thing or the conditional formatting formula
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.
finally someone else who says "yooey"
😁
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?
Sounds like you didn't construct your important dates table dynamically. If you download my file you'll see how I created them.
@@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
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.
I could not continue after 5:38, my conditional formatting looks different.
Please download the example file and inspect and compare my formats to yours to see where the issue is.
How should I modify your formula if I want Monday to be the first day of the week?
I show that at the end of the video - see here 11:25. You can also download the example file to see it
@@MyOnlineTrainingHub thanks
How to velukup image in xl
Please see this tutorial: www.myonlinetraininghub.com/lookup-pictures-in-excel