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?
Assuming you’ve created a
Whatever) this could work (I didn’t really test it) by adding formula field to your table:
If it works, the
’L’ can be change to your liking
There are a lot of threads like this, the easiest way is using
Yes a Birthday column is created but unfortunately it didn’t work
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.
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”?
It’s a “Date” field MM/DD/YYYY.
I’m using the Employee Directory template and I wanted to add an additional column that would put an emoji.
Could it be that it’s not working because I’m on the free version?
No, you can do this with the free version.
It’s just that, when you compare dates with a formula like
DATETIME_DIFF(), it’s going to take the year into account. You want to compare to that employees “Next Birthday”, not their actual “Birth Date”, to get a notification of “1 Week” until their Birthday. It’s just a little more involved, and there are actually a few different approaches you could take. I’m working on what might be the best approach right now.
Yea I realized the year might have something to do with it when you mentioned it before. I really appreciate you taking some time to try and help!
I’m sorry … I was too quick and distracted so I didn’t thought about everything .
I’m leaving the space for the experts …
Ok, next question -
In order to do this, I need to calculate the persons age, as well as the date of their next birthday. Would you like those to be explicit fields that you can see in your table, or would you like them wrapped up into a single field that just gives you a notification of their birth week?
Just the notification of their birthday week. Don’t need to display the age
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.
Almost forgot - you need to replace every instance of
'America/Phoenix' (there are 2) in the formula with your timezone from this list:
And, of course, use whatever emoji’s or text you want in what get’s displayed…