Hello everyone,
I have a base with information about individuals (i.e. Date of birth / death etc.) and I would like to configure a field that would let me know if any of these individuals were born or died or published something significant on a specific day I am logging on the base (“today”). Is there a way to configure this?
Thank you in advance for any assistance!
Best answer by Justin_Barrett
I would prefer the same emoji! One of these would be great :spiral_calendar: :heavy_multiplication_x: :red_circle:
There are a couple ways you could approach this, both of which start in a similar fashion.
Start by making a separate “match” field for each date you want to check. For example, you’ve got a {Birthdate} date field (tweak the name in the upcoming examples as needed for your actual base), so make a formula field named {Birthdate Match} using the following formula:
DATETIME_FORMAT(SET_TIMEZONE(Birthdate, "TIMEZONE_IDENTIFIER"), "MMDD") =
DATETIME_FORMAT(SET_TIMEZONE(TODAY(), "TIMEZONE_IDENTIFIER"), "MMDD")
(NOTE: You’ll need to tweak the timezone identifier in this formula to match your local timezone.)
That formula compares the formatted versions (month and day only) of the birthdate and TODAY(), outputting a 1 if they match, and a 0 if they don’t. Why formatted? That’s unfortunately the only way to apply the timezone correction (dates are stored internally relative to GMT; likewise for the TODAY() function), which is necessary to get an accurate match at any time of day. Without that timezone correction, TODAY() would switch to tomorrow somewhere in the middle of your day depending on your timezone’s offset from GMT.
Keep adding “match” fields for the other dates in your table that you want to track. Once you’re done, add one more formula field named something like {Today Match}, using a formula similar to this. For this example, we’ll assume that I’ve added match fields for birthdate, death date, and publish date.
IF(OR({Birthdate Match}, {Death Date Match}, {Publish Date Match}), "🔴")
This will display that emoji if any of those match fields output a 1. If you’ve got more match fields, add them inside the OR() function.
The only downside to this option is that you know when one of your dates matches today, but not which date (or dates) matched.
Another option to consider is to tweak each of the match field formulas to directly display the desired emoji. In that case, you wouldn’t make a {Today Match} field, and you’d know exactly which date(s) matched instantly. Using the {Birthdate Match} formula as an example, here’s the change:
IF(DATETIME_FORMAT(SET_TIMEZONE(Birthdate, "TIMEZONE_IDENTIFIER"), "MMDD") =
DATETIME_FORMAT(SET_TIMEZONE(TODAY(), "TIMEZONE_IDENTIFIER"), "MMDD"), "🔴")
All this does is wrap the IF() function around the date comparison, outputting the emoji directly if there’s a match.
A third option is to combine both of these ideas. Use the modified comparison formula that outputs the emoji directly in each match field, but change the emoji for each match. Maybe use a birthday cake if there’s a birthdate match, a gravestone for the death date match, a book for matching a publishing date, etc. Then the {Today Match} formula would simply combine all of those emojis, so you know at a glance which dates match. That combination formula would look something like this:
{Birthdate Match} & {Death Date Match} & {Publish Date Match}
That way you could hide the individual match fields and only leave {Today Match} visible.
+5That’s definitely doable. Before getting into specifics, though, I’ve got a question: what exactly do you want the output to look like? Do you just want an emoji to pop up when one of the date fields matches the current day? Or do you want a specific message if there’s a match; e.g. “Bob Ross was born today!”?
That’s definitely doable. Before getting into specifics, though, I’ve got a question: what exactly do you want the output to look like? Do you just want an emoji to pop up when one of the date fields matches the current day? Or do you want a specific message if there’s a match; e.g. “Bob Ross was born today!”?
Thank you for your answer! I think an emoji popping up would be great and easily spottable.
+5Thank you for your answer! I think an emoji popping up would be great and easily spottable.
Do you want the same emoji for all matched dates? Or would you prefer a different emoji for each; e.g. a birthday cake for birthdays, a tombstone for deaths, a book for publishing dates, etc.?
I would prefer the same emoji! One of these would be great :spiral_calendar: :heavy_multiplication_x: :red_circle:
+5I would prefer the same emoji! One of these would be great :spiral_calendar: :heavy_multiplication_x: :red_circle:
There are a couple ways you could approach this, both of which start in a similar fashion.
Start by making a separate “match” field for each date you want to check. For example, you’ve got a {Birthdate} date field (tweak the name in the upcoming examples as needed for your actual base), so make a formula field named {Birthdate Match} using the following formula:
DATETIME_FORMAT(SET_TIMEZONE(Birthdate, "TIMEZONE_IDENTIFIER"), "MMDD") =
DATETIME_FORMAT(SET_TIMEZONE(TODAY(), "TIMEZONE_IDENTIFIER"), "MMDD")
(NOTE: You’ll need to tweak the timezone identifier in this formula to match your local timezone.)
That formula compares the formatted versions (month and day only) of the birthdate and TODAY(), outputting a 1 if they match, and a 0 if they don’t. Why formatted? That’s unfortunately the only way to apply the timezone correction (dates are stored internally relative to GMT; likewise for the TODAY() function), which is necessary to get an accurate match at any time of day. Without that timezone correction, TODAY() would switch to tomorrow somewhere in the middle of your day depending on your timezone’s offset from GMT.
Keep adding “match” fields for the other dates in your table that you want to track. Once you’re done, add one more formula field named something like {Today Match}, using a formula similar to this. For this example, we’ll assume that I’ve added match fields for birthdate, death date, and publish date.
IF(OR({Birthdate Match}, {Death Date Match}, {Publish Date Match}), "🔴")
This will display that emoji if any of those match fields output a 1. If you’ve got more match fields, add them inside the OR() function.
The only downside to this option is that you know when one of your dates matches today, but not which date (or dates) matched.
Another option to consider is to tweak each of the match field formulas to directly display the desired emoji. In that case, you wouldn’t make a {Today Match} field, and you’d know exactly which date(s) matched instantly. Using the {Birthdate Match} formula as an example, here’s the change:
IF(DATETIME_FORMAT(SET_TIMEZONE(Birthdate, "TIMEZONE_IDENTIFIER"), "MMDD") =
DATETIME_FORMAT(SET_TIMEZONE(TODAY(), "TIMEZONE_IDENTIFIER"), "MMDD"), "🔴")
All this does is wrap the IF() function around the date comparison, outputting the emoji directly if there’s a match.
A third option is to combine both of these ideas. Use the modified comparison formula that outputs the emoji directly in each match field, but change the emoji for each match. Maybe use a birthday cake if there’s a birthdate match, a gravestone for the death date match, a book for matching a publishing date, etc. Then the {Today Match} formula would simply combine all of those emojis, so you know at a glance which dates match. That combination formula would look something like this:
{Birthdate Match} & {Death Date Match} & {Publish Date Match}
That way you could hide the individual match fields and only leave {Today Match} visible.
Thank you so much! For some reason the second part of the formula does not work, I get a message saying the formula cannot be saved, I’m not sure where I’m doing something wrong? 
+5Thank you so much! For some reason the second part of the formula does not work, I get a message saying the formula cannot be saved, I’m not sure where I’m doing something wrong?
There’s a space between IF and the opening parenthesis. In most programming languages, IF is a statement, so if (condition) output is fine. In Airtable, IF() is a function, and proper syntax for calling a function requires that there be no space between the function name and its opening parenthesis.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
