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.
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 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!!!!.
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!
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!
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.
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.
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.
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 😊
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.
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.
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.
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.
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?
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.
2 หลายเดือนก่อน
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 :-)
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.
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.
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.
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.
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.
@ 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!!!
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.
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, "")
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
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
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! 😊
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.
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. :)
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.
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.
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.
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.
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!
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.
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.
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
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.
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.
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.
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.
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.
@@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! 😊
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.
I have applied every step exactly as you, but I get a wrong "Today" conditional formatting. Today is September 7, but August 31 and the S above September 7 (MondaySunday week) show up in the color of today, not the 7 of September 7. What did I do wrong?
I suspect you didn't select the date cells correctly. You must select the very first date in the calendar through to the last, then apply the conditional format.
@@MyOnlineTrainingHub The S was selected as I did not copy over the whole formula for Monday-Sunday weeks (only had that in the first month). After I fixed that, the S is not formatted anymore, but August 31st is still the formatted date. Edit: I started over and now it works somehow. Just how do you get your dates to be dynamic as well? When I move to 2025, it’s all blank…
I downloaded the template but could not get the dates to work. If I entered additional dates they didn't format right in the table and didn't show up in the calendar.
That's an error because you have a version of Excel that doesn't have the SEQUENCE function. You can find a calendar that works with your version of Excel via the Excel File tab > New and search for "Any year calendar with holidays”.
Hi Thanks for sharing, however Im using excel 2019 and when formatting the spin button I dont have the "Control" section in format control. Ive entered details in properties windows but still does not work I need to Embed in vba eg for Spin Button Change Private Sub SpinButton1_Change() End Sub Please can you help
Everything looks the same but when I roll over to a new year the dates change and the extra days are whited out but the holidays and birthdays do not show up. What did I do wrong?
Have you changed the dates in the Dates sheet to reflect the new year (i.e. Myrna used 4/7/2024 and if you roll over to 2025 the date would need to be listed as 4/7/2025 to show up). Also the formula for conditional formatting needs to be extended to include any new dates entered.
If you look at my example file you'll see that I construct the birthday dates with the DATE formula that takes the day and month from the table columns and the year from the calendar.
😮 Does anyone really use Excel to keep track of events using a manually made calendar like this? Why? Are there advantages to this approach? Please enlighten me.
I don't think that anybody actually uses this on say a daily basis. But I can see an office using such a calendar ONCE a year to print up say the new Fiscal Weeks of the next year. Other than that I can't see much of a daily use reason.
I do! I use calendars like this to have an overview of certain events in the year rather than using Outlook that's cluttered with all my appointments and reminders. I've been using a calendar like this for years. Each year, I roll it over to the new year.
@@MyOnlineTrainingHub I use a Calendar in my Excel spreadsheets for all of my financial and home management related stuff. I use it to plan out my year such as counting paychecks and reoccurring events to help predict income and expenses in my personal life.
@@MyOnlineTrainingHubfor the next video, could you consider demonstrating the uses of your Excel-based calendar? I’d like to see to get some ideas on how this may be useful.
❓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.
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 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 😁
Thank you, your channel is a must, straightforward as usual and very clear, I really appreciate it !
So pleased you find it helpful 🙏
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 😊
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!
You are a genius, Mynda. I learn so much from your videos! Thank you.
That's wonderful to hear 🙏
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!
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.
That's brilliant - it never ceases to amaze me how you know all these things!!
Thanks so much, Mark!
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.
Great calendar, smart vba controls and best of all it updates automatically. Let's try it. Thanks a lot Madeleine.
Thank you!
شكرا You always have wonderful things to explain them to us
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.
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!
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.
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!
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! 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 🙏
That was very slick and eye appealing too. Thank you
Glad you liked it, Robert!
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.
Practical, Useful with simple formulas. Thank you, Mynda,.
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.
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 😅
OMG I've never used double click format painter. Will try this out tomorrow!
😁great to hear!
Great video! So full of useful information. Thank you for sharing.
Thank you!
Excellent tip. Thx once again. U r an expert. !!
So nice of you 🙏
Simply Scintillating!!!
Thank you!
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.
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.
Hi Mynda nice Video, there have been some takeaways for me, for sure.
Great to hear, Roger!
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.
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.
fantastic, really helpful, many thanks
Great to hear!
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! 🥳
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.
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.
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.
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. :-)
So cool thanks Mynda!
Cheers, Chris! Thanks for watching.
Thank you so much for this
You're so welcome!
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 💪
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 tip! Thank you ❤
Glad it was helpful!
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.
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.
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(...) )
Excellent, very useful
Glad to hear that!
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.
@ 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 😉
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.
nice work thanks for your efforts
It's my pleasure 🙏
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,"🎁","")
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.
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
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.
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.
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! 😊
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.
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.
thanks
Welcome 😊
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.
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!
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.
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.
Spectacular!
Thank you!
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.
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.
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.
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!
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.
Loved it 😊
Thank you!
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!
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.
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
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.
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!
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.
Brilliant
Thank you!
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.
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
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.
Even better if you made the first month variable - based on today() - so you always see the next 12 months
Nice idea 👍
How to make an annual calendar based on employee monthly shift change
Thanks for the suggestion.
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.
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 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...
I have applied every step exactly as you, but I get a wrong "Today" conditional formatting. Today is September 7, but August 31 and the S above September 7 (MondaySunday week) show up in the color of today, not the 7 of September 7. What did I do wrong?
I suspect you didn't select the date cells correctly. You must select the very first date in the calendar through to the last, then apply the conditional format.
@@MyOnlineTrainingHub The S was selected as I did not copy over the whole formula for Monday-Sunday weeks (only had that in the first month). After I fixed that, the S is not formatted anymore, but August 31st is still the formatted date.
Edit: I started over and now it works somehow. Just how do you get your dates to be dynamic as well? When I move to 2025, it’s all blank…
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.
I downloaded the template but could not get the dates to work. If I entered additional dates they didn't format right in the table and didn't show up in the calendar.
You need to edit the conditional formatting range to pick up the new rows in the table if you add more dates.
When I try to generate dates, hit Enter after use SEQUENCE function, is asking for a cell name #NAME?
Any help please? Many thanks.
That's an error because you have a version of Excel that doesn't have the SEQUENCE function. You can find a calendar that works with your version of Excel via the Excel File tab > New and search for "Any year calendar with holidays”.
finally someone else who says "yooey"
😁
How to velukup image in xl
Please see this tutorial: www.myonlinetraininghub.com/lookup-pictures-in-excel
Hi Thanks for sharing, however Im using excel 2019 and when formatting the spin button I dont have the "Control" section in format control. Ive entered details in properties windows but still does not work
I need to Embed in vba eg for Spin Button Change
Private Sub SpinButton1_Change()
End Sub
Please can you help
Sounds like you inserted the Active X version of the spin button. You need the 'Form Control' version.
@@MyOnlineTrainingHub you were spot on , I’ve charged it to Form Control Spinner and brilliant.
Many thanks
Everything looks the same but when I roll over to a new year the dates change and the extra days are whited out but the holidays and birthdays do not show up. What did I do wrong?
Have you changed the dates in the Dates sheet to reflect the new year (i.e. Myrna used 4/7/2024 and if you roll over to 2025 the date would need to be listed as 4/7/2025 to show up). Also the formula for conditional formatting needs to be extended to include any new dates entered.
If you look at my example file you'll see that I construct the birthday dates with the DATE formula that takes the day and month from the table columns and the year from the calendar.
@@MyOnlineTrainingHub Thank you. I did everything from scratch and didn't see that. I am adding a new question to the comments.
😮 Does anyone really use Excel to keep track of events using a manually made calendar like this? Why? Are there advantages to this approach? Please enlighten me.
I don't think that anybody actually uses this on say a daily basis. But I can see an office using such a calendar ONCE a year to print up say the new Fiscal Weeks of the next year. Other than that I can't see much of a daily use reason.
I do! I use calendars like this to have an overview of certain events in the year rather than using Outlook that's cluttered with all my appointments and reminders. I've been using a calendar like this for years. Each year, I roll it over to the new year.
@@MyOnlineTrainingHub I use a Calendar in my Excel spreadsheets for all of my financial and home management related stuff. I use it to plan out my year such as counting paychecks and reoccurring events to help predict income and expenses in my personal life.
@@MyOnlineTrainingHubfor the next video, could you consider demonstrating the uses of your Excel-based calendar? I’d like to see to get some ideas on how this may be useful.
Thanks for the suggestion, @pon0050