In this post I finish off the Calendar matrix by adding holidays.

This is the third in a three-part series on using the SEQUENCE function with dates.

You can review the first post here.

You can view the second post here.

You can download the final file at the button at the bottom of the post.

NOTE: The SEQUENCE function is part of the Dynamic Arrays update that is only currently available in the subscription version of Excel.

At the end of the previous post we ended up with the calendar matrix below.

We want to highlight public holidays on the matrix.

We can use Conditional Formatting.

We need a list of dates to treat a public holidays. On a separate sheet called Holidays, I have added a list of three dates in July for testing.

Adding conditional formatting

1. Select the range C4:I9.
2. Click the Conditional Formatting drop down on the Home ribbon.
3. Select New Rule.Select the last option in the top section “Use a formula to …”
4. Enter the following formula

   =COUNTIF(Holidays!$A:$A,DATE(YEAR($B$1),MONTH($B$1),C4))>0

   The $ signs are important. They are on all the references except C4 because C4 needs to change and become each cell within the range.

   The DATE function is used to create the correct date to compare to the list.

5. Click the Format tab. Click the Fill tab and add a grey Fill and click OK.
6. Click OK.

The result is shown below.

The day of the month is shown in most cells in the matrix. We need to create a date in the current month for each cell. The DATE function allows us to extract the Year and the Month from the date in cell B1 and then use cell entry for the day.

The COUNTIF function counts how many times an entry appears in a range. We are counting how many time the date appears in the list and comparing the result to zero. If the date is in the list the COUNTIF will be 1. This is above zero and so it and will return TRUE. If the date isn’t in the list it will return FALSE.

Any formula used for a conditional format needs to return TRUE to trigger the format.

Adding extra dates to the list can trigger formats in other months.

The above technique could be used to highlight fortnightly pay runs or other regular dates.

You could also use different colours to highlight different types of dates.

Download final file