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.
However, as 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.
Table of Contents:
12 Airtable Date Formulas
- Change Your Date Format
- Calculate the Duration of an Event
- Automatically Add to a Date
- Turning Text into a Legible Date
- Setting up Recurring Yearly Events
- Automatically Display and Update Deadlines in Work Days
- Automatically Populate Your Project with Due Dates
- Generate the Week, Month, or Quarter Number
- Display Specific Messages Based on the Day of the Week
- Using IF Formulas Along With Date Fields
- Set Time Zones and Locales
- Convert Your Date Field to Use It in a Text Field
But before we dive into formulas themselves, let’s take a brief look at how they work.
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.
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 (see below).
When it comes to date functions specifically, they can fit into two categories:
- 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})
- 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. 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.
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:
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.
Softr is the easiest, fastest way to build a website or web app on Airtable.
No designer or developer needed. Zero learning curve.
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.
(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.
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.
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')
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:
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.
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:
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 Softr Community.
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!
About Softr
Softr is an easy-to-use no-code platform that turns Airtable bases into powerful web apps, member-only websites, 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!
