Author: Oscar Cronquist Article last updated on December 11, 2019

This article demonstrates how to highlight given date ranges in a yearly calendar, this calendar allows you to change the year and the calendar dates change accordingly.

The image above shows an Excel Table to the right of the calendar, you can easily add as many events as you like.

This worksheet uses Conditional Formatting formulas containing structured references pointing to the Excel defined Table, this makes it simple to use because no formulas need to be changed when the list of events grows larger.

Steven asks:
I got 6 events with different dates.
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 apply conditional formatting to highlight the days in which I have events.The six events are just a start and the list will grow longer. I want to have a pictorial view of the calendar on where the events fall in the year which is Sheet2.Sheet1 is just the key in the dates for the start and end.

How to create an Excel Table

The main benefit of converting the data set, in this case, is that the cell references in formulas don't need to be changed when you add additional date ranges to the Excel Table.

The cell references pointing to an Excel Table are called structured references and are dynamic, they don't change no matter how many date ranges you add to the Table.

One disadvantage with structured cell references is that you need to apply a workaround in order to use them in Conditional formatting formulas and Drop Down Lists.

  1. Select any cell in your data set.
  2. Press shortcut keys CTRL + T to open the "Create Table" dialog box, see image above.
  3. Press OK button to create the Excel Table.

How to build a dynamic yearly calendar based on input year

Here are the steps to create the calendar without dates.

  1. Select cell range B4:H4.
  2. Go to tab "Home" on the ribbon if you are not already there.
  3. Click "Merge and Center" button.
  4. Type this formula in cell B4: =DATE($K$2,1,1) and press Enter. This will return a date, however, we need only the month name to be displayed.
  5. Select cell B4 and press CTRL + 1 to open the "Format Cells" dialog box.
  6. Select category: Custom and type mmmm.
  7. Click OK button. This will format the date in cell B4 to only show the month name. This will make the month name dynamic meaning it will change if the Excel user has a different Excel language installed.
  8. Select cell B5 and type Mo and then press Tab key to move to the next cell.
  9. Continue typing Tu, We, Th, Fr, Sa and Su with the remaining cells, see image above.
  10. Click and hold on column header B.
  11. Drag with mouse to column H.
  12. Click and hold on any of the separating lines between the column headers.
  13. Drag with mouse until column width is around 26 pixels, you can change this later.
  14. Release mouse button and all selected columns will have the width 26 pixels.
    Highlight events in a yearly calendar build dates1
  15. Copy cell range B4:H5 and paste to J4:P5.
  16. Change columns widths to 26 pixels.
  17. Enter this formula in cell J4 for February: =DATE($K$2,2,1)
    The only difference between this formula and the formula for January is the month argument which I have bolded in the formula above.
    2 represents February which is the second month.
    Highlight events in a yearly calendar build dates2
  18. Copy cell range B4:X5 and paste to B13:X20.
  19. Change these months as well. March formula: =DATE($K$2,3,1)
  20. Repeat with remaining quarters.
    Highlight events in a yearly calendar build dates3

The week starts with Sunday if you live in the US, the image then looks like this.

Select month names, weekday names and six rows below each month and apply a border to the selected cells.

  1. Go to tab "Home" on the ribbon.
  2. Click on border button.
  3. Click on "All borders".

This creates a border around each cell.

  1. Select columns B to X.
  2. Go to tab "Home" on the ribbon.
  3. Click on "Center" button to center cell content.

Calendar date formulas

I center and merged cell range K2:O2 and entered year 2020 as an example, all formulas will be based on this year that is entered in cell K2.

Select cell B6 which is the first cell for the month of January, type the following formula:

=DATE($K$2,1,1)-WEEKDAY(DATE($K$2,1,1),2)+1

This formula calculates the first date in the first week which the first day in January falls, this may be a date in December, however, I will use Conditional formatting to hide dates outside the month later in this article.

The DATE function uses three arguments, year, month and day. DATE(year, month, day)

DATE($K$2,1,1)

becomes

DATE(2020,1,1)

and returns 12/30/2019.

The WEEKDAY function calculates a number based on a date representing the position in a week. WEEKDAY(serial_number,[return_type])

The serial_number argument is the date and the [return_type] argument lets you pick which day the week begins with. return_type 2 returns 1 for Monday, 2 for Tuesday, 3 for Wednesday, etc.

DATE($K$2,1,1)-WEEKDAY(DATE($K$2,1,1),2)+1

becomes

43831-WEEKDAY(43831,2)+1

1/1/2020 falls on a Wednesday and the WEEKNUM function will then return 3. 3 = Wednesday.

43831-3

and returns

43828 which is 12/29/2019.

Copy cell B6 and paste to the first cell in the remaining months, change the number representing the month argument in the formula so it corresponds to the month.

For example, in February the formula becomes:

=DATE($K$2,2,1)-WEEKDAY(DATE($K$2,2,1),2)+1

2 represents February and is bolded in the formula above.

Go back to month January and enter this formula in cell C6:

=B6+1

Copy cell C6 and paste formula to cell range C6:H11. Enter the following formula in cell B7:

=B7+7

Copy cell B7 and paste to cell range B8:B11, month January is now finished. Repeat above steps with the remaining months.

Hide dates

The image above shows the calendar, however, dates that don't belong to the month are also displayed. This may or may not be what you want, you can hide them using Conditional Formatting or color them differently also using Conditional Formatting.

Select cell range B6:H11, go to tab "Home" on the ribbon. Click on the "Conditional Formatting" button and then click on "New Rule...", this opens a dialog box.

Click on "Use a formula to determine which cells to format", then type this formula:

=MONTH(B6)<>1

Click on the "Format..." button and a "Format Cells" dialog box shows up. Click on tab "Font".

Pick font color white, this will make the text hidden. White font against a white background and the entire cell will be white.

Click OK button and click the next OK button as well. Then click "Apply" button.

If you want the dates to be shown but not as prominent, use a grey color instead.

Apply the same conditional formatting formula to the remaining months, however, change the number so it represents the month number.

For example, Februarys CF formula becomes:

=MONTH(B6)<>2

Highlight date ranges in calendar

I will now demonstrate how to apply Conditional Formatting in order to highlight dates based on the events specified in the Excel Table. If you change the year in cell K2 the highlighted dates will change accordingly making the calendar dynamic.

  1. Select all dates in the calendar. Tip! Press and CTRL key and then select the cell ranges. For example. the cell ranges to be selected in the first quarter are B6:H11, J6:P11 and R6:X11.
  2. Go to tab "Home" on the ribbon.
  3. Click the "Conditional Formatting" button.
  4. Click "New Rule..."
  5. Click "Use a formula to determine which cells to format".
  6. Type this formula: =IF(B6="",FALSE,SUMPRODUCT((B6>=INDIRECT("Table1[Start]"))*(B6<=INDIRECT("Table1[End]"))))
    Highlight events in a yearly calendar highlight events new rule
  7. Click "Format..." button.
  8. Click tab "Fill"
  9. Pick a color.
  10. Click OK button.
  11. Click OK button.
    Highlight events in a yearly calendar highlight events order CF formulas
  12. Select the CF formula you just now created, click the arrow keys to move the formula to the bottom of the list.
  13. Click all checkboxes "Stop If True" so that the last CF formula won't be executed if any of the other are. This will prevent hidden dates from being highlighted. See image above.
  14. Click Apply button and then OK button.

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.

highlight events in a calendar2

You can also add a blank row by selecting the last cell in the table.

highlight events in a calendar3

Press Tab key.

highlight events in a calendar4

You can move the table to any sheet you like.

Download Excel file


Highlight-events-in-a-calendarv4.xlsx

Related articles

Monthly calendar template

The image above shows a calendar that is dynamic meaning you choose year and month and the calendar instantly updates [โ€ฆ]

Monthly calendar template #2

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 [โ€ฆ]

Heat map yearly calendar

The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional [โ€ฆ]

Calendar โ€“ monthly view

Tesh asks: How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe [โ€ฆ]