Upcoming Birthday Email

[Jenn_Salsgiver]

To preface, I am completely new to Airtable, and get lost quickly in the formulas. I’ve been searching threads for over an hour, copying any formula I can find that relates, and not a single one has worked (“Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.”).

We want to get a reminder email to one of our employees when an agent’s birthday is a week away. We have a birthday field for our agents, and ideally it would only need month/day, since we don’t keep record of their birth year. If I need to, I’ll just put 2020 for the year on all of them (which is what I’ve done to try to solve this). But I get stuck here, trying to create the new formula field to get the correct date necessary to move onto the automation step. Any and all help is desperately welcome for this seemingly easy task!

[Rupert_Hoffsch1]

Hi @Jenn_Salsgiver, I think we can make this work without using a date field (as that would require a year). Let’s say your storing day and month in two number fields, one for the day and one for the month (let’s call them birthDay and birthMonth). Now we can compare the day and month of NOW() with those fields. Formula would look like this:

IF(MONTH(NOW()) = birthMonth, birthDay - DAY(NOW()), 0)

This formula gives you the difference in days between the birthDay and the today’s day of the month, but only if the month is the same.

If the result of that formula equals 7 (use a “when record matches conditions” automation trigger), send an automated email or whatever you want to do :slightly_smiling_face:

Let me know if that works!

PS: You could also make this work with only one text field for storing birthDay and birthMonth, but it would involve separating those numbers and make it more complicated.

[Jenn_Salsgiver]

Rupert_Hoffschmidt:

IF(MONTH(NOW()) = birthMonth, birthDay - DAY(NOW()), 0)

Thank you for your help and input! I greatly appreciate it. In another thread, someone offered this formula, with the single text field of MM/DD, and it worked! Going to move forward to try the automation, but if it doesn’t pan out, I’ll be back to revisit your suggestion. Thanks again for being willing to step in and help a newbie!!
IF(
{Birthday},
DATETIME_PARSE(
{Birthday},
‘MM/DD’
)
)