What are your favorite Airtable tips & tricks?

[Becky_Mak]

Hey everyone!

I’m Becky and I’m on the Customer Success team at Airtable. I’m currently collecting Airtable users’ favorite tips and tricks so I wanted to reach out and hear directly from our awesome community here!

What are some Airtable tips and tricks that you can’t live without? Are there any formulas that you wish you knew about earlier? Are there any Blocks that are game changers for you? What about Zapier integrations that are automating your workflow?

One of our favorite tricks? Using the DATEADD formula to automatically return dates after a specified time or date interval. It’s perfect for building out a timeline or schedule for your workflow. For example, let’s say you’re using Airtable to manage a content production cycle where the first draft is due 14 days before the publication date, and the final draft is due 5 days before the publication date. To set this up, you can add the following fields:

• Publication Date (date field): Select your publication date
• First Draft (formula field): DATEADD({Publication Date},-14,‘days’)
• Final Draft (formula field): DATEADD({Publication Date},-5,‘days’)

Let us know your favorites in the comments below!

[Jonathan_Schofi]

Thanks so much for the detailed reply.

As you suggest, I’ll try a pipe character as a placeholder with SUBSTITUTE().

For what it’s worth, Airtable support replied to my request with:

“We are in the process of building out a solution that should handle this [line break] situation cleanly, though it’s a ways off.”

[W_Vann_Hall]

In replying to a different message, I realized I hadn’t mentioned the source of some of my favorite tips and tricks: The list of format specifiers for DATETIME_FORMAT() and DATETIME_PARSE(). In fact, my very first Airtable Community post dealt with a DATETIME_FORMAT() trick.

Two of my current favorites are

1. Getting the epoch day.
   Based on the Unix time since epoch specifier, which returns seconds ('X') or milliseconds ('x') since midnight, January 1, 1970, you can calculate the epoch day, or days since January 1, 1970. This will provide a unique, sequential day value for every date from 1/1/1970 through 1/19/2038, when the Unix date system [currently in use] breaks down. I like it in particular because it supports calculations based on tallies from the previous day, but it doesn’t need manual assistance to help it past New Year’s Eve, as many of us, and algorithms based on the cardinal day, do. The formula —

   INT(VAL(DATETIME_FORMAT({Date},'X'))/86400)

   — can easily be reversed by multiplying epoch day by 86400 and using DATETIME_PARSE():

   DATETIME_FORMAT(
      DATETIME_PARSE(
          ({Epoch Day}*86400)&'',
          'X'
          ),
      'M-D-YYYY h:mm'
      )
   

2. Restating local time as GMT.
   Occasionally, one may need to restate one’s own local time as GMT or need to compare two local times. Clearly, this is done by adding or subtracting the local time zone’s offset from GMT to the base datetime with DATEADD(). But how does one determine the offset — a big, ugly IF() statement mapping timezones to hours before or after GMT? A far easier method is to use DATETIME_FORMAT() with the 'ZZ' specifier, which returns the date’s positive or negative offset in hours and minutes without a separating colon.¹ As a result, you can use the following formula to restate a date as GMT:

   DATEADD(
       NOW(),
       ((VALUE(
           DATETIME_FORMAT(
               SET_TIMEZONE(
                   NOW(),
                   '>>>Timezone Specifier<<<'
                   ),
               'ZZ'
               )
           )/100)*60)+
           MOD(
               VALUE(
                   DATETIME_FORMAT(
                       SET_TIMEZONE(
                           NOW(),
                           '>>>Timezone Specifier<<<'
                           ),
                       'ZZ'
                       )
                   ),
               100
               ),
     'minutes'
     )
   

   Where you see '>>>Timezone Specifier<<<' replace it with the appropriate specifier from this list. If you want to restate a date or time other than NOW() replace NOW() with the appropriate field name.

   Note that if you’re working on an application where you can be assured of never running into any partial-hour outliers — that is, dates from timezones offset from GMT by an amount of hours and minutes — you can use this somewhat simpler formula:

   DATEADD(
       NOW(),
       VALUE(
           DATETIME_FORMAT(
           SET_TIMEZONE(
               NOW(),
               '>>>Timezone Specifier<<<'
               ),
           'ZZ'
           )
           )/100,
       'hours'
       )
   

---

1. My uncle had one of those; he said it was extremely uncomfortable. [ba-Da-BUM!]

[Kim_Trager1]

Is there an external editor you can recommend to write airtable formulas…?
I’m trying to get by with the built in editor, but things are getting very confusing and I think I’ll soon have to ‘graduate’ to an external editor

[W_Vann_Hall]

I started to reply to your question, but Discourse stopped me to say I’d already linked to my go-to editor for Airtable earlier in this thread — so I guess I should point you towards this reply. :winking_face:

[Paul_Leavitt]

Hey Alex, this is super cool, and something I’m looking to implement into my airtable for similar-ish field work jobs. Right now we have the lat/long coordinates for each location, and I’d love to derive weather stats for that based that. But it looks like for your formula’s you used city names. Any chance you know how to translate that?

[Alex_Wolfe1]

Hi Paul :wave: The variables you can use in a formula to build a URL for this will depend on what’s supported by the weather service/website you’re using. Does the website you’re using allow for searching by coordinates? Or, when you open a given location for weather data through the site, are coordinates shown in the URL?

If either of the above is true, you could theoretically build that into your formula :thumbs_up: