Help

Re: Upcoming Birthday Notification

1832 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Evan_Pitchie
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

36 Replies 36

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

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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.

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

Tessa_Kriesel
4 - Data Explorer
4 - Data Explorer

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

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.

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

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!

Hey Jeremy - thanks for this amazing formula - but one question: How can i prevent from seeing “ :tada: :birthday: :tada: ” if the birthday lies in the past - i don’t want to get reminders via automations if the birthday is over.

Thanks Wolfgang

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!

The date field is named HTM Anniversary

IF(
  {HTM Anniversary},
  DATETIME_PARSE(
    MONTH({HTM Anniversary}) &
    '/' & DAY({HTM Anniversary}),
    'MM/DD'
  )
)

Thank you so much! This saved me so much time and head scratching. After I asked, I tried to figure it out myself for about a half hour and barely got anywhere. I have a lot to learn, but I think we have what we need for now. I truly appreciate your help on this!