Excel conditional formatting calendar

Excel has some magic formatting options. With conditional formatting I’ve created my own little calendar with highlighted current day / month / date. Full sample for download below this post.

excel.calendar.with.highlight

The screenshot is from March 16, but you’ve already seen that 😉

Current day

In the header of the calendar I’ve set all days (1-31) The value of each column can be compared to the DAY function. With the conditional formatting of Excel I can highlight the current day.

excel.format.cell.value

Based on the cell value that is compared to the calculated current day Excel will apply special formatting to the current day.

Current month

The months are in the vertical header of the calendar. Only difference with the day is the use of abbreviated names in stead of numbers. That is where the INDEX function comes in. With the INDEX function Excel will return the contents of a certain ROW (and COLUMN) in an area.

' A3:A14 = the vertical header
' AH3 = the calculated current month
=INDEX(A3:A14;AH3)

Now apply a conditional formatting rule to the vertical header based on the cell value and the month is highlighted.

Current date

To highlight the current date in the calendar I use the CELL function. With that I can get the column and the row of each cell.

' $AH$2 = the current day
' $AH$3 = the current month
=AND($AH$2 = CELL("col";A1);$AH$3 = CELL("row";A1))

Important was the [reference] parameter of the CELL function. This is the relative location to get the information for. A1 means the current cell.

excel.format.formula

The conditional formatting is now based on a formula. Excel will evaluate the formula for each cell and highlight the cells that evaluate to TRUE.

References

Download Excel file with 2015 calendar

About erictummers

My work as a recruited developer changes almost every month. I like challenges and sharing the solutions with others. On my blog I’ll mostly post about my work, but expect an occasional home project, productivity tip and tooling review.
This entry was posted in Tooling and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s