Upcoming Birthday Notification

[Evan_Pitchie]

I’m creating an employee directory and I’d like to have a record (cell) display an emoji or change colors one week before that person’s birthday. Has anyone figured out how to do that?

[Justin_Barrett]

It’s literally just wrapping a single IF() function around the longer formula I wrote above. Assuming the birthday date field in your base is named Birthday, it should work. Just for completeness, though, here’s the whole shebang:

IF(Birthday,
    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,
            "🎂"
        )
    )
)

[Andre_Zijlstra]

Hi Justin, what software do you use to build this formula? I tried to do this in a simple texteditor but the tabs are positioned differently.

Regards,
André

[Steffen_Pidun]

Thanks Justin, your help and time is very much appreciated :slightly_smiling_face:

[Justin_Barrett]

I use BBEdit on the Mac for editing complex formulas. If it’s not nested too deeply, I’ll do it directly in the Airtable editor. Depending on your editor, you may be able to edit the tab settings to your liking.

[High_Noon]

Thank you so much! I’m so happy with this. You make my day :slightly_smiling_face: :slightly_smiling_face:

[Tessa_Kriesel]

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

[kuovonne]

Welcome to the Airtable community, @Tessa_Kriesel

This thread is about a formula field, not a script. To use it, you need a date field for a birthday, named Birthday. Then create a new formula field and paste in the formula.

[Louise_Nighting]

@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.

[Justin_Barrett]

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. :slightly_smiling_face: 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.

[Louise_Nighting]

No worries @Justin_Barrett - I was hoping to only show an emoji for upcoming birthdays, not birthdays in the past. Appreciate you responding despite being swamped!