Just want to remind folks that some people have no clue where or how to even implement this script.

Your first link held the magic that fixed the problem. I replaced the portions of the formula above that calculated the birthday for the current year with your version, and everything works perfectly. The time option for the Birthday field is no longer necessary, which is an added bonus. Here’s the modified formula:

IF(

    IS_SAME(

        DATETIME_PARSE(

            DATETIME_FORMAT(

                Birthday,

                'MMMM D'

            ) & ' ' &

            YEAR(

                TODAY()

            ),

            'MMMM D YYYY'

        ),

        TODAY()

    ),

    "  ",

    IF(

        ABS(DATETIME_DIFF(

            DATETIME_PARSE(

                DATETIME_FORMAT(

                    Birthday,

                    'MMMM D'

                ) & ' ' &

                YEAR(

                    TODAY()

                ),

                'MMMM D YYYY'

            ),

            TODAY(),

            'days'

        )) < 8,

        ""

    )

)

EDIT: So there’s still a problem. The < 8 comparison means that any birthday date prior to the current date—which generates a negative difference—will trigger the second part of the condition. To fix this, I wrapped ABS() around the DATETIME_DIFF().

@Justin_Barrett - thank you so much for this formula. For some reason when I enter it, instead of only telling me the birthday’s coming up within 7 days, it’s also giving me a cake for birthday’s 7 days prior to the current day. Do you know why that might be? For example, today is May 7 and it’s giving me a cake symbol for Birthdays back to April 30. The first part of the formula works perfectly, it’s just the second part I’m having problems with.

That’s because of the ABS() function used in that second part of the formula. If that weren’t used, the formula would return the cake for more than just the 7 days on either side of someone’s birthday.

Long story short, you’re either going to get a negative or positive number using DATETIME_DIFF() depending on how you set up the date arguments. Say that you set it up so that leading up to someone’s birthday you get positive numbers. Looking for a number less than 7 from an unmodified date difference will work fine there because earlier in the year it’ll be way above 7. However, after their birthday has passed, DATETIME_DIFF() will return negative numbers, all of which are less than 7, so you’ll have that cake there for the rest of the year. The ABS() function forces the difference to be positive, so you’ll get the cake emoji for 7 days before and after, and no more.

It’s definitely possible to rework the formula to just cut off the emojis completely after someone’s birthday, but that would require a more complex formula, and unfortunately I’m swamped with other commitments and can’t put time into it.

Alright - here’s what I came up with:

First of all, Airtable has some strange quirks under the hood when it comes to dates, times, and timezone recognition. For that reason, in order to make this work, you’ll need to enable the “Time” portion of your “Birthday” field:



It’s unfortunate, but you’ll see that if you switch that off, the dates in the calculation are thrown off by 1 day, even though I am using timezone conversion in the equation (perhaps I can figure out how to avoid this at a later time, but for now, the easy work-around is just to enable the “Time” portion, and set it to 12:00am as a standard).

And then here is your formula for the Notification:

IF(

  IS_SAME(

    DATETIME_FORMAT(

      SET_TIMEZONE(

        DATEADD(

          Birthday,

          DATETIME_DIFF(

            TODAY(),

            Birthday,

            'years'

          ) + 1,

          'year'

        ),

        'America/Phoenix'

      ),

      'L'

    ),

    TODAY()

  ),

  "  ",

  IF(

    DATETIME_DIFF(

      DATETIME_FORMAT(

        SET_TIMEZONE(

          DATEADD(

            Birthday,

            DATETIME_DIFF(

              TODAY(),

              Birthday,

              'years'

            ) + 1,

            'year'

          ),

          'America/Phoenix'

        ),

        'L'

      ),

      TODAY(),

      'days'

    ) < 8,

    ""

  )

)

This will show when it is the week of a person’s birthday (within 7 days, inclusive), and will show on the day of their birthday.

EDIT:

Almost forgot - you need to replace every instance of 'America/Phoenix' (there are 2) in the formula with your timezone from this list:

Supported timezones for SET_TIMEZONE

The SET_TIMEZONE function will allow you to set the timezone for the data from a date type field. This is written in the form "SET_TIMEZONE(datetime, 'timezone identifier')," in which the...

And, of course, use whatever emoji’s or text you want in what get’s displayed…

This is not quite so simple as the comments above seem to indicate due to the need to reconcile the date of Birth to the current year.

Is your Birthday field a “Date” field (it includes the “Year” of birth), or is it a “Single Line Text” field that just has the month/day “5/29”?

Hi Jeremy, I’m wondering if you could revisit this to help me out with a current issue I’m trying to figure out? Ideally, my Birthday field would be a Single Line Text field that just has the month/day… could you give me some direction from there? If that doesn’t work, I’ll add 2020 as the birth year for all and make it a Date field. I’m not wanting the same outcome as the above user… I’m just wanting to create a field with a date that will be used to automate an email with notification of an upcoming birthday. I greatly appreciate any help!!

Hi @Jenn_Salsgiver

You could have your text input field where you enter the month/day:

| Birthday |

------------

 11/02

(for the formula below, it’s important that “day” always has 2 digits, so a leading zero for 1-9)

And then have a formula field that converts that into a proper date for you – this formula field could be hidden.

IF(

  {Birthday},

  DATETIME_PARSE(

    {Birthday},

    'MM/DD'

  )

)

This will always produce the month and day defined in the Birthday field, combined with the current year. And now you can have your simple text input, but use the formula field for your automation. Is that what you are looking for?

Yes! Amazing, thank you for helping out a newbie! One last question as I move on, I also want to automate an email for an anniversary situation, but we do have the year for that one, so it’s a date field. Would you create the formula that I would use to convert that field to be used in the automation? I appreciate your help so much!

Yes! Amazing, thank you for helping out a newbie! One last question as I move on, I also want to automate an email for an anniversary situation, but we do have the year for that one, so it’s a date field. Would you create the formula that I would use to convert that field to be used in the automation? I appreciate your help so much!

IF(

  {HTM Anniversary},

  DATETIME_PARSE(

    MONTH({HTM Anniversary}) &

    '/' & DAY({HTM Anniversary}),

    'MM/DD'

  )

)