Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,949 Topics
  • 18,277 Replies
5949 Topics
Caitlyn10
Caitlyn10Known Participant
asked in Formulas
Help to calculate days between dates but show 0 if dates are the same

Hi, can someone please help with my formula? I’d like to calculate the number of days between two dates, but if the dates are the same then they should display 0. The formula below works to show the ones with the same date as 0, however it then shows the ones with different dates as being one figure too little. DATETIME_DIFF(  {Post Sale Date},  {Auction Date/Time (Local)},  'days') To fix this I added +1 to the formula, which of course, solves the latter issue but not the issue for same dates need to show 0. DATETIME_DIFF(  {Post Sale Date},  {Auction Date/Time (Local)},  'days') +1 Can someone please assist 😊

272
Caitlyn10
Lea_Castonguay
Lea_CastonguayNew Participant
asked in Formulas
Help with Rollup Field - Getting Individual Records Instead of Combinations

I'm working with a database structure that has three tables: Publications, Artworks, and Individuals. I'm running into an issue with a Rollup field and hoping someone can help me find a solution.My Table Structure:Artworks table: Has a field that references one or more Individuals Publications table: Has a field that references one or more Artworks Publications table: I want to create a Rollup field that shows all Individuals associated with the Artworks in each PublicationThe Problem:When I use a Rollup field with an ARRAYUNIQUE() formula to get the individuals associated with the artworks, I'm getting unexpected results.For example, if a Publication contains 10 Artworks, and each Artwork has 1, 2, or 3 associated Individuals (sometimes different ones), the Rollup field returns each combination of individuals rather than each individual individually.What I Want:I want the Rollup to return each unique Individual associated with any of the Artworks in the Publication, not the combinatio

282
Mike_AutomaticN
J
JillThornNew Participant
asked in Formulas
Date conversion formula for DOBs with a 2 digit year to not appear as future dates.

I am importing three different reports into the same table.  One report’s DOB field provides the date as we wish it to appear, MM/DD/YYYY.  The other two reports provide a DOB format of DD-MMM-YY.  I am using a formula to convert the DD-MMM-YY format into MM/DD/YYYY and then using an automation to move the DOB Calculation into the DOB field.  Some of the converted dates are future years.  The formula correctly converted 23-Sep-97 into 9/23/1997, but 29-Jan-56 became 1/29/2056.  How do I get the formula to create DOBs in the correct century?Some years convert correctly, others not so much.Current formula. 

263
felipe-saucedo
L
Lu71_2Known Participant
posted in Formulas
Having trouble with TODAY() or some other date functions? Results seem off? Don't use them!

TODAY() and some similar functions are broken because they're stuck on Greenwich Mean Time. What it's meant to do is return a value that changes once per day, at the start of the day. It does this by setting the time part of the current date and time to 0 but, because Airtable's internal time zone is GMT/UTC and yours probably isn't, the result of the function will never be the start of your day. Any formulas that rely on it will have flawed results. The effects depend on how far from GMT your time zone is, and how accurate your formulas need to be. During summer in New Zealand, for example, with an offset of +13 hours, it behaves like a YESTERDAY() for more than half of each day. In California it becomes TOMORROW() early every evening. It can wreck reports and automations.Changing the time zone in the formatting of any formula that uses it has no effect on the underlying value. Verify the problem yourself: create a formula field with only TODAY() as the content and set its formatting

1483
Z
Chris_Moore
Chris_MooreParticipating Frequently
asked in Formulas
Formula for mailing addresses

Hey folks, I’m trying to figure something out.I’ve got an old CSV doc that I’ve integrated. Unfortunately, it’s a mess and I’m only a “enough to get by” kind of Airtable coder. I’m a bit stumped.So, I’m trying to make a formula that combines multiple cells to make a condensed long text field for mailing addresses (if possible). Unfortunately, many of these individual cells are empty and there are 562 lines of them. There’s no good rhyme or reason as to what is empty, either. Sometimes it’s address 1 and address 2, sometimes there’s a city and state and nothing else, sometimes all of the information is filled out, so making a straight hierarchal system like I’m more used to seeing doesn’t really work.Example:IF(address 1, <all the info>,   (IF(address 2, <address 2 down>,    (IF(address 3, <address 3  down>,)))Here’s my best effort at what I want to do:Any advice? Thanks in advance!

544
Mike_AutomaticN
W
WimDCNew Participant
asked in Formulas
change-formula

In one of my first projects, I have a table with a multiselect field "names".Can I add a new column with some formula that shows the name that was last added or removed in the "names"-field ? (with automatic update for every change in that names-field) 

804
Mike_AutomaticN
H
husnumuratNew Participant
asked in Formulas
Circular Referencing

Something as simple as retrieving data from previous record and then mathematically operating this field (even within boundaries of cell) seems to take hours. If finding information or operating this platform is so difficult then whats the value.

272
Taha-Jiruwala
0
0800-grizzlyKnown Participant
asked in Formulas
Counting instances of text within text (finding duplicates)?

Hi, We’re battling getting contact duplicates from Meta lead campaigns. Basically people forget they’ve already signed up, and send in a new registration. Since these are Meta, we cannot control anything upstream. A first step is to get our people, who only work in interfaces, alerted that there’s a duplicate to a contact. It would therefore be enough, for now, to achieve a field that tells the user the amount that the current user’s e-mail address can be found in the total base (~2.000 contacts now). I’m trying an approach where I link all contacts to one record in another table, then lookup all e-mail addresses. Back in my main table I link to the second table and lookup the e-mail addresses. I would the like to count the instances of the field “email” in the lookup field where all e-mail addresses are. FIND finds only the first and gives the position, SEARCH does the same. COUNT and COUNTALL do not work on text. How could I get the count? Rgds. Björn

455
ScottWorld
Caitlyn10
Caitlyn10Known Participant
asked in Formulas
IF Formula multiple criteria

Hi, can someone people assist with my formula? This is what I’m currently working with;IF({Post Sale Date},{Post Sale Date},{Event Date}) I want to expand on this to check the ‘Result’ field so it functions as below. IF ‘Post Sale Date’ exists then use ‘Post Sale Date’IF ‘Post Sale Date’ is empty and ‘Result’ is not ‘SUTH’ then leave blank.IF ‘Post Sale Date’ is empty and ‘Result’ is ‘SUTH’ then use ‘Event Date’ Any suggestions would be much appreciated! Thank you

212
Mike_AutomaticN
M
msavvidesfownesNew Participant
asked in Formulas
Creating Style Number

Hello! I am working to create a style number. If a style has two different colors, it will need two records, but the same style number.I want the number to start with a certain number unique to the division, then another number unique to  the brand, then the third would be a number in sequential order. I have fields for Division, Brand, and Autonumber.   I used the below formula.  SWITCH(Division, "DIV1", "2", "DIV2","3", "DIV3", "4", "DIV54,"5",0)&SWITCH(Brand,"Brand1","2","Brand2","3","Brand3","4","Brand","5",0)&Autonumber The problem is I can’t use Autonumber because two rows may be the same style but different color.  As they are the same style, they need to have the same number. What could I use instead of Autonumber? Possibly the answer isn’t a formula, open to any suggestions.Thanks!

966
M
ipshita
ipshitaNew Participant
asked in Formulas
manually modify auto created date field

Hello airtable community,  I am facing a problem while working on my CRM base. I have an auto “created date” field to track when a new deal was created. I then use this field in the interface to create a dashboard so this field is important. The problem I am facin now is that some of my team members have not filled up this table with their deals and are lacking behind. They are now going to update it but when they will create a new deal the “created date” field will be updated as today’s date even though the deal might have actually happened months ago. In this way my dashboard will not reflect my actual dealflow. How can I have a field that will basically fill up itself as a general rule but which I could still modify if i need to? I hope this is clear. Thanks,

323
TheTimeSavingCo
nroshak
nroshakInspiring
asked in Formulas
Airtable equivalent of Excel IFS()

I have a nested IF formula that is working well, but is excruciatingly hard to maintain because it’s so many levels deep. It would be easier to maintain if I could use IFS(), but that doesn’t seem to exist in Airtable. Is there anything else similar I could use? The logic is (in pseudocode) : IF regex_match(MYFIELD, expr1) then regex_extract(MYFIELD,pattern1)ELSIF regex_match(MYFIELD, expr2) then regex_extract(MYFIELD,pattern2)ELSIF …. etc for far too many more patterns …ELSE blank()ENDIFI don’t think I can use SWITCH or CASE (if these even exist in Airtable) because each if statement clause is different. Essentially, I have a string that may contain one and only one of a dozen substrings, and I need to detect and extract these substrings whenever they show up. Fun times.

322
nroshak
S
StaceyHMHNew Participant
asked in Formulas
Duration Formula Help

I have a working formula in my table to calculate the job duration from "job start" to "job end". I am trying to use the same formula to calculate a job response time from "Job Creation date" to "Job start". The formula is as follows:CONCATENATE(FLOOR(DATETIME_DIFF({Job Start}, {Job Creation Date}, 'minutes') / 60), ":",RIGHT("00" & MOD(DATETIME_DIFF({Job Start}, {Job Creation Date}, 'minutes'), 60), 2))However, this doesn't seem to be working as I have a job created date of 20/06/2025 13:00 and a job start of 20/06/2025 13:08 and the result it is giving me is 0:52 - I cannot seem to get this formula to give me the result of 0:08 which is what the actual result should be.My job duration formula works exactly the same way and gives me the correct answer. Job Duration formula is:CONCATENATE(FLOOR(DATETIME_DIFF({Job End}, {Job Start}, 'minutes') / 60), ":",RIGHT("00" & MOD(DATETIME_DIFF({Job End}, {Job Start}, 'minutes'), 60), 2))Job Start = 20/06/2025 13:08 Job end = 20/06/2025 1

302
S
A
ahenryNew Participant
asked in Formulas
Issues with formula - Attempting an on hold client button

Trying to create a condition via formula which will mean that a client won't be able to be quoted via airtable extension with the following:Formula:IF(Type="Client", IF({On Hold}="On Hold">"Good", "✅ Good to Quote","❌On Hold - Do not Quote"))We are hoping to then enable the On hold note to propagate through to an extension we use to send quotes onto clients There is a field with single text which we use to fill out the client and then a options single select for on hold/good

1049
L
darien12
darien12New Participant
asked in Formulas
Feature Request: Output Markdown from Formula

Can we please get a native way to output valid Markdown (that renders as rich text) from a Formula field?  This simple improvement would cut down on so many hacks.

483
Mike_AutomaticN
S
Scribbles_New Participant
posted in Formulas
Find the median average from a rollup field

Hi everyone,I have a column with a series of numbers, and I can easily get the median from this summary above it, but for the life of me I can't figure out a formula to get the same results in a separate table column.Ideally I want this data in a table so I can use it for other calculations. I've been searching for hours but can't find anything. The closest I can get to this is using the rollup field to roll up all the numbers and then use the AVERAGE formula, but I need to get the median average. Would anybody know a formula I can use to get the median? I've tried other formulas from other places on the forums, but they don't seem to work for me.  

1754
P
A
AndrewMNew Participant
asked in Formulas
Event Planning: Creating a template of tasks with automated due dates based on an event date.

Hi! My team has used Asana to create templates for different categories of events that auto-populate tasks with due dates (e.g. 45 days before event date). What is the best method to creating similar templates in Airtable? Below is a screenshot from the Asana template.  I’m looking to get each Due Date cell to auto to x days before the event date, excluding weekends. I tried using DATEADD, but this does not work on individual cells. Thanks!

383
TheTimeSavingCo
M
mbwalkNew Participant
asked in Formulas
Prefilled Form with Multiple Link Fields

I am comfortable creating a prefilled form using two tables. I am having issues getting a prefilled form to work across three tables. The top field in the form is a Link field between the primary and secondary tables using the recordID. Simple enough so far. But I have a third table with records that link back to the primary table. I want users to be able to select records (from this third table) in a second Link field at the bottom of the form. My attempt at doing this is creating Link fields from this third table to both the primary and secondary tables, hoping the record ID would pull through to populate the selection in the prefilled form. My thinking is that the record ID for the Third--->Primary and Third--->Secondary links are the same record ID. This has not been successful. Below is an example of the formula I am trying. CONCATENATE("https://airtable.com/abc/xyz/form?prefill_PrimaryField=",RECORD_ID(),"&prefill_SecondaryTableLinkToThirdTable=",ENCODE_URL_COMPONENT(RE

795
TheTimeSavingCo
L
Lowell_RingelNew Participant
asked in Formulas
Formula to create time fields from existing text field

 Originally when we created our system, we used text fields to record the Call Time and Gig Time Span. Since we have the time zones already saved for each record, I am wondering if there is an easy way to do the following: Switch the Call Time Field from a Text Field to a Time field (without having to type the time zone every time) Switch the Gig time Span Field into 2 different Time fields for both previous records and current records, a Start Time and End Time. Similarly, I do not want to type the time zone every time. Moving forward, I would like to only have to type the start start and end time for an event in separate fields (not in the text box).If this is possible, these would save a ton of time and allow us to do better, more efficient work. Thank you for your help!

896
Mike_AutomaticN
C
CottageKeeperInspiring
asked in Formulas
Extract Year from a Date Field

Hello, I have a base that has the date and time in one field, I have created another field and I would like to have that field enter the YYYY from the date time field using a formula. Would anyone know what the formula would be? Thanks in Advance!

2075
TheTimeSavingCo
A
Abigail0102Known Participant
asked in Formulas
How to create a formula that takes a date from a previous record

Hello, I work with ships and would like to create a grams/day formula which takes the date from our last interaction (order) with that ship to measure from. I cannot work out how to have it search for the previous record to take that as the date to give the days passed value for the equation.Anyone have any tips?

526
TheTimeSavingCo
A
anneboccInspiring
asked in Formulas
combining two IF(FIND( formulas into one

Hi Airtable folks, I’ve got this really fun bookkeeping base I’ve created, which I use in conjunction with Fintable.io (a way to push bank transactions into Airtable – highly recommend!). Since Fintable downloads the transaction names from my bank statements in all sorts of different ways depending on how the banks format them, I wanted to create a formula to extract the Vendor name from when I receive or send money through Zelle. The thing is that this can show up in a variety of ways:ZELLE FROM FIRSTNAME LASTNAME ON DATEZELLE TO FIRSTNAME LASTNAME ON DATEZelle Payment to FirstName LastNameZelle Payment from FirstName LastNameZelle Payment to FirstNameZelle Payment from FirstNameAnd so on…  What I’m getting stuck on is how to combine two formulas I ended up creating: one that returns both the first name and last name of the person (but when there is no last name, returns some of the random extra characters after the first name), and one that returns only the first name, whether there

888
TheTimeSavingCo
IngSiang_Tan
IngSiang_TanNew Participant
asked in Formulas
Formula to combine fields and other text into a rich-text formatting

Hello all, I am trying to automate/ organise/ consolidate the way i run my Shopify product information.I use Airpower (a 3rd party API integrator) to sync Airtable to and from my Shopify.I am trying to create a product description field (rich text/ HTML) then sync it to my Shopify product information field.Currently:My product information field is in Rich Text and will sync across to airtable. What I want:I wanted to split the field further - e.g. About The Item, In the Box, Compatibility So that I can make the formatting more consistent ​​​​The Result Format I want: <h3>About The Item</h3>This phone case is… The colour is… It is made of durable plastic etc...<h3>Compatibility</h3>iPhone 16 Pro iPhone 16<h3>In The Box</h3>Phone Charger etc.Is there a way to achieve that using formula (e.g. concatenate)? Thank you!

341
TheTimeSavingCo
M
MSCM14New Participant
asked in Formulas
Date Range Verifier is Excluding Data Within its Supposed Range

Hi! good day!I am currently using the formula below to determine if a record/records is within last weeks Sunday to Saturday. Sunday being start of the week and Saturday being end of the week. AND(       IS_AFTER(           {Time in},           DATEADD(               DATEADD(                   TODAY(),                   -WEEKDAY(TODAY(), "Sunday") - 7,                   "days"               ),               0,               "days"           )       ),       IS_BEFORE(           {Time in},           DATEADD(               DATEADD(                   TODAY(),                   -WEEKDAY(TODAY(), "Sunday") + 0,                   "days"               ),               0,               "days"           )       )   ) If today is the 20th of June then my Current formula will tag my June 8 , 8AM record as false. If I adjust the time to 8:30AM, formula will tag it as true, would like the formula to include the entire day of sunday up to the entire day of saturday. Appreciate the help!

414
TheTimeSavingCo
S
SamuelNew Participant
asked in Formulas
Formula

I need to have the formula to calculate the total revenue and profit from my sales records table, which has been linked to my product inventory table. The aim of this set up is to update records automatically with every sale.

182
TheTimeSavingCo

Badge winners

Show all badges
Terms & Conditions