Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,938 Topics
  • 18,225 Replies
5938 Topics
V
Vic11New Participant
asked in Formulas
Nested IF with “!=blank()”

Hey guys, I’m having some troubles with the blank formula. I build this simple task-manager. The idea is that i can choose a task-startdate and choose with the single select the duration. Then I have a formula which counts based on the single select the right end-date. When there is no date, I get an error, which I would like to remove. This is the original formula for the end-date: IF(Block = "2 Std", DATEADD(Datum, 2, 'hour'), IF(Block = "4 Std", DATEADD(Datum, 4, 'hour'),IF(Block = "1 Std", DATEADD(Datum, 1, 'hour'),IF(Block = "8 Std", DATEADD(Datum, 8, 'hour'),IF(Block = "0,5 Std", DATEADD(Datum, 0.5, 'hour')))))) This is the one I tried with blank, but I’m doing something wrong here… IF(Block = "2 Std", DATEADD(Datum, 2, 'hour'), IF(Block = "4 Std", DATEADD(Datum, 4, 'hour'),IF(Block = "1 Std", DATEADD(Datum, 1, 'hour'),IF(Block = "8 Std", DATEADD(Datum, 8, 'hour'),IF(Block = "0,5 Std", DATEADD(Datum, 0.5, 'hour', IF(Datum = BLANK()))))))) Thank you very much! Best, Viktoriya

53
V
Z
ZAINA_InternatiNew Participant
posted in Formulas
Holiday Planner

Hi there, I would like to create a table to count and assign 25 holidays per year to employees. I’ve managed to create a formula that sums holidays excluding national holidays and weekends but I would like to create a field called “Days Left” with a formula that calculates the holidays left out of a 25 days yearly allowance. That would mean I would have to calculate something like this: 25 (days) - (minus) Sum of Holidays taken up-to-date from the period 1st Jan 2020 to 31 Dec 2020 Any help would be much appreciated

788
O
B
Blake_EskinNew Participant
asked in Formulas
Counting unique dates of submission // pull data from Pivot Table app back into table

I teach a class where students have to share information in a daily form. They get credit for the number of days they submit a form, not for the total number of forms submitted. The form feeds a Daily Submissions table, which is linked to a Students table. Because I didn’t see something equivalent to Sheets/Excel COUNTUNIQUE in Airtable, I created a Pivot Table app in the Daily Submissions to calculate Days Shared. I’m trying to figure out whether I can pull the number of Days Shared from the Pivot Table app back into a field corresponding to that student in a table of students, so I can share a view of table that includes Days Shared. If that’s not possible, is there a way to count the unique days in a table without a pivot table? There is a Lookup field in the Students table that collects all the days, including duplicates, but I don’t know how to dedupe that. Or do I need to do create an interface that displays the Pivot Table app data? Thanks for your help!

102
B
V
Vijay_chenKnown Participant
asked in Formulas
How can the final result of the formula be converted to a numeric value with 2 decimal places

Hi Can airtable fulfill my requirement? Please see the picture for specific examples. I need to reserve two decimal digits in the column of “Original Price” in the picture

84
V
M
Monica_CzwarnoNew Participant
asked in Formulas
Creating a link to an external website address in forms

Hello, community! I am using the free subscription for this table. In the form that I am creating from the table, I would like to add a section that will allow those filling out the form to be directed to an external link. (Ex: “Thank you for completing this form, if you would like to join our mailing list, please click (XX).” Our mailing list is held via another program.) I thought that the solution was to add a button and then have the button redirect to the external link. But that column does not show up in the form view (because I imagine it’s meant to support internal processes). Is there a way to do this? Thank you.

832
M
B
Blinkt_UitNew Participant
asked in Formulas
Workday_diff with linked holidays gives error

Hi there, I have a question for our PTO system. I like to calculate the workdays between the PTO startdate and enddate minus the holidays + non working days (for partime employees). I want to do this in a linked field because of large number of dates. But I constantly get an error when implementing. I’ve made a table for all days in the coming year I’ve made a lookup field referencing to this table. Then a field with a formula field WORKDAY_DIFF({startPTO}, {endPTO}, {Days in year}) I’ve also tried this advice from Justin_Barret in this topic. But unfortunately it keeps showing an error. WORKDAY_DIFF({Start Date Field}, {End Date Field}, {Lookup Field} & "") Does anybody have an idea? Or can point me in the right direction :grinning_face_with_big_eyes: Many Thanks

212
B
A
Aditya_IshanNew Participant
posted in Formulas
Retrieve email id

We have list of POCs for leads in Airtable. We want to create a formula and column that when ever a POC is assigned to the lead or for all the leads in the airtable having POC the column should generate the email respectively mapped to that POC.

30
A
B
Brandon_HatcherNew Participant
posted in Formulas
Can I Extract the first sentence of a description and copy it in a different column?

HI, I am using airtable to plan out an event. I will be creating an app from the airtable data. On the schedule for the app, I want to have a short description that is one sentence long, then when you click on the details of a schedule item I want to see the full description. Ideally I would like to write the full description out and have the first sentence of the full description be copied into another cell to serve as the short description. For example: This would be the long description: Come to the Ice Cream Social to get to know your fellow attendees. The Ice Cream Social will be held in the blue building next to Hospitality. It is sponsored by ACME Products inc. This would be the short description (first sentence of above): Come to the Ice Cream Social to get to know your fellow attendees. I guess what I am looking for is a formula that would take everything up to the first period on the long description then copy that sentence into another cell.

374
kuovonne
D
Darian_GrantNew Participant
posted in Formulas
Combining Lookup values from multiple columns into one column

I’ve attached an image of a dummy setup. What I’m trying to do is combine the names from “Lookup Participants - Project 1” with the names from “Lookup Participants - Project 2” into one column (the “Combined Participants” column). My goal is to create a somewhat scalable research repository where each new research project will be able to feed into this “Master Takeaways” sheet, and participants that get tagged with the same “Main Takeaway” (column 1) will appear in the “combined participants” column and then the count in “total count of participants” will reflect that. (the count part, I can figure out, it’s mainly that first issue of getting the participants all into one column) This second image is what it ideally should look like: Please help! There has to be a way to do this with formulas. Even if it won’t achieve the exact ideal solution, something close enough would be a help at this point.

21
D
K
Karim_BadraParticipating Frequently
posted in Formulas
Request Codes (AutoNumber Reset)

Hi guys, Anybody know a way to create proper request codes / references for each row. For example, my current system is to concantenate a Letter (‘S’ for example) with the product name, then place the year of the request, then add the autonumber ID for reference. So it comes out looking like: S-MOT-22-1. However, I would like the last number in this field to reset and start counting again at every new year, and maybe even with every product type. Ex: S-MOT-22-1 for the first MOT request of Jan 1 2022 & S-MOT-23-1 for the first MOT request of Jan 1 2023

41
V
M
Mark_SanbornNew Participant
posted in Formulas
Check to see if a Date is missing and make code more efficient

My question has 2 parts to it. I need to check for a missing date in 8 columns (4 sets of Start and End Date) that has a start date or end date, but not the other. I use these columns to calculate vacation days used. So if someone inputs a start date but forgets to put a end date in, I want to show “Start or End Date Missing” instead of the calculation. This works so far for one start/end column pair. IF(LEN({Vacation Start 1})>0, IF(LEN({Vacation End 1})=0),“Missing a Start or End Date”, BLANK()) But I am looking to group all 8 columns to one answer like this IF(LEN({Vacation Start 1})>0, IF(LEN({Vacation End 1})=0,“Missing a Start or End Date”, BLANK()), IF(LEN({Vacation Start 2})>0, IF(LEN({Vacation End 2})=0,“Missing a Start or End Date”, BLANK()), IF(LEN({Vacation Start 3})>0, IF(LEN({Vacation End 3})=0,“Missing a Start or End Date”, BLANK()), IF(LEN({Vacation Start 4})>0, IF(LEN({Vacation End 4})=0,“Missing a Start or End Date”, BLANK()),BLANK())))) But this just l

13
M
A
Archie_TimosencKnown Participant
asked in Formulas
Setting the score based on a multi-select column

Hey all! I’ve asked the community in my recent post whether or not it is possible to “score” records based on the value in a multi-select field, and I also needed my formula to calculate the total for all values when the multi-select has 2 or more values. Here’s what @Justin_Barrett came up with and helped me a lot: IF(FIND("Global", Locale), 1) + IF(FIND("US", Locale), 0.9) + IF(FIND("UK", Locale), 0.9) + IF(FIND("DE", Locale), 0.7) + IF(FIND("CA", Locale), 0.8) + IF(FIND("FR", Locale), 0.7) + IF(FIND("ES", Locale), 0.6) + IF(FIND("MX", Locale), 0.6) + IF(FIND("BR", Locale), 0.6) + IF(FIND("AU", Locale), 0.6) + IF(FIND("IT", Locale), 0.6) The way that I expect my records to be scored has slightly changed as so I need to refine the formula - I don’t need it to put the values together, instead, I want it to return the biggest value from the list alone. Here’s an example: The score per locale is as before: US - 0.9 UK - 0.9 DE - 0.7 Global - 1 CA - 0.8 FR - 0.7 ES - 0.6 MX - 0.6 BR - 0.

120
A
A
Archie_TimosencKnown Participant
asked in Formulas
Formula that counts in values from a multi-select column/cell

Hey people! I’m trying to “score” some of the records in my table, one of the columns represents a localization information about each record, e.g.: US UK DE Global CA FR ES MX BR AU IT Each localization adds up points to the final score: US - 0.9 UK - 0.9 DE - 0.7 Global - 1 CA - 0.8 FR - 0.7 ES - 0.6 MX - 0.6 BR - 0.6 AU - 0.6 IT - 0.6 The column which represents locales is actually a multi-select column, as my “records” can be present in multiple locations. My formula can already read the value in the Locale cell and add up the corresponding value to the final score, but how can I program it to see, that there are several values, and calculate them all together? Current formula: IF(Locale=“US”,0.9,(IF(Locale=“UK”,0.9,(IF(Locale=“DE”,0.7,…etc. Thank you!

160
A
H
Hendrik_YangInspiring
asked in Formulas
Formula for Text Formatting

My Current format is like this I want the format to be Shrink-General | 5 x RM-SHRINK 1 x RM-SLIP 1 x RM-BAREFOOT What is the formula for that?

172
H
K
kdbNew Participant
posted in Formulas
Plz tell me how to use it as Excel's days() function?

I hope use this function in airtable… please help me out

21
Rupert_Hoffsch1
B
Bartek11New Participant
asked in Formulas
The formula IF there is something it is supposed to show "✔️" or "❌"

I would like the formula to be able to identify the client by field {Rodzaj usługi}. If the client would be (in the {Rodzaj usługi}) “Fotografia ślubna” then I would like {Status} to show “ :heavy_check_mark: ” only if the {Odbitki}, {Album}, {Galeria internetowa}, {Selekcja zdjęć}, {Zwiastun}, {Obróbka zdjęć} fields will be filled with the word “Oddane”. Otherwise, the {Status} field should show “ :x: ”. When the client would be (in the {Rodzaj usługi}) “Film ślubny” then I would like {Status} to show “ :heavy_check_mark: ” only if the {Film wrzucony na dysk}, {Teledysk ślubny}, fields will be filled with the word “Oddane”. Otherwise, the {Status} field should show “ :x: ”.

22
B
K
Kevin_FrileyNew Participant
posted in Formulas
Adding a string to any formula returns an error

For whatever reason, I cannot add text to a formula, even using the same formula I have running in other bases. Basically, I want the formula to return to values with a hyphen or colon between the two. I have two variables, Color (single-line text) and Brand (single select), and I want it to return like “Color - Brand” or Color: Brand.” I have tried the following formulas: Color & “ - “ & Brand Color & ‘ - ‘ & Brand Concatenate(Color, " - " , Brand) Concatenate(Color, ’ - ’ , Brand" Each of these returns an error for an invalid formula. And, as I said, I have another base with an effectively identical formula and it works with no problems. Further, I have tried moving the hyphen elsewhere and I’ve tried using different characters, none of which works. For example, if I just try to add a random word to the end of the formula, like the one below, I get the same error: Color & Brand & “randomword” Concatenate(Color, Brand, “randomword”) These, too, return the s

21
ScottWorld
J
Jason_FKnown Participant
posted in Formulas
Count # of Times Current Date Appears in Lookup

Hey Everyone: I have a table that links items to form submissions. I already have a link to see each item and all the form submissions that include that item. I also have a lookup that looks up the dates of the form submissions in the link to forms. I want to take that lookup and run a formula to count all the form submissions from today’s date. Any ideas?

32
J
D
Dale_SwackhammeNew Participant
posted in Formulas
Formula for tracking payments

I was able to do a simple subtracting formula to show amount owed and amount paid and balance remaining. Is there a way to expand on that formula to include when interest is charged so that it can be added to the balance? I want to have a more accurate picture of my financial spending. Any help or advice would be appreciated. Thank you.

50
D
D
Devan_KreitzerInspiring
asked in Formulas
Removing "Other" free form survey responses from multi-select field

Hello! I have a Jotform survey automatically going into Airtable. Some of the questions have “Check all that apply” with an “Other” response that prompts a free form answer. These free form responses are being added as multi-select option in Airtable (rightfully so). I’m trying to do an Airtable Interface on the survey responses but, obviously, those free form responses are being considered. In a column, I want the options and replacing the free form response with “Other”. Then in a separate column track the free form response (just so that data isn’t lost). Any ideas? This is essentially what I’m trying to do: The “Name” and “Q1 Answers” field is the only thing imported with survey responses.

154
D
S
Susan_CollinsNew Participant
posted in Formulas
Formula Help. Adding Rows / Filled Amounts

Hello, I am new to this software but have written a pretty functional Airtable to keep track of the UK 50p coins I am collecting. I’m able to add costs and dates and photos and descriptions and have even been able to add columns up at the bottom using the Filled option. I have three columns (16, 33, 7) which I would now like to add together to give a total of coins. I would then like to multiply that number by .50 to give me the bare minimum value of my collection. I think it ought to be simple but I am held back by lack of knowledge of basic formulas and would really appreciate any help the community could give me.

206
S
J
Jeffrey_PorterNew Participant
posted in Formulas
Counting how many times a value appears in a table

Background: I have a table that includes a list of activities each customer has done. So there unique value is a random “Event ID”, another column is “Customer ID” and a third column is called “Activity Type.” I need to find a way to count how many times a Customer has done a certain Activity Type and then be able to use that formula to create a notification when a customer has their FIRST activity (first appears in the table.) The table is populated via API, so it’s not as simple as just knowing I did it. I found a Pivot table that can just give me the counts of Activity Type by Customer, but you can’t act on any of the information in that table. So essentially I need a way to find any time a new record is created that contains a unique Customer ID that does not exist yet in the table. TL,DR: I need a way to answer the question “Tell me when a new client has their first activity”.

1563
Ben_Young1
C
CCtheOppNew Participant
asked in Formulas
Updating status with formula field

Hello, I am horrible at formulas and it’s really becoming a frustration as I continue to learn Airtable. I am wanting to create a status in the formula field to then automate an email trigger. The formula needs to update to either 60-days or 30-days based on the contract end date. I already have fields for the start date and end date so the formula can work with either. Seems simple enough but I am a struggle bus. What formula do I need to enter? If we are 60-days out from the contract ending, update the status to 60-days, and if we a 30-days from the contract ending, update the status to 30-days. Start with IF(AND(, correct? IF(AND({Contract End Date} …blank stare…, “60-days”), repeat blank stare, “30-days”) Help :weary:

253
Kamille_Parks11
A
Archie_TimosencKnown Participant
asked in Formulas
Scoring based on >, <

Hello Community! I need to calculate the score of a record based on that record’s numeric value. My records’ numeric values can be anywhere between 1 and 100, and I need to set a score between 1 and 10 to each value based on how high that value is, like so: 1-3 => 1 3-5 => 2 5-10 => 3 10-20 =>4… you get the idea My problem is with setting up a formula - whet I try to set it like this: IF(Value>1,1,(IF(Value>3,2,… etc. the result only reads the first parameter (value>1 => returns 1) and it doesn’t compute when the value of the record is more than 3, it still returns me 1. How can that be fixed?

122
A
M
MattWeberNew Participant
posted in Formulas
Formula with today() and a set time

Hey there…look for assistance with formula that uses the TODAY() function with a set time. I.e. I want the field to have the current date but with a set time so that it would show for example the current date that updates each day but always has 12:00 PM. Thanks in advance for help

343
ScottWorld

Badge winners

Show all badges
Terms & Conditions