Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,225 Replies
5939 Topics
J
Jim_HitchParticipating Frequently
posted in Formulas
Can I utilise the automated functions at the bottom of every base in formulae?

I’ve been using Airtable for some years now and would consider myself pretty knowledgeable. However, I’ve never been able to figure out how to, or even whether, I can use the automated functions at the bottom any Base Window which gives a variety of information about the column above it, for eg. Sum, Average, Median, Min, Max etc. Is this possible, if so, how? Thanks.

91
ScottWorld
J
Jonathan11Inspiring
asked in Formulas
Is there a "null date"? So I don't get errors or "Your result type is not a number or a date."

For example, I have a date field, some of which are filled in, some of which are not. I want to make a second date field that is a time offset from the first. I want this formula field to stay in date format so it can be exported to a calendar, etc: However, when the first field is blank, the second field shows an error instead: How can I change my formula to output “absence of date” when the first field is blank? Using IF(BLANK( to return "" or 0 breaks the date formatting:

1243
J
B
Bobbo_BuckleyNew Participant
posted in Formulas
Your result type is not a number or a date

This is probably a stupid question, but why is my result not a number in the following If statement? IF({Course Order}>0,{Cost-1st Attendee}+{Cost-2nd Attendee}+{Cost-Additional Attendees}, “”) It actually works, but I can’t format it, and the Summary fields don’t work. When I click on the Formatting tab, I get the following message. “Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.” How can I get the results to be formatted as currency (I assume that fix will make the Summary fields work)?

52
J
W
Weng-ICParticipating Frequently
posted in Formulas
Auto Tabulate Between Tables

Airtable Auto Fill - Airtable Explore the "Auto Fill" base on Airtable. Is there a way for me to auto tabulate from “Inventory” description to the respective field under “description” column for all the rows? Title: enter titleYear: enter yearType: enter typeDimension: enter dimension

82
W
G
Georgie_CummingParticipating Frequently
posted in Formulas
Reverse engineer a rollup

I am working in a base built by someone else. There is a Leads and Clients table, a Products table and a Sales Log table. Product prices are listed in the Products table and I imagine this should feed into the Sales Log which is then linked to Leads and Clients. There is a rollup field for lifetime value in the Leads and Clients table but it’s not working. Unfortunately, I can’t see what it is supposedly rolling up so I can try and fix it. I know it’s pulling info from the Sales Log but how can I see exactly which field it’s rolling up so I can fix it?

96
G
R
Raza_AliNew Participant
posted in Formulas
Convert Created date into Text

I want to convert my Created Date field into Text like “Latest to Oldest” and “Oldest to Latest”. I have checked all previous articles and had tried DATETIME_FORMAT and SET_TIMEZONE but it doesn’t return text it returns time.

132
R
A
automadienInspiring
asked in Formulas
If time is between two times, return a value

Hi there, I want to return a value of “Morning news” when the time from a live date falls between 5AM and 9AM. This needs to be irrespective of date, so I made a field that parses the time from the live date field. Currently I’ve got: IF(AND(VALUE(DATETIME_FORMAT({ADMIN - Time},‘Hmmss’)) <90100, VALUE(DATETIME_FORMAT({ADMIN - Time},‘Hmmss’)) >45900), “Morning news”, “Other”) But that’s returning an error. I tried it without the Value parameters too.

310
A
J
John_White1New Participant
posted in Formulas
To Do List base, want to calculate time left in non-completed tasks

I have a To-Do List sorted by department with the estimated time to complete each task in a daily view. I want to surface the time left to complete tasks marked in progress, not started, pending. Is there an easy trick I’m missing?

122
J
N
Nicole_FlewelleParticipating Frequently
asked in Formulas
Nested IF Referencing Multiple Fields with String Return

I’m trying to figure out this formula, which I based on a different formula that I got to work successfully, but am hitting a wall. Basically, I want the formula to reference 4 date fields (Edit Start, Format Start, Production Date/Time, and Ship Date). If there are values in any of those fields, I want it to return, as a string, the date, and either "Edit Start: ", "Format Start: ", "Production Start: ", and/or "Ship Date: " ahead of each date, as applicable. So, a full string with all dates filled in should look like: Edit Start: XX/XX/XX; Format Start: XX/XX/XX; Production Start: XX/XX/XX; Ship Date: XX/XX/XX However, one or more of these date fields may be empty at any given time, so I want the text string to adjust accordingly and not show the lead-in if the corresponding date field is empty. Here’s my attempt, which won’t compute. “Edit Start: ” & DATETIME_FORMAT({Edit Start}, ‘L’ ) & IF(AND({Edit Start}, OR({Format Start}, {Production Date/Time}, {Ship Date})), "; ") &am

143
Kamille_Parks11
A
Aimeric_BEZARDNew Participant
posted in Formulas
Get Email from longtext with specific text before

Hi, I have a question about formulas, I have a longtext field extracted from an Email Body and I would like to find inside this field if there is any mail marked like this : “From : email@domain.com” and extract just the mail to put it in. For information, I don’t know the line in question, it may have text after the email and the domains are not the same. I have tried Regex but I have not succeed. Thanks

74
A
S
Sally_SchuelerNew Participant
asked in Formulas
Using button to email multiple email addresses and a cc field

Hi all, I’m trying to email multiple people in a record with a button field. I’ve almost got it but when I can manage to get both email addresses to show up, they are joined together and the second email address is a bit off using a % instead of the @ (although I think if I could separate them it would resolve). This is what I’m using: “mailto:”& ENCODE_URL_COMPONENT(ARRAYJOIN({Seller 1 email})& ENCODE_URL_COMPONENT(ARRAYJOIN({Seller 2 Email}))) & “?subject=” & Property I’m trying to paste an image but I can’t see in this email draft… How do I get the addresses to be separated? I’ve tried all I know how to do, quotes, commas, semi colons…I’m at a loss. The other piece is the cc portion which I’ve only seen gmail examples and they aren’t working in a generic email. Thanks in advance for any help!!

174
S
Drew_Nemer
Drew_NemerKnown Participant
posted in Formulas
Conditionally Linking Records

Hello, I am trying to assign the correct Payment Standard to our unit rent increase tracker. CR Approvals is one table. Payment Standard is my other table I am trying to figure out if there is a way to automatically updating the PS in the CR table with the correct Payment Standard listed on the other table based on the city, effective date and the BR fields. So a 1 BR, effective for 7/1/2022 in Boston would be assigned a PS of $2383.00. I feel like this is an exercise that would work in Excel with a XLookup type of function or something but I am not sure how to make this work on Airtable. Any thoughts?

73
ScottWorld
E
East_Lima_RealtNew Participant
posted in Formulas
Formula limit? airtable shortens my formula?

is there a formula limit in airtable? i wish to compute for the payment rental reminder. could be every 2 months or every 3 months. but upon saving, airtable is shortening it to half of what i type. what’s happening? IF(AND(FIND(“Bank Transfer Regular”, {Payment Term}), Status= “Active”),SWITCH(AND(FIND(“2 months advance”, {Payment Term}),{Contract Start}< DATEADD({Contract Start}, 2, ‘month’)), DATETIME_FORMAT(DATEADD({Contract Start}, 2, ‘month’),‘MM-DD-YYYY’)), SWITCH(AND(FIND(“2 months advance”, {Payment Term}),TODAY()> DATEADD({Contract Start}, 2, ‘month’), (TODAY()< DATEADD({Contract Start}, 4, ‘month’))), DATETIME_FORMAT(DATEADD({Contract Start}, 4, ‘month’),‘MM-DD-YYYY’)),SWITCH(AND(FIND(“2 months advance”, {Payment Term}),TODAY()> DATEADD({Contract Start}, 4, ‘month’), (TODAY()< DATEADD({Contract Start}, 6, ‘month’))), DATETIME_FORMAT(DATEADD({Contract Start}, 6, ‘month’),‘MM-DD-YYYY’)), SWITCH(AND(FIND(“2 months advance”, {Payment Term}),TODAY()> DATEADD({Co

134
Alexey_Gusev
S
Sean_ComerfordParticipating Frequently
posted in Formulas
DATETIME DIFF - Rounding up between months

I’ve seen one or two topics touching on this, hoping to see if there is a workaround here. Using DATETIME DIFF appears to be rounding down the resulting unit by default. For example, calculating the number of months between 1/1/2015 and 12/31/2015 results in “11.0” with no possible means to round up to the more accurate “12” due to the result being rounded down. Is there a possible workaround for this that is capable of producing a value (maybe “11.9”?) that can then be used in conjunction with ROUNDUP to round up to the nearest whole number?

251
Kamille_Parks11
E
Edward_ZevelevParticipating Frequently
posted in Formulas
Default value in number fields

After I set up a default value for regular number field, when new records generated the field still show blank, instead of default value. Am I doing something wrong?

271
ScottWorld
E
Edoardo_AbateNew Participant
posted in Formulas
IF FIND AND OR formula?

Hello! Do you guys know if it’s possible to create a formula that return a rate (1 to 3) IF: in the field {name field}, there are both “option1” AND “option2” = 3 in the field {name field}, there is only “option1” = 2 in the field {name field}, there are no “option1” OR “option2” = 1 I tried to use IF, FIND, AND, OR but I couldn’t do it :frowning: Thanks for support!

865
Alexey_Gusev
M
Mark_WernerNew Participant
asked in Formulas
Convert linked record names into unique comma separated values

I am creating standard project tasks from a template and am stuck with writing a formula that creates a comma-separated text string from the names of linked records but also adds the project name to the front of each record name in order to make it a unique name. For example: my linked template records are Cat Dog Cow and the projects are Project 1, Project 2, etc. I want the resulting field to be Project 1 - Cat, Project 1 - Dog, Project 1 - Cow, and so forth. That way the “Cat” in project 1 is a unique record from the “Cat” in project 2. Any help would be greatly appreciated!

552
M
S
Sadie_GlissonNew Participant
asked in Formulas
Help updating formula to leave "#ERROR!" result blank

Hi all! I have a formula field that calculates a variety of numbers into an average and then translate them into star ratings, here: REPT("⭐", ROUND(AVERAGE(IF({Overall Review NUMBER not accepting 0 rating (from Reviews)}, {Overall Review NUMBER not accepting 0 rating (from Reviews)}, 0)), 0)) This works great, except when there is no star rating submitted, then the “number” is seen as NaN, and the formula spits out “#ERROR!” Can anyone help update this formula to say: if result=“#ERROR!” then leave field blank ? IF it really needs to be done in the number sequence calculation step, I’ll give you that info: My formula for creating the number sequence in “Overall Review NUMBER not accepting 0 rating (from Reviews)” from above is this: AVERAGE({Work/Life Balance}, {Pay & Benefits}, {Culture & Values}) I previously tried to turn NaN into 0, but then it factored in “0” into my average, which I don’t want because it brings the average down inappropriately. That code WAS: ROUND(AVE

133
S
V
Vasilii_LysenkoNew Participant
posted in Formulas
INT(0) doesnt works in IF Statement

IF({ExpireEnd},IF({ExpireEnd} = TODAY(),INT(0),INT(DATETIME_DIFF( {ExpireEnd}, TODAY(), 'days')) ), "") When “ExpireEnd” field condition is equal today formula returns nothing empty, should return 0 INT(“0”) - with quotes same effect nothing. when I put in formula just “0” - without INT() - its ok Also work fine when I use any number other than zero

01
V
D
David_BevanParticipating Frequently
asked in Formulas
Correct formula to calculate a date based on dropdown selected in another field

Hi, struggling with a formula / best approach for calculating dates based on a condition. Fields in scope are as follows: Meeting Type = Single select option Date = Date Meeting to take place Request for information = calculated date based on meeting type selected. So if for meeting type I choose ‘x’ I want request for information to populate a date 8 days prior to the ‘date’ field (which is already populated with a date) If for meeting type I choose ‘y’ I want request for information to populate a date 11 days prior to the ‘date’ field within the request for information field. Previously the calculated dates were identical regardless of ‘meeting type’ and the ‘DATEADD’ formula worked fine. Now I need to make it conditional, I cannot seem to get an if statement formula working correctly. Any help would be greatly appreciated.

216
D
L
Lane_UnitedNew Participant
posted in Formulas
Floating Point Error - Addition/Subtraction

There is a floating point bug that I can only get to rear its head in a particular way. I have distilled down to this simple example. Add three particular numbers (haven’t played around with which numbers cause the problem, but these do), subtract the sum from the same number in another field and the result is displayed as zero, as it should. Put an IF statement in another field comparing the result to zero, and the comparison fails. I was able to get the IF statement to display the floating point error by displaying “ZERO” or the calculated result. See below: I have a larger base where I am adding a number of fields and comparing the result to what it should be and flagging the result if it isn’t the same. This bug is causing a number of false negatives, so a fix would be greatly appreciated! Love Airtable and I use it for more and more projects everyday, so thank you so much. -Dave

8717
H
K
Kosta_KondratenInspiring
posted in Formulas
Setup mobile number for API

I have Australian mobiles that I need to convert to the following format: 61412826666 (note no spaces and also no plus signals) Here are the examples of mobiles I have: 0439 584 111 +61 448 196 088 4197 118 98 Is there a formula that can convert all of these to the 614… format I specified above? Thank you!

234
ScottWorld
E
Edo_VigoNew Participant
posted in Formulas
Calculate working hour putting into consideration break time

Maybe the solution is a script, but I’m really after is the time in hours between two dates, but only the business hours. So Monday - Friday, 8.30am - 12.30pm and 1.30pm - 5.30pm. just to be clear: 23/6/2022 8:30AM - 23/6/2022 5:30PM. => 8 23/6/2022 8:30AM - 24/6/2022 12:30PM => 12 23/6/2022 8:30AM - 24/6/2022 5:30PM => 16 24/6/2022 8:30AM - 27/6/2022 12:30PM => 12 23/6/2022 8:30AM - 23/6/2022 5:30PM. the result of the formula should be 8 in this case start date and end date have 8 working hours the second one has this working hour: 23/6/2022 8:30AM - 23/6/2022 12:30AM 23/6/2022 1:30PM - 23/6/2022 5:30PM 24/6/2022 8:30AM - 24/6/2022 12:30PM in total 12 hours Do you have a solution for this? Is something like Microsoft project

50
E
K
Kosta_KondratenInspiring
posted in Formulas
Capitalise first name in Formua field

I have a ‘Contact’ field with a value as follows MICHAEL Ashworth I have a formula field that I want to say the following: Hi Michael, Are you ready for something exciting… (etc.) How can I get only the first name (remove everything after the space) - and then capitalise only the first letter. Thank you!

1101
Justin_Barrett
C
Cassy_AmeliaKnown Participant
asked in Formulas
IF, ELSE, OR in Formulas

Hi, This is my current formula for an If not blank > do this OR if blank > do this: IF({Discount}, {Brand} & " Discount Code: " & {Discount} &" off -", IF(NOT(Discount), {Brand}))& " "&{Product Title} But I would like to add another option that if {column} contains “FREE TRIAL” or “FREE SAMPLE” to do the same as the first line but without the '“off -”. So far I have this: IF({Discount}="FREE TRIAL", {Brand} & " Discount Code: " & {Discount} &" - " & {Product Title}, {Brand} & " Discount Code: " & {Discount} &" off -" & {Product Title}, IF(NOT({Discount}, {Brand}& " "&{Product Title}))) But it’s glitching and I know the formula isn’t correct but hopefully, someone can please help! :grinning_face_with_big_eyes: Thank you!

3733
Kamille_Parks11

Badge winners

Show all badges
Terms & Conditions