Highlight events, weekends and holidays on calendar in Excel
HTML-код
- Опубликовано: 11 июн 2024
- Learn how to highlight/color weekends, holidays and other events on a calendar in Excel.
In the previous video • Create an annual Calen... we learned how to create a dynamic annual calendar. In this video, we will continue and highlight event dates on the calendar.
Step 1: Create event dates in separate tables, 1 table for each event type
Step 2: Write a Conditional Formatting rule to color weekend days using WEEKDAY function
Step 3: Write a Conditional Formatting rule to color event days using MATCH function
Step 4: Repeat step 3 for each additional event type.
Handling conflicting events on same date: Change the order of the conditional formatting rules keeping in mind that the rules higher in the rule set will override the rules below.
Download free Excel calendar template from indzara.com/2018/12/2019-exce...
Functions used in this week's video: MATCH, WEEKDAY
Features used: Conditional Formatting
Social:
Subscribe to RUclips: ruclips.net/user/theindzar...
Facebook: / theindzara 
Хобби
I searched for so long to find someone to explain this for me! Thank you so much for you very easy to follow instructions!
You are welcome. Thank you for sharing your valuable feedback.
Best wishes.
Dear Indzara, thank you for very good and useful lesson with distinct clear demonstrations.
Thanks for your kind words. Best wishes.
Thanks for this very useful tutorial!
You are welcome.
Best wishes.
😍I love this video!! This video was superbly done! I was trying to find out how to highlight pay days and this video showed that and so much more. Thank you so much! I just subscribed to your channel.
Thank you for valuable feedback. We are glad that you liked our video. You are welcome. Thank you for the subscription.
Best wishes.
Very slick - thank you.
Thank you for sharing your valuable feedback and you are welcome.
Best wishes.
Thankyou for your video. Helped me so much.
You are welcome. We are glad that our video was helpful.
Best wishes.
waooo, very nice video. Thank you so much.
Thanks for the feedback. Glad you find it useful.
Best wishes.
Thanks so much for these videos! I followed along with the annual calendar one. I was wondering how to highlight say the 3rd Saturday of the month. If you could point me in the direction on how to learn to do this, I'd appreciate it! Thanks!
You are welcome.
In the Calculations sheet, we calculate the 'week number in month' and the weekday. For the conditional formatting rule for third sat of a month, we need 'week number in month' = 3 and weekday =7 (for sat).
Best wishes.
Hi, thanks for the vids i was wondering if there was a way to highlight by multiple days based on week number to show shift patterns across a 3 week rota if thats possible? And if this 3 week rota would carry over if i changed the calendar year
Thank you for this video, very helpful! My question is, if you need to add an additional event/date after creating the tables and adding in conditional formatting (e.g., a new work event is added in the middle of the table), do you need to re-do the conditional formatting formula each time?
You are welcome.
If we do the events and holidays as tables, we can make it dynamic and not having to do the formatting again.
Best wishes.
Hello, Thank you so much for these videos, I was able to make my calendar with all the Pay days and holidays highlighted in different colors. One question though, for days that are both Payday and Holiday, I was wondering how I can compare the result of my 2 tables and have the similarities put into a 3rd table(using some sort of formula) so I can apply a different colour for those days?
You are welcome.
You can write two match condition as shown below with AND to get the required output:
=AND(MATCH(B5, PAY_DAY_LIST)>0,MATCH(B5,HOL_DAY_LIST)>0)
In manage conditional formatting rules window, move this condition to top of all the conditions and tick mark the stop if true on the right side of the rule.
Best wishes.
Thanks so much for these videos.I just have one question: How do you highlight two events related to different category (ex:holidays and personal) in the same day?
You are welcome.
Since we are highlighting one cell, it can only be one color (for one of the categories). If we need multiple colors, we need to allocate multiple cells to each day.
Best wishes.
@@indzara I tried to do that but not sure how - are you able to share how you would do that? Thanks!
@@Bel-gk7ie Requesting to follow below steps to apply the method explained in the previous comments:
1.Write the formula to display the date in cell A1 and the next date formula should be in cell A3.
2. Select 2 cells if you want two different event highlighted on same day or select 4 cells for 4 events. For example, A1 & A2 for two events, A1, A2, B1, B2 for 4 events.
3. Press CTRL+1 -> Alignment -> Horizontal alignment (Centre Across Selection)
4. Write conditional formatting on A1 and separate conditional formatting on A2 for separate vacation type. (Note - Conditional formatting formula must have be modified accordingly.)
Best wishes.
Thanks for sharing. great simple calendar:) my question is, if I want to see what is the event name instead of just knowing there is an event on that day, is there any quick way to insert some comments inside the calendar? Thanks
You are welcome.
Please see calendar template. indzara.com/2018/12/2019-excel-calendar-template/
Best wishes.
Thank you for the video, it's really helpful!!! My question is - How can I highlight last working day of each month? Considering Saturday and Sunday as off. It will be great if you let me know ways to do so. Thanks in advance.
You are welcome.
Highlighting event instances involves complicated steps and the same is available in our Event Calendar Maker template. Following is the link to the template for quick reference:
indzara.com/product/event-calendar-maker/
Best wishes.
Hi Thanks for sharing this exactly what I was looking for. If I have 15 personal event dates but want to highlight event dates which meet specific criteria how can I achieve that?
You are welcome.
The requested feature involves some complicated formulas. Requesting to check our Event Calendar Maker template, which may suit your needs,
indzara.com/product/event-calendar-maker/
Requesting to email to support@indzara.com for more details.
Best wishes.
This is great, thank you! Really helpful.
I'm trying to highlight multiple days - how do I do that?
For example, I have two columns of holidays - one when holiday starts, the other when holidays finish. How can I set conditional formatting to highlight all cells in between please?
Thank you for sharing your valuable feedback and you are welcome.
You can achieve by using AND logic like (">= start date and
Thank you for the previous video! My question is how do I input pay days that occur biweekly and change automatically when I change the year. Also adding US holidays to automatically change when I enter the year under the condition that if the holiday falls on a Saturday then the holiday is observed on a Friday and highlighted and if the actual holiday falls on a Sunday then the holiday is observed on that following Monday and highlighted instead.
Thank you for sharing your requirement.
The requested feature is available in our premium version of the template. Following is the link to the template for quick reference:
indzara.com/product/event-calendar-maker/
Following is the steps to achieve your requirement in our Event Calendar Maker premium template:
1. Create two events with start date as start of the year (01-Jan-2022 or 01-Apr-2022) and end date as end of the year (31-Dec-2022 or 31-Mar-2023).
2. First event with Nth Business day of the month event frequency type
3. For first event, N need to be 11 and all working day need to be 1.
4. Second event with Last Business day of the month event frequency type
5. For second event N should be blank and all working day need to be 1.
Best wishes.
Hi, I've been watching your videos for a while now I have created the calendar already, everything works fine but about highlighting the events or holidays, is that in case we want to add some days in the events table will it automatically highlight the dates added or we have to edit the formula in conditional formatting again. pls reply thanks a lot
Thank you.
If the events are in table, then the dates should get highlighted in the calendar automatically.
Best wishes.
Hello, can you format a calendar cell color depending on another cell result witch is matched with a date on a table.
Thank you for watching our video.
Yes, you can highlight a calendar cell depends on another dynamic cell value. If you want more help on this, requesting to share us your sample sheet at support@indzara.com highlighting your requirement to check further.
Best wishes.
Great tutorials. Please take time to pause and move cursor when entering formulas so we can verify the content. I need all the help I can get.
Thank you for sharing your valuable feedback.
We will try to make the suggested improvements in our upcoming videos.
If you have any queries on this video, please share us the query and time frame on which your query is present at the below link to assist further:
support.indzara.com/support/tickets/new
Best wishes.
You created a list for your conditional formatting. I also created a list, but I used a formula to create the list so I don't have to manually add appointments to the list every time I schedule one. Now the conditional formatting is highlighting everyday because it is picking up that formula instead of the results of the formula. Is there a way to tell it to read the results and not the formulas?
Thank you for watching our video.
I cannot let you know the changes, without looking at the formulas. We also have a premium version of the template where you can select the frequency of the event to highlight the same in the calendar accordingly. Following is the link to the same for quick reference:
indzara.com/product/event-calendar-maker/
Best wishes.
Thanks for sharing however I am having issues. When I created my calendar I used the following formula in the 1st Sunday cell (A7): "=A5-WEEKDAY(A5,1)+1" and then in cell A8 I used "=A7+1" and then continued this for the rest of the cells for the month. I then used the following conditional format to grey out the days that didn't pertain to the current month: "=MONTH(A7)MONTH($A$5)". I should note that cell "A5" is date 1/1/2020. When I follow your instructions on adding a conditional format to highlight holidays it does not work. Any thoughts on why this isn't working? Thanks
Please email file to support@indzara.com and mention the issue. I will review and get back to you.
Thanks & Best wishes.
Thanks for sharing. I just have one question: How do you make public holidays dynamic to the change of years?
You are welcome.
Please try this indzara.com/2016/01/how-to-create-u-s-holidays-on-calendar/ and let me know if this addresses your question.
Best wishes.
how would you do this if the personal dates had start and end like a vacation?
This involves some more validation in the formula. For example, if the calendar date is 06-Aug-2021 then you need to write a formula to validate whether the date is between the start and end date.
This feature is available in our premium Event Calendar Maker template. Following is the link to the same:
indzara.com/product/event-calendar-maker/
Best wishes.
I have to make a birthday calendar for our employees. Each month there are about 50 birthdays. Do you know if I can take a list of names and birthdates and easily fill in an excel calendar and not fill them in one by one?
Thanks.
Please see this video on creating annual events using Event Calendar Maker template.
ruclips.net/video/6b0gnuPQMGM/видео.html
Best wishes.
Very Nice Video. Is it possible to highlight the day according to a rotating schedule? For Example Its my turn to work this weekend so I want to highlight Friday,Saturday,Sunday for me. Then another employee will work the following weekend and so on based on 5 different people that rotate. So my turn comes the 1st weekend in January then again the 6th week of the year which is mid February.
Thank you for sharing your feedback.
Yes it is possible and the same is available in our Event Calendar Maker Premium template. Requesting to check the same in the following link.
indzara.com/product/event-calendar-maker/
Best wishes.
Can you do a range of days? So if my excel schedule has two columns Start 1/1 and End dates 1/4 is there a way to highlight all dates from 1/1 to 1/4?
Yes, formula has to be modified to consider the end date entered by user.
best wishes.
Hello Mark, did you end up doing this? I have the same question. Need to highlight a range of dates based on start and end date of event. Thanks for the help
Can I highlight one box with 2 colours?
Thank you for showing interest in our template.
No, you cannot highlight one box with 2 events. If you want to highlight one event with 2 colour, you can use gradient colour in conditional formatting.
Best wishes.
Haven't you created employees monthly working schedule template?
Thank you for showing interest in our template.
Requesting to check our Employee leave Manager in following link:
indzara.com/product/employee-leave-manager/
Trail version of the above template is available in the following link:
indzara.com/2016/11/employee-leave-vacation-tracker/
We also have Employee Project Management excel templates:
indzara.com/project-management-excel-templates/
Best wishes.
Requesting to share your sheet to support@indzara.com to check further.
Best wishes.
What of it is a series of dates? Start date to end date. How do I highlight a series of dates?
It involves some additional steps. Currently, we do not have video explanation available for the same.
You can check our Free 2024 calendar template available at the below link which has the requested feature:
indzara.com/free-excel-template-for-calendar-2024-with-24-formats/
Best wishes.
How to do this in google sheets? Thanks!
It is similar in Google Sheets. Just the place to open the conditional formatting will differ. Following is the link to the same template in Google Sheets:
indzara.com/2020/03/2020-calendar-free-google-sheet-template/
Best wishes.
How to make the following formula to match only the following date for example: if the date is 10.dec.2020 to check only the date and the month becouse i wana use it for data of birth!?
Step 1: Create a new table with two columns. Let's say in cells AG4 and AH4 are the two column headers. In first column enter month and day of birthday (for example 10-23 for Oct 23rd in cell AG5). In the cell AH5, enter the following formula =(AG5&"-"&W1)*1
Note that W1 is the year of the calendar.
Step 2: Now, create the conditional formatting rule as explained in the above video to highlight dates that appear in cells in column AH.
If you change the year in cell W1 to 2020, the dates in column AH will become dates in 2020. the calendar will now show highlighted in color you chose in conditional formatting.
Best wishes.
why is this on one page on the video and 2 pages when i download it from the website :(
Sorry, I do not understand your exact concern.
This video is a tutorial video on how to highlight events, weekends and holidays. The template published in the website has 19 different calendar design, hence the sheet calculations are organized.
If this is not your concern, requesting to share your concern at support@indzara.com to check further.
Best wishes.
Great video, thanks for hosting it, but please remember to slow down your instruction, as your viewers will be watching what your doing and trying to mimic you, also, please give more detail on what exactly is in your formulas, not all your viewers are excel whizz kids.
Thanks for feedback.
Best wishes.