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?

[Wolfgang_Derigo]

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

[Jenn_Salsgiver]

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!!

[Jeremy_Oglesby]

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?

[Jenn_Salsgiver]

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!

[Jenn_Salsgiver]

The date field is named HTM Anniversary

[Jeremy_Oglesby]

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

[Jenn_Salsgiver]

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!