How to Highlight Birthday Automatically

In this article, you will see how you can change the color when a Birthday is coming up in few days. No macro has been used.

Step 1: Add the current date

First of all, add the current date with the function TODAY() in a new column.

=TODAY()

Step 2: Gap for the months

In a new column, calculate the number of months remaining before the birthday.

Use the DATEDIF function and the parameter "ym" (number of months in the current year).

=DATEDIF(C2;D2;"ym")

Step 3: Gap for the days

In the same way, we are going to calculate the number of days remaining before the birthday.

Use the function DATEDIF again but with the value"md" this time for the third argument.

=DATEDIF(C2;D2;"md")

Step 4: Analyze of the result

As you can see on the picture

  • If the birthday is soon, the value of the gap in day and month is huge
  • On the other hand, if the birthday has been reached, both value are close to 0

In this situation, it is not easy to create a test to warning when a birthday is coming up. Except if you add a delay 😉😋💡

Step 5: Add a delay

Write the number of days before a birthday in another cell and change the previous formula to include this value.

The formula for the months becomes

=DATEDIF(C2-$I$2;D2;"ym")

The formula for the days becomes

=DATEDIF(C2-$I$2 ;D2;"md")

Write the reference of the delay with an absolute reference, for example,
$I$2.

Step 6: Create a test with the 2 columns

The test for the month is really easy because the value must be 0

=E2=0

The test for the days is not complex but it is clever. We will test if the Gap in days is lower than or equal to the Delay in days.

The test is:

=F2<=$I$2

Merge these 2 tests into a single one with the logical operator AND.

=AND(E2=0,F2<=$I$2)

The test returns what we expect but it isn't really easy to identify which cells return TRUE at first glance. This is why we are going to write this test in a conditional formatting rule.

Step 7: Add a conditional formatting

  • First copy the formula of the test (with the AND)
  • Open the menu Home > Conditional Formatting > New rule
  • Select the option Use a formula to determine which cells to format
  • Paste the formula
  • Customize the format when the rule is TRUE (here font color: white and background: red)

Step 8: Change the range of application

The final step is to apply this conditional formatting to the whole column.

  • Open the menu Home > Conditional Formatting > Manage rules
  • In the text box Apply to, select the range of cells to apply the conditional formatting to.

The final result is displayed below:

Related posts


Permanent link to this article: https://www.excel-exercise.com/how-to-highlight-birthday-automatically/


Leave a Reply

Your email address will not be published.