Introduction to Date Formulas in Airtable

Published on April 16, 2021

Whether you’re building a job board, an event directory, or a portfolio on Softr, chances are you’ll want to include dates. After all, they are instrumental in most modern services, from booking platforms to project management software, event planning websites, and more.


But dealing with date formulas in Airtable can be overwhelming. Dealing with formula fields is intimidating at first, and knowing where to start is not always easy.


In this article, we will go over what date formulas are, how they work, and what are the main functions you should know to be able to leverage all their power. Let’s start from the beginning.

The Basics of Airtable Date Formulas 


In Airtable, formulas allow you to compute values based on the content of other fields, or even other tables. They are a powerful way to get more out of your data and to automate a lot of the calculations however complicated or mundane you might need to interpret it. 


Available as a default type when creating a new field, you can write formulas directly in the field’s text box, as well as select its formatting:

airtable formula type field

Formulas can contain functions, numeric operations, logical operations, and text operations. When it comes to date formulas in particular, they are a great way to go beyond the 4 main types of date and time fields:


  • Created time field: Automatically show the date (and time) that a record was created;
  • Date (and time) field: Allows users to easily enter a date (and a time) into a cell;
  • Duration field type: Indicates time durations measured in hours, minutes, seconds, or milliseconds;
  • Last modified time field: Automatically returns the most recent date and time that a record was modified by a user.


While useful, we can agree that these fields are limited in providing advanced contextual information. Using formulas allows you take it a step further by creating custom fields tailored to your needs. A few examples could be:


  • Indicating when a project is due;
  • Sort rows based on time-related priority; 
  • Displaying a countdown;
  • Creating recurring events.


The list goes on. The possibilities are only limited by your imagination and the scope of your projects. 


It may seem like a daunting task to start putting together formulas by yourself, but don’t worry, there’s no need to be a maths genius here. Airtable features a number of functions that can serve as a base to your formula experiments and plenty of tested formulas for you to have fun with.

Leveraging Functions in Airtable


Functions can be defined as pre-existing formula elements that perform specific values in a particular order. You can find them directly under the text box as suggestions when typing your formula. 


Don’t worry, you don’t need to know them all by heart or to keep a cheat sheet at all times. Upon selecting on the function you want to use, a short definition will be displayed, along with the syntax and operators you need to use for the formula:

airtable syntax formula

When it comes to date functions specifically, they can fit into two categories: 


  1. Returning a specified value. Example: If you wanted to get the month of every due date in a table, you could use: MONTH({Due date})
  2. Formatting datetimes into specific strings. Example: If you wanted to display the current date in a YYYY-MM-DD format, you could use: DATESTR(NOW())


Now that we understand formulas and functions, let's take a look at the date functions you need to start playing with date formulas.

The 5 Main Airtable Date Formulas You Need to Know 


In this section, we will go over the 5 basic date functions you need to master to get started with date formulas. They will serve as a stepping stone for all your date formula needs. 

1. Calculating the difference between two dates: DATETIME_DIFF()


The DATETIME_DIFF() function can help you calculate the difference between two different dates. The formula syntax shows as follows:


DATETIME_DIFF([date1], [date2], 'units')

Example: DATETIME_DIFF({Start date}, TODAY, days)


The difference will be calculated by subtracting [date2] from [date1]. The output by default is in seconds, but replacing the "units" specifier will allow you to choose a different one. Check out the following table of the type of specifiers and their abbreviations for your reference.


Bookmark this: Unit specifiers for the Date time difference function

type of specifiers and their abbreviations

2. Adding to a date: DATEADD() 


This function allows you to add a specified count of your choice to an existing date and time. All you need is an existing date field, what count you want to apply, and in which unit (you can refer to the table above or type out the unit in full letters):


DATEADD([date], [#], 'units')

Example: DATEADD({Deadline}, -1, 'month')


This can be useful in plenty of ways, especially coupled with other functions to get intricate formulas.

3. Converting data into a structured date: DATETIME_PARSE()


You might be importing values from an external source into a format that Airtable cannot read as a date. If that’s the case unfortunately, you won’t be able to use that field in other formulas as a result.


The DATETIME_PARSE() allows you to convert that text value into a readable date for Airtable. Check out the formula syntax and an example:


DATETIME_PARSE(date, ['input format'], ['locale'])

Example: DATETIME_PARSE("4 Mar 2017 23:00", 'D MMM YYYY HH:mm')


Once the text has been converted by the formula to a readable date format, it can be used anywhere just like a regular date field. By default, the date will be converted into a ‘M/D/YYYY h:mm a’ format (ex: 4/5/2021 05:54pm). If you want to use another format, make sure to refer to the next section.

4. Changing the format of date fields: DATETIME_FORMAT


Different projects and collaborators require different date formats. As you know, people from around the world don’t necessarily write their dates the same way, and some of your bases will require date and time when some others won’t.


Date fields provide formatting options already, but when combining date fields and other types, the resulting data will need to be edited for display or you might end up with something like this:

airtable datetime format complicated

The DATETIME_FORMAT function allows you to format date fields how you like. The syntax is as follows:


DATETIME_FORMAT([date], '[output format]')

Example: DATETIME_FORMAT(NOW(),'YYYY-MM')


Bookmark this: The most common keys for dates in Airtable

The number of supported date and time formats is very large, and you can find the total list on this Airtable support page. For practicality’s sake, we’ve put together this table of the most useful shortcuts for you to refer to when needed:

most common shortcuts for dates in airtable

5. Timezone example


The SET_TIMEZONE function allows you to set a timezone for all the data in a date field. This is very useful with collaborators scattered around the world, and is particularly relevant as remote work becomes more widespread. The syntax goes as follows:


SET_TIMEZONE(Datetime, timezone) 

Example: SET_TIMEZONE({Appointment time}, 'Europe/Oslo’)


By default, the timezone in Airtable will match the device you’re using. You can select the "Use the same time zone (GMT) for all collaborators" option in a date field to display the same time for all collaborators across timezones:

airtable timezone all collaborators

To see the complete list of supported timezone identifiers, please check out this article on Airtable support.

Conclusion


After going over the main types of date formulas in Airtable, we hope that you have a better grip on the topic and are inspired to try and come up with your own formulas. Let’s just go over them one last time:


  • DATETIME_DIFF() helps calculating out the difference between two dates;
  • DATEADD() adds a specified count to a datetime;
  • DATETIME_PARSE() converts a text string to a structured date;
  • DATE_FORMAT() displays a date in a format of your choice;
  • SET_TIMEZONE() sets a timezone for a date.


Keep in mind that this is only the beginning! Now that you have a firm understanding of the topic, it’s up to you to experiment, mix and match these functions, and come up with formulas that fit your needs. 


For more formula inspiration, join our Slack group to exchange with other Softr users and check out our article on the 10 must-know airtable date formulas.


Happy formulating!


by Thierry Maout

About Softr


Softr is an easy-to-use no-code platform that turns Airtable bases into powerful web apps and client portals. Softr offers a way for you to authenticate your end-users, control access to your content and data based on conditional rules like roles, logged-in status, subscription plans etc. If you're using Airtable as a product catalog you can use a Softr template to build your e-commerce website. Or maybe you'd like to build a custom website for your travel journal, there's a template for that too!