12 Must-Know Airtable Date Formulas

Published on April 20, 2021

Dates are a cornerstone of any database. You’d be hard-pressed to think of a project that doesn’t include at least a date field. They are key to leveraging data in a variety of ways:


  • Sorting projects;
  • Prioritizing tasks;
  • Grouping events;
  • Providing guidelines.

 

And the list goes on. Date formulas are a great way for Airtable users to go beyond traditional date/time fields and supercharge their bases. 


But because they’re so powerful, they can also be confusing at times and complicated to start using. In this article, we will provide you with 12 Airtable date formulas you can easily implement to take your tables to the next level. But before we dive into formulas themselves, let’s take a brief look at how they work.

Create a Webapp in Minutes
Custom domain included 😉

Airtable Formulas 101


Formulas allow Airtable users to compute values based on the content of certain fields, to automate calculations. You can write them by selecting the formula field type when creating a new field in your table, and follow the guidance from a tooltip that will assist you with syntax and purpose:

airtable formula example

A very basic example could be calculating the wage of an employee in a {Weekly Wage} field by leveraging two other fields:


{Hours Worked} * {Hourly Wage}


When it comes to dates, things get a bit hairier, as formatting can make calculations challenging. Before diving too deep, you should already be familiar with Airtable formulas and the basics of date functions. If that’s not the case, check out this article covering everything you need to know about date formulas. 


We’ve gathered 12 of the most useful date formulas you can implement, starting with the essential existing functions you can already select in Airtable and progressively diving into more creative, advanced tricks.


Now let’s take a look at some formulas!

1. Change Your Date Format


Let’s start with the basics and an essential formula to make sure your dates are displayed correctly. Whether you work with international collaborators or need extra details included in your dates, DATETIME_FORMAT() will help you get the result you’re looking for while avoiding crazy-looking date fields. And the best part is it’s pretty straightforward: 


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

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


While you have the option to easily select a default format when creating a date field in Airtable, this formula allows you to dive much deeper and generate whatever date format you can think of. To give you an example, these are all taken from the same date field, but in a different format:


  • 4:34 PM
  • 09/23/2021
  • Sep 23, 2021 4:34 PM
  • Thursday, September 23, 2021 4:34 PM
  • 1632407640


Check out our introduction article to access a table of the most common keys to use with this formula.

2. Calculate the Duration of an Event 


Another straightforward formula you need to know, DATETIME_DIFF() allows you to calculate the difference between two dates in order to determine the duration of an event. Similar to the previous example, this is a preset function that you can easily access when creating a new formula field:

datetime difference formula

For this formula to work, you need to have two existing date/time fields in your table and use the following syntax:


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

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


Note: Here, we’re calculating the duration between two events in days, but you could use any other time measurement whether it’s weeks, months, or even minutes and hours.

3. Automatically Add to a Date


Another essential, the DATEADD() formula allows you to add a count to a date and time in your table. To apply this other pre-existing Airtable function, you need another date field in your table and an idea what to do with it (count and unit). The syntax is as follows:


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

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


There’s a lot of ways to leverage this useful function, some of which you’ll discover later in this list.

4. Turning Text into a Legible Date


Wrapping up the basic functions, DATETIME_PARSE() helps turn a text field into a legible date formula. This comes in handy when importing values from another source, or for dates that are simply not readable as such by Airtable. 


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

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


The result of this formula is a readable date field, which can be used anywhere in your other formulas and calculations. 


Note: The default format for the resulting field will be ‘M/D/YYYY h:mm a’ (ex: 7/23/2021 16:34pm). This can be adjusted using the first formula on the list.

5. Setting up Recurring Yearly Events


Now that we’ve covered the essential functions, let’s get into some more advanced practices. Are you using the calendar view in Airtable? It’s a great way to visualize dates and organize projects and can easily be improved using the right formulas.

airtable calendar view

(source: The Deep Listen)



One of the basic features of any worthy digital calendar is the possibility to set up recurring events. Whether it’s birthdays, celebrations, or anniversaries, you can do the same thing in Airtable by leveraging the DATETIME_FORMAT and DATETIME_PARSE functions that we went over earlier in this article.


First, we have to pick an existing date field, strip it of its year, and make sure the displayed date fits the current year. Use this formula to do so:


DATETIME_PARSE(DATETIME_FORMAT({Date},'MM/DD')&"/"&YEAR(NOW()))


Every year, the records will automatically update to reflect the current year, and you won’t have to create them again. This can be very useful to automatically display the latest year on some parts of your website (e.g. Terms of Services, Copyright, etc).

6. Automatically Display and Update Deadlines in Work Days


Deadlines are a great way to stay on track and incentivize stakeholders, but you know what’s even better? A countdown to a deadline.

airtable calendar view

DATETIME_DIFF() serves a similar purpose and can be used to create a countdown field, but it might not reflect the actual time left, since it would include weekends. That’s when the WORKDAY_DIFF() function comes in handy by excluding Saturdays and Sundays from calculations.


The following syntax allows you to display the number of working days left until the date of your choice:


WORKDAY_DIFF(TODAY(),Date)


Note: When using WORKDAY_DIFF(), remember that the formula counts both the start and end day in the total number of days. 


Additionally, you can choose to exclude bank holidays and/or other specific dates from your countdown. For that, you have to manually add them in the formula, after the “Date:”


WORKDAY_DIFF(TODAY(),Date,'2021-09-23, 2022-01-21, 2022-08-19')

7. Automatically Populate Your Project with Due Dates 


Depending on your activity, due dates might be something you have to add over and over again:


  • Agencies working with the same clients;
  • Recruiters with fixed deadlines;
  • Marketers running campaigns for A/B testing;
  • Content writing teams. 


And the list goes on. The point is that if the timeline is always the same, setting up the different steps in the process (first draft, editing, confirmation, or last review, for example) every single time can get time-consuming. Let’s automate that with the DATEADD() function we discussed earlier.


First, you need to create a final due date field in your table, and a field for each of the steps in your process: first draft, first review, revision, editing, etc., whatever the case might be. From there, the formula helps populate these fields relative to the due date. 


For example, if your first draft has to systematically be submitted 7 days before the final due date, use this formula:


DATEADD({Final Due Date},-7,‘days’)


In this instance, DATEADD() can help automatically map out the strategic dates for each project based on your established timeline, reducing the need for mindless number crunching on your end. 


This is especially useful for collaborative tables, where work has to go through several hands in a specific, timely process. Your project manager will thank you!

8. Generate the Week, Month, or Quarter Number


Let’s dial the difficulty back with a simple way to prioritize projects: breaking dates down to the position of that date in the year in order to group records, organize dates and sort tasks for relevancy. 


Using the CONCATENATE() function along with DATETIME_FORMAT(), you are able to determine the number of the day, week, month, or even quarter for that date. To get the number of the quarter for a date for example, use this formula:


CONCATENATE("Q",DATETIME_FORMAT(Date,"Q YYYY"))


You can then swap out “Q” for “W” to get the week number or “DDD” for the day number for example, and play with the formatting to get exactly what you’re looking for. See our introduction to date formulas for a rundown of the most useful date shortcuts.

airtable day week month quarter formula.e alt

9. Display Specific Messages Based on the Day of the Week


This is a fun formula, especially useful for Softr users that want to display a specific message on their site depending on the current day, month or time. 


Using the SWITCH() and WEEKDAY() functions, can configure a specific message for each day of the week for example:


SWITCH(WEEKDAY({Date}),0,’Sunday fun day!',1,'#MondayMotivation',2,'Have a good Tuesday!',3,'Happy Hump Day!',4,'#TBT',5,'Thank God it’s Friday',6,'Enjoy your weekend')

Create a Webapp in Minutes
Custom domain included 😉

10. Using IF Formulas Along With Date Fields


IF() formulas are a great way to add some conditional logic to your base. We didn’t really cover them in the list so far, as they are especially useful when used in concordance with other formulas to add a degree of depth and personalization.


The syntax of an IF() formula is quite straightforward, especially with dates. 


IF(logical, value1, value2) 

Explanation: The formula returns value 1 if the logical argument is true, otherwise it returns value2.


When it comes to dates, an IF() formula could help you automatically determine whether a given date is before, after, or the same as another date:

if formula with dates

This could be useful to display a specific time-related message on your site, such as a promotion or a celebration. For the “Is it today?” field in the example above, the syntax would be as follow:


IF(IS_SAME(Date,TODAY(),'days'),"It's today","It's not today")

11. Set Time Zones and Locales


Working with collaborators scattered throughout the world is getting more common by the day, especially with remote work becoming a widespread practice in modern companies. This comes with a set of challenges of course, but Airtable formulas can help ensure mixing up timezones isn’t one of them! 


By default, Airtable stores your data using Greenwich Mean Time (GMT). But it’s up to you to choose between two options to determine how dates will be displayed in your base:


  • The same for every collaborator, by toggling on the “Use the same time zone (GMT for all collaborators” option when setting up a date field;
  • Differently for each collaborators, based on their local timezone collaborators.


This has to be set on a field-by field basis. Let’s say you want one field to be displayed in a specific time zone for everyone. That’s where the SET_TIMEZONE() function comes in, alongside the list of supported timezones that you can access here:


SET_TIMEZONE(Datetime, timezone) 

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


This will allow you to change a date to display it in different timezones for example.

timezone example

Image source: ScottWorld



If we take it a step further, Airtable can also allow you to set a locale, changing the date format to a specific language or region. As you can guess, a locale modifiers can impact several aspects of a date and time:


  • Numerical digits
  • Script direction
  • Names
  • Long date format


This function uses the syntax, along with a locale modifier, of which you can find the completed list here:


SET_LOCALE(datetime, 'locale modifier')


Coupled with the DATETIME_FORMAT() to display more information, it allows you to accommodate and cater to every collaborator in your base:

airtable timezone locale

12. Convert Your Date Field to Use It in a Text Field


Last but not least, let’s turn date fields into text fields! As a Softr user, you might not always want to display a raw date, especially in customer-facing projects. If you’re looking to convert a date to the day of the week, and seamlessly insert it within a text field for example, DATETIME_FORMAT() can help with that:


Syntax: "It’s already "& DATETIME_FORMAT({Date}, 'dddd')&"!"

Result displayed if the date field is a Monday: “It’s already Monday!”


You can also go a bit deeper, and throw DATEADD() into the mix. As an example, let’s say we want to set up an automated message that always says that a given product will be back in stock the next month:


Syntax: "Our product will be back in stock in "& DATETIME_FORMAT(DATEADD({Date}, 1, 'month'), 'MMMM')&"!"

Result displayed if the date is in April: “Our product will be back in stock in May” 


The possibilities are endless here, and can not only save you a lot of time but also be a lot of fun, both for your end users getting a personalized experience, and for you once you go down the rabbit hole of experimentation.


Note: Due to the shortcuts and limitations of Airtable’s API, you might have to leverage this formula for your data to navigate between Airtable and Softr. Please take a look at our article on the topic to learn more.

Conclusion


With these 12 formulas in your arsenal, you’re ready to tackle any project featuring dates that you might have in mind. Airtable is a powerful tool and these will serve as a great foundation for your own experimentations. Before jumping the gun and playing with date formulas, however, remember to:


  • Keep it short and simple: The 5 main date functions cover a lot of ground already, don’t overcomplicate things;
  • Think ahead: Consider what you want to achieve carefully before starting to mess around with formulas, or you’ll waste a lot of time and energy aimlessly; 
  • Get the formatting right: Always consider the format of your date fields to avoid confusion and make it clear for everyone. You have a formula for it now, no excuse!
  • Have fun: Experiment, play around, and share your findings in our Slack group. 


Date formulas are a great way to take your tables to the next level and get more out of your data. We hope this list helps and we look forward to seeing their impact on your creations on Softr!


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!