Highlight events in a calendar
Event - DATE START - DATE END
1
2
3
4
5
6
On sheet 2 i have a Year Calander (365 Days)I need to do a conditional formatting to highlight the days which i have events.The 6 is just a start and the list will go longer...I want to have a pictorial view of the calendar on where the events fall in the year which is SHEET 2.
Sheet 1 i just key in the dates for the start and end....
Answer:
How to change year
You can change the year in cell K2 and the calendar changes almost instantly.
How to add or remove events
The events are in an excel defined table. You can add or remove rows by right clicking on a cell and select Insert or Delete.
You can also add a blank row by selecting the last cell in the table.
Press Tab key.
You can move the table to any sheet you like.
How to change formatting color
- Select cell range B6:X38
- Go to tab "Home"
- Click "Conditional formatting" button
- Click "Manage Rules..."
- Select the rule with grey formatting
- Click "Edit Rule..." button
- Click "Format..." button
- Go to tab "Fill"
- Pick a different color
- Click OK
- Click OK
Download excel *.xlsx file
Highlight events in a calendar v3.xlsx
Related articles
The image above shows a calendar that is dynamic meaning you choose year and month and the calendar instantly updates [โฆ]
I have created another monthly calendar template for you to download. Select a month and year in cells A1 and [โฆ]
Plot date ranges in a calendar part 2
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names [โฆ]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional [โฆ]
Tesh asks: How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe [โฆ]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The [โฆ]
Highlight specific time ranges in a weekly schedule
In a previous post I created a simple weekly schedule with dynamic dates, in this post I am going to [โฆ]
Tesh asks: How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe [โฆ]
Plot date ranges in a calendar part 2
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names [โฆ]
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of [โฆ]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional [โฆ]
Excel template: Getting Things Done [VBA]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add [โฆ]
11 Responses to โHighlight events in a calendarโ
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish
Hi,
There are several bugs in your calendar. The dates are not quite right for 2013, and I think this is because of the parameter chosen in the Weekday function.
Also, when the month starts on a Sunday, it drops down a line. I tried to fix this by changing the functions but then the Sunday was blanked and I didn't have time to unravel your conditional formatting.
regardless, this is very useful for me so thanks
Peter
Peter Clark,
thanks!
There are several bugs in your calendar. The dates are not quite right for 2013, and I think this is because of the parameter chosen in the Weekday function.
Corrected!
Also, when the month starts on a Sunday, it drops down a line.
I am not seeing this on my sheet.
Thanks for commenting!
Hi.
I have a worksheet that generates a schedule of milestone dates via formula after I type in one date. I use your calendars on different worksheets and I pull in the dates from my main sheet to insert into the table that you use to drive the calendars (only I have the start and end date the same since they are milestones-- only one day is highlighted on the calendar, not a range of days.) I then use Excel's camera utility to take an image of the calendar and put it on my main screen next to the generated dates.
The problem I'm having is when I create a second set of worksheets in the same workbook, the new calendars still want to show the values that were on the first set of worksheets, even though I've changed the pointers so they point to the dates on the new main sheet. Example: on the first set that worked-- the formula for the first start date in your calendar's table is:
='Nov 2013 Schedule Calculator'!E3
On my new set, the formula for the first start date in your calendar's table is:
='Aug 2013 Schedule Calculator'!E3
So the dates in the table are correct-- but the calendar seems to be getting its instructions on what to highlight from elsewhere.
How do I adjust this?
G. Cooley,
1. Select the picture in the second set of worksheets
2. Change the cell reference in the formula bar
3. Press Enter
Oscar-- thanks, that fixed it. This is great software.
I just have one last question. I did change the color from the gray to red so it stands out, and I'm using the same start and end date because I'm showing milestones, not ranges. It works fine, but I have a date-- Sept 4, 2013. It shows up in red on Sept 4, but it also shows up in as the red 4 in the bottom of the August block (in the area that is supposed to be white-one-white). Do you know what is causing that?
G. Cooley
Oscar: I think I may have figured this out. On your rule for formula IF(MONTH (J24)8,1,0) for August, you have the Format set to white lettering on a NO BACKGROUND. All your other conditional formatting rules like this have the format set to white lettering on a WHITE background. I changed it and this seems to have fixed the problem. (without this fix, if you have days in the table in that first week of September, they show up at the bottom of the August month-- in that row that is supposed to be white on white.
G. Cooley
We are using your calendar for our machining work centers. One question though, is there a way to have multiple tabs (example, one tab would be grinding, one tab would be machining etc...)
I tried just copying the tab, but that doesn't seem to work.
Thanks,
Rob
Robert,
is there a way to have multiple tabs (example, one tab would be grinding, one tab would be machining
Yes!
1. Copy sheet
2. Select the new sheet
3. Find out the new table name (Select a cell in the table and click tab "Design")
4. Change this conditional formatting formula, replace Table1 with the new table name (Table2):
=IF(B6="",FALSE,SUMPRODUCT((B6>=INDIRECT("Table1[Start]"))*(B6< =INDIRECT("Table1[End]")))) to =IF(B6="",FALSE,SUMPRODUCT((B6>=INDIRECT("Table2[Start]"))*(B6<=INDIRECT("Table2[End]"))))
Is it possible to display Event name in calendar instead of highlight or first three letters of event
Great job on this. 2 quick questions.
1. Is there a way to make each event a different color?
2. Is there a way to make it so that if 2 event dates overlap that it makes the overlapping calendar dates highlighted red?
I will dig into it and figure it out but just thought you may have a quick answer.
Thanks!
when you set event1 date 1.1.2013 to 31.12.2013, you can see
august has a problem with coloring.