In Excel - how to compare a date to today's date, but ignoring the year portion?

I have a large and constantly evolving list of contact's birthdays. They all include the year, but that is irrelevant to our needs. I would like to have a cell that calculates whether a birthday (day and month) is between Today and 14 days from today.

I have been using this formula:

=IF(F5>TODAY()+14,"later", "earlier")

But of course, it is comparing everything including the year - so of course everyone that was born before today. I know my formula isn't complete, but I'm just using that for the purposes of clarity.

Blobby

Posted 2019-01-07T23:03:29.667

Reputation: 3

You should use the appropriate button to indicate that one of these answers works (or works better) for you. This way future visitors will see which one answered your question, and the people who gave the answer will receive appropriate points. – music2myear – 2019-01-07T23:52:53.863

Answers

Use DATE(YEAR(TODAY()),MONTH(F5),DAY(F5)) to return a date that is this year on that day and month.

=IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))>TODAY() + 14,"later",IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))<TODAY(),"Past",IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))=TODAY(),"Today","With in Two Weeks")))

Scott Craner

Posted 2019-01-07T23:03:29.667

Reputation: 16 128

Thanks, I like this way too. Edit: came back to say that this probably works better for me - as it includes the entire calculation for the "within two weeks". Thanks! – Blobby – 2019-01-07T23:38:43.733

Here is one way:

=IF(TEXT(F5,"dd/mm")>TEXT(TODAY()+14,"dd/mm"),"later","earlier")

Brian

Posted 2019-01-07T23:03:29.667

Reputation: 681

Terrific! That seems to work :) – Blobby – 2019-01-07T23:37:20.633