Tracking dates is essential for prioritizing projects, meeting milestones, and planning for the future. Whatever the task you're trying to tackle, it's likely to involve a key date or two. But keeping deadlines straight across multiple projects, and making sure they're all up to date, can turn into a job of its own.
Here's where Airtable's powerful formulas can step in to automate some of your most repetitive tasks. Instead of counting the days till your next due date, figuring out which contracts expire in Q3, or re-building your holiday calendar every year, let these time-saving formulas help you zero in on what's most important.
Let's take a look at some of the best ways you can harness the power of date and time with some clever date formulas. And don't stop here—take these starting points as inspiration for your own custom formula-building. You'll be powering up your due dates in no time.
Automatically set due dates ⏰
Who wants to set due dates every day or even every week? You can use a formula to do the work for you. Take the DATEADD function and you can add and subtract units of time to automatically calculate additional dates based on the one you supplied. In essence, you can set a due date for your project in one field and use formula fields to calculate prior due dates for drafts or other check-ins. Check it out:
- Due Date (date field): Select your due date
- First Draft (formula field):
DATEADD({Due Date},-14,‘days’)
- Final Draft (formula field):
DATEADD({Due Date},-5,‘days’)
We can also add an IF statement here, so that the due date will only appear if a publication date has been set, to avoid any unsightly error messages:
IF({Due Date},DATEADD({Due Date},-14,'days'))
Naturally, you may have a date field with a different name. If so, just swap out Publication Date with the name of your table's field. Just make sure to include those {curly braces} around the name so Airtable knows you're referencing a field.
Calculate duration ⌚️
When you need to calculate how much time is left before a deadline, or any other duration for that matter, a simple formula can do the work for you. All you need is the DATETIME_DIFF function:
DATETIME_DIFF(TODAY(),{Deadline Date},'days')
What's happening in this formula? Well, the DATETIME_DIFF function calculates the distance between two dates (with time, too, if you need it) and returns that distance in a format of your choosing. We've provided it with three parameters: the “to” date, the “from” date, and the format.
The “to” date uses the TODAY function to simply return the current date and time in a formula Airtable understands perfectly. The “from” date references a date field named Beginning Date, but you can have it reference any date field you like. Finally, we've set the third parameter to 'days' because we want to know the number of days from this very moment until the “from” date occurs. Alternatively, you can specify other measurements, as small as milliseconds and as large as years, if you prefer.
If you want to take this basic formula to the next level, here's one that will count the hours between two dates and ignore the weekends:
IF(DAY({Email received at})=DAY(DATETIME_PARSE({Task first completed on},'YYYY-MM-DD HH:mm')),DATETIME_DIFF({Email received at},{Task first completed on}),DATETIME_DIFF({Email received at},{Task first completed on})-(DATETIME_DIFF({Email received at},{Task first completed on},'days')-WORKDAY_DIFF({Task first completed on},{Email received at})+1)*86400)
This example demonstrates a way to automatically calculate time worked for a specific task. You can customize it or make something similar to work with durations in whatever way it suits your workflow best.
Figure out which date comes first 🔜
When you set a bunch of dates in your Airtable base, it helps to have a formula field that can detect which date comes first so you don't have to constantly compare them all yourself. You only need a couple of IF statements and date comparison functions to get the job done:
IF(IF(XOR(BLANK(),{Expiration Date},{Date})=1,"",IF(OR({Date},Expiration Date)=1,IS_BEFORE({Expiration Date},{Date}),"")),"⚠️ Expired","Ok")
As you can see, the IS_BEFORE function checks which date comes first. In this example, we're comparing the fields {Expiration Date} and Date, which you can change to fields of your choosing if you decide to employ this formula in one (or many) of your bases. The IF statements help by resolving to a message that immediately tells you if the specified date (Date) exceeds the expiration date (Expiration Date). If expired, the message also includes a warning emoji for added effect. Airtable supports emoji in many places, including strings of text used in your formulas, so don't hesitate to use them as much as you'd like!
Create recurring events 🔄
Whether you make use of Airtable's robust calendar view to track holidays, company milestones, or celebrity birthdays, you may want to make adding recurring events easier. This formula can make quick work of that task:
DATETIME_PARSE(DATETIME_FORMAT({Recurring Date},'MM/DD')&"/"&YEAR(NOW()))
This formula looks at a date field called Recurring Date and strips the year away using the DATETIME_FORMAT function. It then adds the current year using the YEAR and NOW functions before parsing the newly-formulated date using the DATETIME_PARSE function so that Airtable knows to consider it a date and not just a text string you concatenated. This way you never have to update the event this record pertains to because it will automatically update itself whenever the new year rolls around.
Check the day of the week (or the week or quarter of the year) 🗓
You probably know the current day of the week, or perhaps even the number of the week, but does your base know it as well? It can! Sometimes you need to check if it's a Tuesday, check for every 13th week to note the end of a quarter year, or calculate similar information for a variety of other reasons. Whether you have a specific use case or just love Tuesdays, you can use the same type of formula to figure it out.
Here's a basic formula to check if a particular date field contains a Tuesday and display a message accordingly:
IF(WEEKDAY({Date})=2,'Happy Tuesday!','Not Tuesday yet... :(')
As you can see, the formula looks at a field appropriately-titled Date, processes it with the WEEKDAY() function, and if that function returns a 2 (Monday = 1, Tuesday = 2, Wednesday = 3, etc.) then the IF statement will display “Happy Tuesday!” Alternatively, using a SWITCH statement, you can display a message for any day of the week:
SWITCH(WEEKDAY({Date}),0,'Happy Sunday!',1,'You have a case of the Mondays...',2,'Hooray for Tuesday!',3,'Happy Hump Day!',4,'#ThrowbackThursday',5,'TGIF',6,'Relax on Saturday')
If you want a more generic message that just dynamically inserts the day name, you can use this formula instead:
"Happy "& DATETIME_FORMAT({Date}, 'dddd')&"!"
If you'd like to calculate the week number to perform similar checks, you only need the following function to process a date field of your choosing:
WEEKNUM({Date})
Finally, if you wish to check week numbers to determine which quarter of the year it is, this formula will take care of that task directly by simply telling the DATETIME_FORMAT function to format a given date based on that specific measurement:
CONCATENATE('Q',DATETIME_FORMAT({Date},'Q'))
With these formulas, your base can always know the day of the week or week of the year for whatever purpose you require.
Find the next occurrence of a specific day ⏳
Perhaps you don't need your base to know what day it is, but rather when the next specific day—perhaps a Tuesday?—will occur. This formula can tackle that for you:
IF(2-WEEKDAY({Date})<=0,DATEADD({Date},2-(WEEKDAY({Date}))+7,'days'),DATEADD({Date},2-(WEEKDAY({Date})),'days'))
Naturally, Date represents the field containing the specific date and you can change it to any date field in your base. This formula will return the date of the next coming Tuesday, so you can format the results however you like in the formatting options for the formula field.
Of course, you might need to figure out the next Wednesday or Thursday instead of Tuesday and you can change the formula to work with any date easily. Because Tuesday represents the second day of the week, you'll find a lot of 2s in the formula above. Simply change them to the number that corresponds to the day of the week you want. Just don't change any of the other numbers, such as 0 and 7! Those need to remain the same for a reliable calculation.
That formula handles dates in the future, but if you just need to know the nearest Tuesday to a given date—even if that Tuesday has already passed—this simpler formula will do the trick:
SET_TIMEZONE(DATEADD({Date},2-(WEEKDAY({Date})),'days'),'America/Los_Angeles')
Again, you just need to change the 2 in this formula to the number of the day of the week you want if you need a non-Tuesday-related formula. Also, please note that when incorporating date fields that also track time you may need to use the SET_TIMEZONE function to your timezone like you can see in the above example. If you need a list of timezones you can use, you can find them all at the Airtable Support site.
Display the “week of” for a given date 📅
When organizing tasks, it often helps to know which week they belong to so you (and your team) can focus on the tasks most relevant. While we previously discussed how to use the WEEKNUM function to get the week's number, this formula will figure out the date a given week begins on automatically:
CONCATENATE("Week of"," ",DATETIME_FORMAT(DATEADD({Date},-DATETIME_FORMAT({Date},'e'),'days'),'M/D'))
Aside from the aforementioned benefit of noting the week, you can also group by the results so you can see a weekly task agenda for everyone or use an additional grouping rule to create task agendas for individual team members as well. To learn more about the versatile powers of grouped records, check out the official guide.
Filter records based on complex date information 🔍
Airtable comes with powerful filters that allow you to dynamically hide any records that don't match specific criteria, but sometimes you'll want to filter on a specific date range and a formula can make that possible.
Let's say you want to find out if a date exists within the current week. To do that, we need to know when Monday and Sunday occur this week. First, let's find Monday. To do that, we need the TODAY function to figure out what day it is right now, the WEEKDAY function to turn that date into a day number (e.g. 2 for Tuesday, 4 for Thursday, etc.), and force it to become a Monday by using the DATEADD function to subtract the number of days that have passed since the most recent Monday:
DATEADD(TODAY(), -(WEEKDAY(TODAY())-1), 'days')
That's a lot to think about but it doesn't take much to write as a formula. The formula simply figures out the current day number of the week and subtracts the number of days necessary to reduce that number to a one—the day that represents Monday, also known as the first day of the week. Now we need to do the same for Sunday, the last day of the week:
DATEADD(TODAY(),-(WEEKDAY(TODAY())-7),'days')
Those formulas offer the date range we need but we also need to combine those formulas using an IF statement in order to check if another date field falls into that range. We'll check on the field {Date} in this example:
AND(DATETIME_FORMAT({Date},'X')>=DATETIME_FORMAT(DATEADD(TODAY(),-(WEEKDAY(TODAY())-1),'days'),'X'),DATETIME_FORMAT({Date},'X')<=DATETIME_FORMAT(DATEADD(TODAY(),-(WEEKDAY(TODAY())-7),'days'),'X'))
As you might have noticed, this kind of comparison requires a few extra steps. First, we have to create a conditional statement to discover if the {Date} occurs after this week's Monday. Second, we have to create a second conditional statement to do the same thing for this week's Sunday. Finally, both statements get sent to the AND function which tests to make sure both statements are true. If they are, the formula field will display a 1 and, if not, it will display a 0 instead. If you get a 1, the date is within the specified range! Now you can use this information to sort, filter, and group records based on their assigned date.
Now, let's take a look at a more specific use case that utilizes a slightly different approach. If you're the editorial manager of a publication and want to see only the articles that will be published during the current weekend you can use a formula like this:
OR(IS_SAME({Date},DATEADD(TODAY(),IF(WEEKDAY(TODAY())=0,-1,6-WEEKDAY(TODAY())),'days')),IS_SAME({Date},DATEADD(TODAY(),IF(WEEKDAY(TODAY())=0,0,7-WEEKDAY(TODAY())),'days')))
This formula checks a field named Date to find out if it occurs on a weekday or not. If it doesn't, the formula returns a 0. If it does, the formula instead returns a 1. By filtering out all the records that display a 0, you can quickly create a view for your table that only shows the articles scheduled to be published on the current weekend.
Calculate the priority of a task based on its due date (and other factors) ‼️
When trying to figure out the priority of a particular task, it usually amounts to at least two things: the due date and its abstract level of urgency (via a numerical rating). You can have a date field that includes the due date and a rating field to set the urgency, but you'll also want a formula field to combine the two and calculate the task's priority based on both elements. Here's a formula to do just that:
(DATETIME_DIFF({Due},TODAY(),'hours'))*(1+(IF({Urgency}=BLANK(),0,{Urgency})/10))
The Due field holds the due date and Urgency field holds the urgency rating, naturally. The formula sorts out if the task is almost due, due, or past due, and calculates an urgency score that can be used to sort tasks more precisely.
A similar effect can be accomplished through sorting, but using a formula allows you to create groups organized by priority that will automatically sort themselves so you don't have to. Beyond that, you can create urgency groupings and filter out tasks with low urgency scores. This formula, and similar ones, allow for more precise organization options in the views you create.
Need more or have a great formula of your own? 💡
If you've got a great date-related formula, or just a great formula in general, share your knowledge in the Airtable community forums! Alternatively, if you're looking for a formula to perform a specific function but don't know how to create it, you can post there as well.