Skip to main content

I am new to Airtable and Formulas



I saw a post by Florian_Kleinicke



Who said he had managed something similar, could someone please explain how I use the formula below.



DATETIME_DIFF(DATETIME_FORMAT({Birthday},‘MM-DD’),DATETIME_FORMAT(TODAY(),‘MM-DD’),‘days’)



My problem (which is likely is also a problem here) is that TODAY() won’t update on its own. It stays the same for a while.


Is there a solution to that problem?


I’m using automations to send an email, once the computed difference hits 0.



Ah I just came um with a solution, that might circumvent that problem. I can compute the birthday


DATETIME_PARSE(DATETIME_FORMAT({Birthday},‘MM-DD’),‘MM-DD’)



Hi @Phillip_Silk-Neilsen ,



Formulas is not yet my great trip,


but it seems to me that this post





could help you to achieve your goal, with or without formula.



olπ



Hi @Phillip_Silk-Neilsen ,



Formulas is not yet my great trip,


but it seems to me that this post





could help you to achieve your goal, with or without formula.



olπ


@Olpy_Acaflo



what do you mean computed difference hits 0?


executive summary email will not do as you have to create a calendar base which is separate from my CRM base.



PSN

Try this:



DATETIME_FORMAT(Birthday, "MMDD") = DATETIME_FORMAT(TODAY(), "MMDD")


That will output a 1 when the month and date of the birthday match the current day, and a 0 at all other times. It’s worth noting, though, that TODAY() is based on the current date in GMT, not your local timezone. Depending on where you live, there will probably be some crossover, but it’s going to change at a time other than midnight in your local timezone unless you also live in GMT.



One way around that is to use NOW() instead, and wrap that in SET_TIMEZONE() to force it to your local timezone before formatting. Here’s an example using my local timezone:



DATETIME_FORMAT(Birthday, "MMDD") = DATETIME_FORMAT(SET_TIMEZONE(NOW(), "America/Los_Angeles"), "MMDD")


You can find other timezone specifiers here:



@Olpy_Acaflo



what do you mean computed difference hits 0?


executive summary email will not do as you have to create a calendar base which is separate from my CRM base.



PSN


@Justin_Barrett



Thank you for this, just worked out where to put it



Phillip

Thanks a lot @Justin_Barrett !



Sorry @Phillip_Silk-Neilsen , just coming back now but I do not have better than Justin’s solution !



olπ

Thanks a lot @Justin_Barrett !



Sorry @Phillip_Silk-Neilsen , just coming back now but I do not have better than Justin’s solution !



olπ


@Olpy_Acaflo Have it working now.


For any other newbies the steps are:





  1. Create a new field


  2. Customise field-type to formula


  3. paste in DATETIME_FORMAT({Date of Birth}, “MMDD”) = DATETIME_FORMAT(TODAY(), “MMDD”)


  4. Change {Date of Birth} to match your field.


  5. Create an automation Trigger: When record matches conditions Action: Gmail Send Mail.


    Presto




Thanks to @Justin_Barrett for the correct formula.

@Olpy_Acaflo Have it working now.


For any other newbies the steps are:





  1. Create a new field


  2. Customise field-type to formula


  3. paste in DATETIME_FORMAT({Date of Birth}, “MMDD”) = DATETIME_FORMAT(TODAY(), “MMDD”)


  4. Change {Date of Birth} to match your field.


  5. Create an automation Trigger: When record matches conditions Action: Gmail Send Mail.


    Presto




Thanks to @Justin_Barrett for the correct formula.


@Justin_Barrett Just another quick question in the following formula is there a way that if {Partner Date of Birth} is blank it always returns a 0?



DATETIME_FORMAT({Partner Date of Birth}, “MMDD”) = DATETIME_FORMAT(TODAY(), “MMDD”)



this is because if a client in the base is single, they will not have a partner

@Justin_Barrett Just another quick question in the following formula is there a way that if {Partner Date of Birth} is blank it always returns a 0?



DATETIME_FORMAT({Partner Date of Birth}, “MMDD”) = DATETIME_FORMAT(TODAY(), “MMDD”)



this is because if a client in the base is single, they will not have a partner




Yes. Wrap the first part of that formula in an IF() function so that it’s only executed if {Partner Date of Birth} isn’t empty:



IF({Partner Date of Birth}, DATETIME_FORMAT({Partner Date of Birth}, "MMDD")) = DATETIME_FORMAT(TODAY(), "MMDD")


If there’s no partner date of birth, that IF() function will return an empty string, which won’t match the formatted version of TODAY(), meaning the field will output a 0.



Yes. Wrap the first part of that formula in an IF() function so that it’s only executed if {Partner Date of Birth} isn’t empty:



IF({Partner Date of Birth}, DATETIME_FORMAT({Partner Date of Birth}, "MMDD")) = DATETIME_FORMAT(TODAY(), "MMDD")


If there’s no partner date of birth, that IF() function will return an empty string, which won’t match the formatted version of TODAY(), meaning the field will output a 0.




@Justin_Barrett Thanks a million

Oh, I remember I wanted to do this too! However, I don’t remember the exact formula, because I made it too complicated….

Oh, I remember I wanted to do this too! However, I don’t remember the exact formula, because I made it too complicated….


I had a crazy idea to make Airtable notify me the week before somebody’s birthday, so I could order a gift basket with flowers and candies. Anyways, you should probably use an if-cycle. For example, the formula could look like this: if the date of today corresponds with somebody’s birthday => send a birthday email. It should not be hard.

Hello,

maybe I am late on the question.

Why not use a View called "Today birthdays" and automate on the event "When record enters view"?

Or you can make an automation that starts at midnight for example where "Birthday Date" field is equal to "today".

Reply


Terms & Conditions