Airtable Date Formulas with Examples and Scenarios
I wrote a post a few weeks back covering the datetime fields in Airtable but wanted to go a step further and show you how you can use the fields along with formulas to really bring your table to life and view dates and time in a more meaningful way.
Unit Specifier
Before diving into the formulas themselves, it is important to know about ‘unit specifiers’, as these will help give you control when comparing dates and times. If you want to add days or time to the future or look in the past from a date, you will need to specify which unit you want for the number.
For example if I wanted to add 4 from today, does that mean 4 days, 4 hours, 4 minutes or 4 years? Using the unit specifier will allow us to declare exactly what we mean, so if I wanted it to be 4 weeks from today, the formula would be:
DATEADD({{datetimefield}}, 4, ‘weeks’ (or the abbreviation of ‘w’).Getting Information About a Date or Time
You may want to get information about date or time data and display it on its own, in a friendly and easy to view way. These formulas allow you to essentially break a datetime field apart and get the information you need for filtering and grouping.
For instance, let’s say you’re using Airtable to store data about appointments and you want to see what the busiest time of day has been across all of your records. You could use the HOUR function and it would return a numeric output of between 0 (midnight) and 23 (11 o’clock pm). You could then group by this field and see the count of how many appointments you had each hour of the day.
The WEEKDAY() formula will return which day of the week a record is for, with 0 being Sunday and 6 being Saturday. while WEEKNUM() will return which week of the year it is, so the end of March would be about week 12. You can also specify whether your week starts on a Sunday or Monday.
Date Information Formulas
- YEAR({{datetime field}})
- MONTH({{datetime field}})
- DAY({{datetime field}})
- HOUR({{datetime field}})
- MINUTE{{datetime field}})
- SECOND({{datetime field}})
- WEEKDAY({{datetime field}}, [Sunday or Monday])
- WEEKNUM({{datetime field}}, [Sunday or Monday])
Boolean Results
There are formulas you can use that will compare two dates and return a 1 for ‘true’ and a 0 for ‘false’. Let’s say you have a table that you are using to track award submissions and that all submissions were due by a certain date. You could use the IS_AFTER formula and any true (1) result would let you know that they missed the deadline.
IS_AFTER({Start Date},{Due Date})It would be the same thought process with IS_BEFORE except that you would be looking for anything that happened before a specified date and returning it as true.
- IS_AFTER({{datetime field1}}, {{datetime field2}})
- IS_BEFORE({{datetime field1}}, {{datetime field2}})
- IS_SAME({{datetime field1}}, {{datetime field2}}, unit specifier)
Calculating Differences Between Dates
I think the date calculations formulas are the most useful because they can help you understand dates in relation to other dates and deadlines without having to do the math in your head.
TONOW and FROMNOW
- TONOW({{datetime field}})
- FROMNOW({{datetime field}})
The TONOW and FROMNOW functions are essentially the same. They calculate the number of days either to or from a date field on your base, and the calculation will be the same backwards and forwards. What I mean by that is that it doesn’t matter if you use FROMNOW() with a date that is a week from now it will show 7 days. If you use TONOW() with a date that is a week from now, it will also show 7 days, even though it is 7 days in the future. It probably still make sense to use the correct one for your purpose, so if you want to get the difference in time from something that happened in the past, us TONOW. If you want to see how much time a date is in the future, use FROMNOW, but it really doesn’t matter.
The reason you would use FROMNOW and TONOW is it gives you a clear idea of when an event happened, or is taking place in the future, by spelling it out in clear language. It is easier for me to look at a base and see something is in 7 days than to look at the date and calculate it in my mind. These formulas would be helpful for event planners or even if you have a personal base for remembering important events.
Creating a New Date Based on an Existing Date
- DATEADD({{datetime field}},number, ‘unit specifier’)
Although it has ‘add’ in its name, this formula can add or subtract time from a date field. If you want the field to be in the past, you would just put a minus sign before the number in the formula.
Subtracting From a Date
Let’s say you’re a project manager and for every project, you want to set a soft deadline for two weeks before the project is due. You could use -2, and ‘weeks’ within the formula and it will create a date two weeks prior.
DATEADD({Due Date},-2,'weeks')Add To a Date
Adding a date works in the opposite way and allows you to add a certain amount of time to date. For instance, if you work in HR and want to set a 90 day review for a new hire you could use:
DATEADD({Start Date},90,'days')Work Days With Holidays
These are my favorite formulas, and people who use Airtable for business will find them invaluable. The WORKDAY and WORKDAY_DIFF both work by using an existing date field and either declaring a specific end date or a number of days until a date. The formula will then take out all of the weekends so that you know how many working days you have.
WORKDAY()
This formula would be used if you know something will take X amount of working days to complete. Something may take 30 days to complete but that doesn’t mean it will be done in a month because of weekends and holidays. For example, let’s say that a project kicks off on Wednesday, June 21, 2023 and is expected to take 30 days to complete. If you use the DATEADD function, it wouldn’t be accurate because it would give you a date of July 21, but there are weekends and an Independence Day holiday for us Americans. We can use the WORKDAY formula to choose the start date, add 30 days, and mark down July 3 and 4 as company holidays. The formula is below and returns a date of August 4, 2023:
WORKDAY({Start Date},307,'2023-07-03, 2023-07-04')
WORKDAY_DIFF
The WORKDAY_DIFF is similar except you specify the end date and it calculates how many working days you have to complete. Using the example above and the same project start date, let’s say that the project needs to be done by July 25, using this formula, we would see that we only have 23 days to complete it when figuring in weekends and holidays.
WORKDAY_DIFF({Start Date},{Due Date},'2023-07-03, 2023-07-04')
Working With Other formats
The date field gives you five different out of the box settings to display a date, but you can do even more with the DATETIME_FORMAT formula. For example, if you only wanted to show the last two digits of the year and month for a date, you could use DATETIME_FORMAT({{datetime field}},’YY-MM’).
The DATETIME_PARSE formula allows you to take a text field that has a date formatted in a certain way, and changes it into a date field. For instance, if we had a field that stored the date as text, such as 20230324 (YYYYMMDD),we could parse this out using this formula and will return the date of 3/24/2023 12:00am
DATETIME_PARSE({TEXT DATE},'YYYYMMDD')The DATESTR() and TIMESTR() are straight forward and just convert a date or time into text. This makes it easier to combine dates with other text such as creating naming conventions.
- TIMESTR([{{datetime field}}/timestamp])
- DATESTR({{datetime field}})
- DATETIME_FORMAT({{datetime field}}, ‘[specified output format]’)
- DATETIME_PARSE({{datetime field}}, [‘input format’], [‘locale’])
Setting Dates
There are a couple of different ways to get or set date formats using formulas. While most of these things could be handled in the field settings such as setting time zones or using a created or modified date field, it also gives you flexibility to use it in a formula. You can use the DATETIME_FORMAT in conjunction with some of these to give you more control on the output.
SET_LOCALE & SET_TIMEZONE
You can set these at the field level but let’s say that we have a field that we want based off of the company’s headquarters in the USA but we have a good number of collaborators in Finland. We can use the the SET_LOCALE() function to return the date in Finnish. Using SET_LOCALE() with DATETIME_FORMAT() from above, we can use this formula which produces a result of perjantai, 24. maaliskuuta 2023, klo 11.09, the day, date, and time in Finnish.
DATETIME_FORMAT(SET_LOCALE(Now, 'fi'), 'LLLL')Finally, these last four formulas are helpful as part of nested formulas and saves you from having to create additional fields. Using some of the examples above, you may want to set a date to be two weeks from the created date. You could simply use this formula below and you don’t even need to create a ‘Created time’ field on your table (because it always lives in the background).
DATEADD(CREATED_TIME(),2,'w')Similarly, if you want to see how much time is left until a due date, you can use either NOW() or TODAY(), which would be used if you want accuracy down to the second, or TODAY() which is just 12:00am of a date. This means you don’t need to create an additional field just to store what today’s date is.
WORKDAY_DIFF(NOW(),{Due Date},'2023-07-03, 2023-07-04')There are a lot of ways to use these formulas to display dates and time exactly as you need and will often find that nested date formulas within other formulas, really saves space by not needing to create additional fields.
