Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,946 Topics
  • 18,252 Replies
5946 Topics
V
Vijay_chenKnown Participant
asked in Formulas
How do I change the #ERROR to null?

How do I change the #ERROR to null? How do I change the #ERROR and NaN to null?

92
V
G
Gideon_KlionskyNew Participant
posted in Formulas
Join date-elements from two columns

(topic withdrawn by author, will be automatically deleted in 1 hour unless flagged)

00
G
W
Wael_DacoNew Participant
posted in Formulas
forumle for our school teachers calendar

Hello, I need to create a formula to include permanent (fixed) class hours in our airtable school calendar :blush: . Classes are repeated on the same days at the same time , depending on education levels throughout the school year (from September 1, 2020 to July 5, 2021, eliminating the following school holidays: • From Saturday October 17 to Monday November 2 • From Saturday 19 December to Monday 4 January • From Saturday February 20 to Monday March 8 • From Saturday April 24 to Monday May 10 • From Thursday May 13 to Monday May 17

00
W
M
Mike_AshtonNew Participant
posted in Formulas
Autonumber formula in primary field

Hi, I would like my primary field to be autonumbered, starting with “PW10001”, then +1 value to each new record (so PW0002, PW0003, etc). I think I need a hidden Autonumber column to give me each new sequential number, but I’m stuck on how to string the formula together in the primary field to bolt the “PW10000” to that. Can anyone help?

6584
M
P
Paul_TownsendNew Participant
asked in Formulas
Trying to create a formula to check if one field (text) contains another field (number) within a record

Hi Guys, Having trouble finding out how to write a formula to check if one field contains another field’s data. The specific use case is data being pulled from an ecommerce site. Each order comes from a page dedicated to a suburb/postcode. Each order populates a record with this suburb/postcode in one field as well as the delivery address in another field. I am trying to create a formula that will check if the delivery address contains the same postcode in the other field (and if so label it “true”). Any help would be much appreciated! Thanks in advance!

1012
P
S
Sali_DimondKnown Participant
asked in Formulas
DATETIME_FORMAT is generating wrong Date

First - I am pretty new to writing formulas. I know enough to get myself into trouble. :slightly_smiling_face: Here’s my problem … I have a table which is a listing of tasks that are assigned and have associated dated. The fields in this table includes: Date field that includes the time field (Start Time) I created an automation that would send an email when a particular person is added to a record (assigned a task). This email includes in the body of the email, the name of the task and the date and time that this task is scheduled to begin by inserting data from the Start Time field. The resulting email looked ok except that the data from the Start Time field was ugly (for lack of a proper technical term). So I learned how to convert the data from the Start Time field into text by creating two new Formula fields using the DATETIME_FORMAT function: Field name: Date STRING // Formula: DATETIME_FORMAT({Start Time},‘MM/DD/YYYY’) Field Name: Time STRING // Formula: DATETIME_FORMAT(SE

742
S
K
KC7New Participant
posted in Formulas
Nested IF Statement HELP

I’m a new Airtable user and still learning the ropes. I have a nested if statement that keeps getting kicked back as invalid and I cannot figure out what the problem is: Here’s what I need the formula to populate: IF{Appra Waived?} = ‘YES - Waived’, “N/A”, IF{Appra Waived?} = ‘NO - Needed’, then: a)IF{Appra Accepted By Due Date} = ‘NO’, then insert a date 4 days after {Appra Order Complete} not including weekends and holidays. b)IF{Appra Accepted By Due Date} = ‘YES’, then insert a date 2 days after {Appra Order Complete} not including weekends and holidays. Here’s the formula I wrote that does not work: IF({Appra Waived} = ‘YES - Waived’, “N/A”,IF({Appra Waived} = ‘NO - Needed’,IF({Appra Accepted By Due Date} = ‘NO’, DATETIME_FORMAT(WORKDAY({APPRA ORDER COMPLETE},4,2020-10-12,2020-11-11,2020-11-26,2020-12-25,2021-01-01,2021-01-18,2021-05-31,2021-07-05,2021-09-06,2021-11-11,2021-11-25,2021-12-24,2021-12-31’),IF({Appra Accepted By Due Date} = ‘YES’, DATETIME_FORMAT(WORKDAY({APPRA ORDER

20
K
D
Dan_HaugenNew Participant
posted in Formulas
Find Character String and return next x amount of characters

I am trying to get values that follow a defined string in incoming emails. Example: I have zapier setup to bring emails of interest in from UPS and I want to put the "tracking number in a new cell from that email. FIND("Tracking No.: ",{UPS Email})+11 what i get in the cell is 11 not the next 11 characters that I want.

192
D
S
Sonia_GrootNew Participant
posted in Formulas
Pull out domain without .com from a URL

Hi guys, I have a question about a formula regarding a URL. I want to have the domain without all www. etc before the domain and without the .com or whatever. LEFT( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE({Website URL}, “http://”,""), “https://”,""), “www.”, “”), IF( FIND("/", SUBSTITUTE( SUBSTITUTE( SUBSTITUTE({Website URL}, “http://”,""), “https://”,""), “www.”, “”))=0, LEN( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE({Website URL}, “http://”,""), “https://”,""), “www.”, “”)), FIND("/", SUBSTITUTE( SUBSTITUTE( SUBSTITUTE({Website URL}, “http://”,""), “https://”,""), “www.”, “”))-1)) My script is working on the left side and it gives me the URL: airtable.com, which is my goal. Now I want to delete the .com, what do I need to add? Thanks in advance!

30
S
I
ibrahim_shaukatNew Participant
posted in Formulas
Two fields matches two fields of other table give that result

If i have 2 tables one has rent of different location and in other if some one booked from to some location when from and to matches in both tables, in booking table give rent amount from rent table. Is it possible or any way to do this action and get result.

410
Kamille_Parks11
F
Felix_RahlmeyerNew Participant
posted in Formulas
Counting multiple Mails

Hello everyone, for an automation with Zapier I would like to count in a formula field in Airtable how often the respective mail address already appears in this grid view. For example, if xxx@gmail.com is already two times in this view, then it should display 2 next to the mail address in the formula field. The email field is the primary field. Unfortunately I didn’t find a solution here in the community, I hope you can help me! Thanks a lot!

40
F
I
itoldusoandsoInspiring
posted in Formulas
Is there a way to search multiple keywords in the linked field? (Only single keyword or phrase allowed now)

My linked table has 17,000 records. I want to look up the record I need but I only can look up one of these options: Keyword A Phrase (“keyword A keyword B keyword C”) What I want is I want to be able to look up “Keyword A Keyword Z” where keywords are not part of a phrase. In the picture below - I want to be able to find the entry for Baby Carriages & Buggies by searching keywords Home Buggies. Right now I have to either search Buggies or search Hearth or search the expression “Home & Hearth” or search expression “Baby Carriages & Buggies”. Are there some operators or wildcards I can use?

70
I
I
International_HNew Participant
posted in Formulas
What is wrong with my formula guys?

if ( {revenus ou dépense}=‘revenu’, LEFT({catégorie}, 5), {revenus ou dépense} ) i want my key field to display different things according to what is written in another field thanks girls and guys in advance

21
J
A
Alba_Machado2Known Participant
asked in Formulas
Using last modified time to populate Amazon's Update Delete field

Creating a table that will be split into different views for different online marketplaces, as they each require different fields. The Status field at Abebooks corresponds to the Update Delete field at Amazon, though, so I’m making the Update Delete a formula field that converts the language like this: ‘’'SWITCH( {STATUS}, “For Sale”, " ", “On Hold”, “Delete”, “Sold”, “Delete” ) My question is how can I add a statement that will look at the last modified time field I’ve named Updated and return the value “Update” only if that Updated field has today’s date? So that I can get only the updated today records into their own view to be downloaded as a CSV files I can use for the online marketplaces? I feel like this is a thorny one, but this community has surprised me on multiple occasions so maybe I’m just too much of a newb to recognize real Airtable thorniness.

140
A
M
Martin_HammarluNew Participant
posted in Formulas
Formula for fetching user who completed a task

Hi, I have managed to fetch latest modified date to AirTable, to see when a task was completed - now i want to add a field displaying WHO finished the task - is that possible?

32
M
J
joelgabauerNew Participant
asked in Formulas
Unique number using year and week numbers

Hi All, I’d like to create a unique number for a record based on the year and week. So for example, I have a date field which I use as YEAR(Date)&" - "&WEEKNUM(Date). So if the date was today this would give me an output of 2020 - 43. Now, I want to add a counting number for each record with a date in that week in order of when the record was created. So the first record created with a date in that week would be “2020 - 43 - 1” and the second record created would be “2020 - 43 - 2” and so on and so forth. But when the week changes to the next week I want the counting number to start again, so it would then go “2020 - 44 - 1”, “2020 - 44 - 2” and so on. I just don’t know how to do this and would appreciate any help thanks.

302
J
S
Student_ServiceParticipating Frequently
posted in Formulas
Conditional calculation formulas, or formulas using formulas?

Thanks for being here! I’m trying to calculate weekly sales as a percentage of monthly sales goals. I’ve made a conditional formula to populate sales goals according to month, and my formula is accepted but only returns errors. I’m also wondering if I can bypass the separate sales goals field and embed the formula into a calculation, i.e., can I say that IF {Month} is MM/YYY, {Current Month Sales}/‘XXXXXX’ If not, I’d like to just be able to calculate simple division, but that doesn’t seem to be working either. Heather

11
Kamille_Parks11
J
Jennifer_MeloNew Participant
asked in Formulas
Formula: time worked

Hi, I need some help, I am counting the total hours worked of my employees, But I need a formula that won’t count the lunch time or that some how if table (lunch in) or (lunch out) is empty it won’t say NaN. some people take lunches, some don’t this is what I have it like this. I have it to count hours worked before lunch and after lunch and add that all up. Screen Shot 2020-10-19 at 1.25.01 PM|700x99

133
J
R
Rachael_CastelaKnown Participant
posted in Formulas
Show Upcoming Step based on chronological order

Hey there, I need to show the most current step in progress in my “Is Current Step?” formula as “1”. I’m running into an issue with my current formula because some of the Upcoming Due Date Lookup dates are the same. (Example below for the Content Quality Objective, where it looks like there are two current steps). Ideally the formula would say “Is Current Step?” = 1 when Actual Completion Date (per Objective) is empty. It’ll then look at the next upcoming date based on the Revised Completion Date. If those dates are the same, it’ll look at the Step # and it should be the next one, chronologically. FYI I need it to be “1” because my Objectives tab will show the current step based on the condition (below). Thank you in advance! Show Current Step, based on complete, date Formulas Hey team! I want to be able to show my “Current Step in Progress”, based on the information in my “Steps” table. How it should look: If there is an actual completion date, the f

50
R
V
VignissonParticipating Frequently
posted in Formulas
Converting hex values to the nearest color name

does anyone know a way to convert hexadecimal values to actual color names (or the nearest neighbour). Examples: #ffffff = white #eeeeee = light grey #45c1f6 = light blue

7211
J
D
Daniel_GavelNew Participant
posted in Formulas
Nested Formula the same on two logins - but not working for one

Hi, I am looking for help as i am struggling to work out why a nested formula will built isn’t working for one login but not the other within our tea, despite it being the same and all the tags, fields etc being mirrored. We are trying to categorise our database into - Gold, Silver, Bronze and have used the formula below which works for my analyst, but not for me. Can anyone help me understand why it isn’t working on mine IF(Category = “Gold”, IF(DATETIME_DIFF(TODAY(),{Last Catchup},‘days’)>60,“Overdue”), IF(Category = “Silver”, IF(DATETIME_DIFF(TODAY(),{Last Catchup},‘days’)>90,“Overdue”), IF(Category = “Platinum”, IF(DATETIME_DIFF(TODAY(),{Last Catchup},‘days’)>30,“Overdue”)))) Note: We copied and past the forumla across, so not sure if that has had an impact

35
kuovonne
L
Lisa_BacherNew Participant
posted in Formulas
Just learning conditional logic and need help with a formula

I would like a formula for this: When the status of a record is Project Complete for 3 workdays, change the status to Archive.

75
L
A
Adrien_BideauNew Participant
posted in Formulas
Formule rang dans airtable

Bonjour, je cherche à indiquer un rang (de 1 à 10) dans un champs selon une notation d’un autre champs. Un peu type formule rang sur excel. Merci d’avance.

10
A
L
Liam_WoolleyNew Participant
posted in Formulas
If a field is blank use another field

Very new to this so apologise if its a simple question. I have a field that is “cost”, a field that is “cost over-ride” and then a feild that is “actual cost”. What i want to do is to use the cost over-ride field but if blank use “cost”. Any tips will be much appreciated.

61
N
G
Geraldine_MaddrNew Participant
posted in Formulas
Commission of 40%

Can you pls tell me how to write a formular when I have a retail price in a colum and I need to have a colum that is 40% of the retail price.

00
G

Badge winners

Show all badges
Terms & Conditions