0

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.

  • 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 Jan 7 at 23:52
1

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")))

enter image description here

  • 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 Jan 7 at 23:38
1

Here is one way:

=IF(TEXT(F5,"dd/mm")>TEXT(TODAY()+14,"dd/mm"),"later","earlier")
  • Terrific! That seems to work :) – Blobby Jan 7 at 23:37

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.