How to Highlight a Birthday Reminder in Excel

How to Highlight a Birthday Reminder in Excel
Last Updated on 13/06/2023
Reading time: 3 minutes

Follow this step-by-step guide to highlight a birthday reminder in Excel. It can be also a reminder for an important date, like an invoice payment.

Step 1: Add today's date

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

=TODAY()

Step 2: Months until a birthday

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

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

=DATEDIF(C2;D2;"ym")

Step 3: Days until a birthday

In the same way, we calculate the number of days until 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 the result

Gap in days and months before after the birthday in the excel worksheet

As you can see on the picture

  • If the birthday is soon, the value of the number of days and months is large
  • On the other hand, if the birthday is right around now, both values are close to 0

In this situation, it is not easy to generate a warning when a birthday is coming up. Except if you add a visual reminder 😉😋💡

Step 5: Add a reminder ⏲

Decide how many days before the birthday you want to set a reminder and enter this into another cell. In the example below I have added 7 days in cell I2. Change the previous formula to include this value as per the example below.

The formula for months becomes

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

The formula for days becomes

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

Write the reference of the delay with an absolute reference. In this example that would be $I$2.

Detect the birthday in Excel a few days before the date

Step 6: Test whether or not to display a reminder

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 in Excel if the difference between the days until the birthday and the reminder is lower than or equal to the reminder.

The test is:

=F2<=$I$2

Value of the reminder before the birthday in Excel

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

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

result of the birthday reminder in an Excel cell

The test returns TRUE if the number of days until the birthday is less than or equal to the remainder. However, 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 an Excel conditional formatting for the birthday

Let's convert this test to a custom conditional formatting rule.

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

Step 8: Change the data range for conditional formatting

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

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

The final result is displayed below:

3 Comments

  1. Joren
    14/02/2020 @ 00:49

    Hi,

    There's an error in the =AND(E2=0,F2<=$I$2) formula , or at least it won't work on my version of excel (2016) it needs to be a ; after E2=0;F2<=$I$2 instead of a regular comma, otherwise excel removes it and you won't get the correct returned value.

    It corrects it to =AND(E2=0<=$I$2) which obviously won't work.

    Just wanted to let you know that, maybe it is different in a later version of excel?

    Reply

  2. RANJITH
    22/10/2019 @ 12:12

    This formula is not working in my excel sheet. why?, I need a solution of regarding this issue.

    Reply

    • Frédéric LE GUEN
      23/10/2019 @ 17:13

      Which formula doesn't work?

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

How to Highlight a Birthday Reminder in Excel

Reading time: 3 minutes
Last Updated on 13/06/2023

Follow this step-by-step guide to highlight a birthday reminder in Excel. It can be also a reminder for an important date, like an invoice payment.

Step 1: Add today's date

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

=TODAY()

Step 2: Months until a birthday

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

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

=DATEDIF(C2;D2;"ym")

Step 3: Days until a birthday

In the same way, we calculate the number of days until 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 the result

Gap in days and months before after the birthday in the excel worksheet

As you can see on the picture

  • If the birthday is soon, the value of the number of days and months is large
  • On the other hand, if the birthday is right around now, both values are close to 0

In this situation, it is not easy to generate a warning when a birthday is coming up. Except if you add a visual reminder 😉😋💡

Step 5: Add a reminder ⏲

Decide how many days before the birthday you want to set a reminder and enter this into another cell. In the example below I have added 7 days in cell I2. Change the previous formula to include this value as per the example below.

The formula for months becomes

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

The formula for days becomes

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

Write the reference of the delay with an absolute reference. In this example that would be $I$2.

Detect the birthday in Excel a few days before the date

Step 6: Test whether or not to display a reminder

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 in Excel if the difference between the days until the birthday and the reminder is lower than or equal to the reminder.

The test is:

=F2<=$I$2

Value of the reminder before the birthday in Excel

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

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

result of the birthday reminder in an Excel cell

The test returns TRUE if the number of days until the birthday is less than or equal to the remainder. However, 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 an Excel conditional formatting for the birthday

Let's convert this test to a custom conditional formatting rule.

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

Step 8: Change the data range for conditional formatting

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

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

The final result is displayed below:

3 Comments

  1. Joren
    14/02/2020 @ 00:49

    Hi,

    There's an error in the =AND(E2=0,F2<=$I$2) formula , or at least it won't work on my version of excel (2016) it needs to be a ; after E2=0;F2<=$I$2 instead of a regular comma, otherwise excel removes it and you won't get the correct returned value.

    It corrects it to =AND(E2=0<=$I$2) which obviously won't work.

    Just wanted to let you know that, maybe it is different in a later version of excel?

    Reply

  2. RANJITH
    22/10/2019 @ 12:12

    This formula is not working in my excel sheet. why?, I need a solution of regarding this issue.

    Reply

    • Frédéric LE GUEN
      23/10/2019 @ 17:13

      Which formula doesn't work?

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *