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.
