Share via

Ignore the YEAR when comparing dates in a formula

Anonymous
2012-04-03T19:43:55+00:00

I am keeping track of upcoming birthdays.  I'm looking for a conditional format that will highlight the cell if a birth day and month (ignore the year) are within the next 10 days.  Currently if I compare a birthday, 04/6/59 to today's date + the date 10 days forward, nothing happens because 04/6/59 does NOT fall between 04/03/12 and 04/13/12.  I want it to ignore the year in the formula and only evaluate whether 04/06 falls between 04/03 and 04/13.  Thanks in advance, Judy

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2012-04-03T20:04:36+00:00

    Let's say the birth dates are in B2:B50.

    Select this range, then select Format | Conditional Formatting...

    Select Formula Is from the first dropdown, then enter the following formula in the box next to it:

    =AND(DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))>TODAY(),DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))<=TODAY()+10)

    Here, B2 is the active cell within the selected range.

    Click Format... and specify the desired formatting.

    Click OK twice.

    5 people found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-04-03T20:36:20+00:00

    Let's say the birth dates are in B2:B50.

    Select this range, then select Format | Conditional Formatting...

    Select Formula Is from the first dropdown, then enter the following formula in the box next to it:

     

    =AND(DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))>TODAY(),DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))<=TODAY()+10)

     

    Here, B2 is the active cell within the selected range.

    Click Format... and specify the desired formatting.

    Click OK twice.

    Thank you!  It worked great!

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2012-04-03T20:05:06+00:00

    .. I want it to ignore the year in the formula and only evaluate whether 04/06 falls between 04/03 and 04/13 ...

    You could approach this from two different angles.

    First, you could take the year of the birthdate and today's date out of the formula entirely by converting to text like this,

    =SUMPRODUCT(--(TEXT(A1,"ddmm")=TEXT(TODAY()+ROW($1:$11)-1,"ddmm")))

    On the other hand, you could adjust the birthdate's year to today's year and compare the dates as you mentioned in your OP like this,

    =AND(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))>=TODAY(),DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY()+11)

    The above formulas assume that A1 is the top cell in your column of birth dates. Adjust as needed when you create your conditional formatting rule.

    0 comments