Using Excel Conditional Formatting Based on Date

Turning your cluttered spreadsheet of data into living, actionable information can be as simple as adding visual cues. Unless you are really good at doing date calculations in your head, conditional formatting is especially helpful when determining ages or anticipating birthdays and anniversaries. The following steps show how to perform a common Human Resources task: Finding and Highlighting Birthdays.

Highlight Upcoming Birthdays

If you are in charge of sending a birthday message to every employee each month, you can use conditional formatting to quickly highlight who has a birthday in the current month without sorting or filtering.

How to Do It:

1. Select the range of cells that contains the birthday data. Do not include headings.
2. On the Home tab, click Conditional Formatting.
3. Select New Rule.
4. Select Use a formula to determine which cells to format.
5. Use Excel’s MONTH function to find the cells that contain birthdates in the current month. Use the TODAY function as your comparison date so the spreadsheet will always be current when you open it. (The example was written on April 20.) Your formula would be:

6. Click the Format button to specify how you want the matching cell to appear.
7. Click OK.

To follow using our example, download FPS_Conditional Formatting Date Data.xls

More Ideas:

• Calculate birthdays occurring in the current week: =WEEKNUM(C2)=WEEKNUM(TODAY())
  (WARNING! Note that dates can fall on different weeks in different years just as your birthday won’t be on the same day of the week each year. This method will work for highlighting upcoming birthdays on a regular basis, but may not be appropriate for other uses.)
• Color code by department:
  • Use the AND function to specify multiple criteria for the condition. Specify the Department in the formula, and the formatting you want in the dialog box: =AND(MONTH(C2)=MONTH(TODAY()),D2=”Sales”)
  • Then, follow the steps above to create separate Rules for the other departments. Your results might look something like this image:

Highlight Specific Anniversaries Each Month

If you want to highlight specific upcoming birthdays – such as a 65^th birthday – or anniversaries, that will take a little more formula work.

1. Our first condition will find birthdays happening this month as we did above with the formula: =MONTH(C2)=MONTH(TODAY())
2. Then we’ll use the YEAR function to find birth years that are 65 years ago: =YEAR(TODAY())-YEAR(C2)
3. The AND function combines the two for our conditional formatting result of: =AND(YEAR(TODAY())-YEAR(C2)=65,MONTH(C2)=MONTH(TODAY()))

Each time you open the workbook, you wil find the current month’s 65^th birthdays highlighted.

Hint! To make your birthday calculations even more visually appealing, highlight the entire row instead of just the date cell.