Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,945 Topics
  • 18,247 Replies
5945 Topics
T
tim_giblinNew Participant
posted in Formulas
Find records with date field equal to now not working

So I am attempting to use the api (via the npm client) to find records that are now or in the past, then perform an action, and mark them as complete (mark completed column as true). Select is as follows: view: 'Scheduled', filterByFormula: `DATETIME_DIFF({Game Start}, NOW(), 'minutes') <= 0`, The formula works fine for records in the past, but not for when the times are equal. For instance, if it is 8:25am and the ‘Game Start’ is also 8:25am, it is not coming back as a result, but when it is ran at 8:30am, it does return as a result. Not sure if it’s a timezone difference, formula issues, or what. Any help would greatly be appreciated. Thanks!

90
T
T
tamoor_ishaqNew Participant
posted in Formulas
Show expiry day

i create one table for employee .in that i create one filed passport expiry.i want that one more field should shoe that if expiry date is gone.filed show some message

72
T
J
JWHWNew Participant
asked in Formulas
IF Column One is prefixed A, then X

Trying to achieve an outcome where: IF Column One is prefixed 381 then Apple If column one is prefixed 382 then orange etc Column 1 381-9287 382-2818 383-2812 Column 2 formula Thanks!

155
Kamille_Parks11
H
Harrison_JonesNew Participant
posted in Formulas
New Emails In A List Highlighted

Hello Everybody, Firstly thank you so much for any help in advance. I am trying to mimic a formula that we have in G Sheets where we highlight any new instances of an email address (customer) in a list (but only the first & not any repeating instances) so we can see if the customer is a new customer or if the customer is a repeat customer. I have hit a wall with trying to figure it out myself. With my current formula, a “new” customer record will switch to an “existing” customer row when the customer buys from us again (as it is no longer unique & has more than one instance in the column) My current AT formula (totally incorrect I know) IF(ARRAYUNIQUE({Renter Email (ROOT - LINK)}), “NEW”, “Existing”) My GSheets formula that I am trying to replicate in AT. =if(isna(match(E3787,$E$1:$E3786,0)),“New”,“Existing”) Thank you so much for any help again. Cheers Harry

93
H
jowan_qupty
jowan_quptyKnown Participant
posted in Formulas
Count Unique check-in dates

Hi all, I’m using Airtable as a reservations automation platform for my short-term rentals. In 1 table (Bookings) i have all the bookings with their relevant information (check-in, check-out, # of guests, …etc). This table is linked to a second table called “Guests”, which summarises everything for this guest, including the booking this guest has made, unique phone numbers, unique emails, how much they’ve spent so far…etc In the “Guests” table, i am trying to create a field which counts the number of unique reservations (based on check-in date), this guest has made. I currently have the following set up in the “Guests” table: Right now, the formula in “Returning guest” is showing “Returning” if the “# of reservations” field is larger than 1. This is not completely accurate because a guest can have multiple reservations on the same day but for multiple properties, which does not make him a Returning guest. Ideally i would like to have the formula compare the number of dates in “All che

51
Justin_Barrett
L
Leon_WetterichNew Participant
posted in Formulas
Generate Text Based on Rules and Parts of Post Code

Hey Everyone, I’m pretty new with Airtable and really enjoyed the freatures that it gives the user. I’m creating a Data-Sheet that basically manages Leads and also outputs them to Asana, Now my question was if there is a possibilty to generate a Text (Name of an Employee to Handle the Case) in a seperate cell based on a Number in a cell (Number of Teams - Prioritized) and a Postcode in another cell (The Data in the Sheet Currently has 5-6 Characters, all numbers) The thing is we seperate Tasks based on the first 2 characters of the postcode to our Employees. Do you see any Possibility to get this to work? Thank you in Advance :grinning_face_with_big_eyes:

93
Justin_Barrett
P
Patrick813Known Participant
posted in Formulas
Single select choice creates date in a second column

Hi, How do I create a column or what is the formula that will automatically add the date based on a single selection in a previous column? For the date, I just need Month / Day / Year, not time. Example: Column A single select choices are: New / Pending / Closed. Column B is the date when I select Column A- NEW, Column C is the date when I change Column A from NEW to PENDING, Column D is the date when I changed Column A from NEW or PENDING to CLOSED. (I do want to be able to see all three dates which is why I’m using Columns B, C and D vs. have the date change in Column B.) Thanks, Pat Barron

84
P
A
Alex_StefanacciNew Participant
posted in Formulas
Advanced Lookup w/ Multi-Record Evaluation

I’m hoping someone can help here… I have a base with two tables (Initiatives and Tasks). Each Initiative is linked to one or more Tasks and each Task has a State field (single-select [Backlog | In Progress | Complete]). What I would like to do is create a separate State field on the Initiative record that is driven by the State of the related Tasks. Something like this… If all Task State values are “Complete” then make the Initiative State = “Complete” If one or more of the Task State values is not "Complete then make the Initiative State = “Not Complete” Any suggestions would be greatly appreciated

44
A
C
Carl_DuffautNew Participant
posted in Formulas
Product Inventory, Packing List with Group of element

Hey there, Trying to setting up an Airtable Base to manage my product Inventory. I sell and install device to irriguate fields, each device have a default quantity of piece that I use for earch order. To help My employees I’d like to create Packing List stored by date and client with Airtable. I managed to do so but only by referencing each piece at a time (one device got many different piece). To prevent errors i’d like to create a categorie where I can set all piece and default quantity for one Device Then on my Packing List simply add One device to pack and all the different piece will be added to the packing list with their default quantity at once. I didnt see anything to help me with customed Group where I can add Piece and Quantity to display every with one click. Thanks for helping,

121
C
S
Shama_ArigeNew Participant
posted in Formulas
Convert date to a number

I have a date and a turnaround time. Need to calculate start date = date - turnaround time. The problem is date is date format and turnaround time is a number like 28. Is there any way to convert a date to a number?

144
Justin_Barrett
A
Adam_KramerKnown Participant
asked in Formulas
Turn text into web-safe strings

Hey guys, I am wondering how I would go about importing text from another field like “Diamond Plus” and translating it into “diamond-plus”? Any way to do this without just building a really long IF statement? Thanks so much, Adam

82
A
E
Expert_Credit_SKnown Participant
posted in Formulas
Correct Nested IF showing blank instead of result

I’m building something for my client wherein they choose a single-select and it will show results for 6 total fields based off that selection: i.e. If x = selection1, then 1 = A, 2 = B, 3 = C, 4 = D, 5 = E, 6 = F If x = selection 2, then 1 = B, 2 = C, 3 = D, 4 = E, 5 = F, 6 = G If x = selection 3, then 1 = C, 2 = D, 3 = E, 4 = F, 5 = G, 6 = H I’ve included screenshots and my formulas below and I cannot figure out WHY “Round 3” shows blank when I’m like 100% positive that the formula is correct. Please help! Formula 1 for “Round 1”: IF(T({Choose Item Type & Round}) = “charge-offDOLA”, “remove this account due to the fact that there are different dates last active reported. How can there be different dates when this is what stipulates when the account naturally falls off? You can’t report different months and different years!”, IF(T({Choose Item Type & Round}) = “charge-offDOLP”, “date last paid shows mutliple dates and this makes it look like I paid when I didn’t and didn’t wh

014
E
A
Alix_BoyleNew Participant
posted in Formulas
How can I use a number in a Single Select to Calcuate

I would like to utilize a formula to calculate pricing that is contained within Single Select text. One of my fields includes a Single Select drop down like this: Heading: {Initial Program Cost} 325 for Massage (first 3 hours) The next field includes a Single Select drop down like this: Heading: {Additional Hourly Cost} 125 for Massage (each additional hour) The next field includes a number: Heading: {Additional Hours} 3 **I am looking for a formula that would calculate the total cost: {Initial Program Cost} + {Additional Hourly Cost}*{Additional Hours} 325+375 I would appreciate any help!!!

82
A
M
markpNew Participant
posted in Formulas
Multiple currencies in one column

Hello – I’m creating a database of projects for a company with offices in multiple companies. Ideally I’d love to be able to input a budget value into a column and have it auto assign a currency based on the office location. Is there an example or a best practice for how to achieve this?

221
Justin_Barrett
M
Matthew_PupaNew Participant
posted in Formulas
Replace letters with blank values

Going to make another request on here for anyone who has done this… I’m trying to look through characters in the records in one of my columns (it’s a combination of letters and numbers), and I’m trying to get rid of the letters so only numbers remain. For example, “I have 3 dogs” would get changed to “3”. Tried doing something like the below, but no luck… SUBSTITUTE(twilio_text, OR(“a”,“e”,“i”,“o”,“u”),"") I tried the same thing with AND() and no luck either. I know if I can do this, I’ll probably need to combine the result with TRIM() to get only the number value. Has anyone done anything like this before? To be transparent, I got this recommendation for a different column I was trying to create… IF( ** FIND(‘black’, Text) > 0, ** ** ‘black’,** ** IF(** ** FIND(‘white’, Text) > 0, ** ** ‘white’,** ** IF(** ** FIND(‘brown’, Text) > 0, ** ** ‘brown’** ** )** ** )** ) I get using the IF(FIND()) combination technically could work, but it would be pretty

171
M
E
Erika_MNew Participant
posted in Formulas
Help with IF Formulas and Calculations

Hi there, I’m not too familiar with formulas in general and haven’t succeeded after reading many posts. I need to have a pre-filled selling price column under certain circumstances. I’m not sure if it’s possible to have many ranges as I’m requesting. The multiplying doesn’t need to happen in the same column, I just need to get the “x multiply by” number in a column first. Here are the different circumstances in a table (with examples in the last 2 columns), and written out how I think they should be for a formula. If PC is between these two numbers, then multiply by X. IF(pc<=2,“3.5”, IF(pc>=2.01,“3”, IF(pc<=5,“3” IF(pc>=5.01,“2.5”, IF(pc<=7,“2.5” IF(pc>=7.01,“2”, IF(pc<=10,“2” IF(pc>=10.01,“1.75”, IF(pc<=40,“1.75” Any help is much appreciated! Erika

62
Justin_Barrett
M
Matthew_PupaNew Participant
posted in Formulas
Trying to break out individual words from a long text field

Hello Airtable community. I’m playing around with the formula field reference, and I’m not finding exactly what I’m looking for. I have a long text field, where I’m collecting messages. I’m basically trying to extract key words out of it and save those words to corresponding columns. For example, for the message “I have a young brown puppy who is a male”, I want to break that out into columns so dog_color would get a value of ‘brown’, dog_gender would get a value of ‘male’, etc. I’m sure there’s a solution using some combination of formulas like FIND, SEARCH, TRIM, etc., but I figured I’d send a message to the community while I try figuring it out myself. Thanks!

114
M
A
Allison_PridgenNew Participant
posted in Formulas
How can I make column 2 dependent on the answer in column 1?

I have one column (Draws) that is single select, numbers 1 through 100. The driver would pick the number 55, for example. In the next column will be Returns. I want to make the it so the choice could NOT be over 55 (of course) for the amount of returns available. So basically, the answer in the Returns column cannot exceed the answer in the Draws column. UPDATE: I have created a separate field with an IF formula that will output the word ERROR if the number exceeds the previous column’s number. This works well. However, it seems to be seeing the Single Select numbers incorrectly. For example, if I put in 10 in the first column and 9 in the second, it is seeing 9 as greater than the 1 in the number 10. HELP!

42
A
T
the_rynoNew Participant
posted in Formulas
Items on concurrent date

Firstly I love airtable and it has provided a ton of solutions to our company. I am having trouble solving this. I would like to know, how many projects were running concurrently on the same date. So for example: August 1, we had item A and item B going, on august 3 we started item C, and august 4 we started items D, E, and F. On Aug 5, we finished item A and Item C. I would like to see the data like this. Each one of these items will get a start date and an end date. Aug 1: Item A and Item B (2) Aug 2: Item A and Item B (2) Aug 3: Item A, Item B, and Item C (3) Aug 4: Item A, Item B, Item C, Item D, Item E, and Item F (6) Aug 5: Item B, Item D, Item E and Item F (4) I have been using WORKDAY_DIFF currently to count how many days these items ran for. The end goal is I would like to divide an overhead cost to into each items depending on how many items are currently running so using the same example Aug 1: 2 1000/2 = 500 Aug 2: 2 1000/2 = 500 Aug 3: 3 1000/3 = 333 Aug 4: 7 1

10
T
H
Harrison_JonesNew Participant
posted in Formulas
NESTED IF AND OR Formula help

Hey,I am looking for some help with a formula I am working on. I’m trying to achieve the below statement. IF {role} = “HOT” OR “BUS” AND {payment method}= “Checkout”, then ({Total Payment Received}*0.0275+0.75) ELSE 0 THE {role} within my formula is a “linked” field so I am unsure if that is causing an issue? Any help would be really appreciated. Thanks

152
H
C
Curtis_BudkaNew Participant
posted in Formulas
Calculating the difference between time/date values between records

I am using this table as a punch clock. Each record is a time stamp for moving to a different repair order or stopping work entirely. So Each record needs to be able to identify how long it was until another record was created (only within a specified view). I currently have integromat free version hooked up for another table, so if I can avoid 3rd party integration for this that would be great. Also noted: The primary field obviously uses the created time field to time stamp, but I also have a date/time field worked in with an IF statement that can be used to effectively “edit” the created time. Thank you.

124
C
M
Michael_NovotnyParticipating Frequently
posted in Formulas
Lookup or Rollup with formula

I want to perform a vlookup into a separate table. Similar to in excel, you can vlookup from another tab. However, it seems the only way to do that is by using the lookup field. That is pretty straightforward, however the field with the linked records must be a text field. I’d like to have that be a formula. Here is an example: Create formula that combines two columns. This creates a unique value I want to use that unique value (that uses the formula) as the value to lookup into another table to pull in a value from that table. Is this possible? I just want to execute what a simple vlookup would do in excel, but by using a formula instead of single text to be the value that lookups into the second table.

174
M
M
M_kInspiring
posted in Formulas
Can Someone Help?...How To Create Flood Expression in Integromat

Hi I am trying to change the decimal point in some numbers, to be used in Integromat, using ceil or floor options. I tried using the decimal option in GS, but it didn’t work. Apparently, I need to extract the numerical value, by using split + get functions, then use the math functions floor or ceil. I wondered if someone can help. I managed to create an expression, with some help, apparently I have to use “String” after ”Split” were “1.value” is, but I was a bit stumped, do I place a value there or use a tag? If it’s a value and I want to change the decimal position from 24.01 to 24, how would I write it? If it’s a tag, we’re would I find it? Also, were do I place the “Temperature: Day” tag? Thank you, Mary This is what I would like to convert: This is how far I got: Thank you, Mary

812
M
C
Christian_MooreNew Participant
posted in Formulas
Formatting numbers in a formula

How do I format the results of a numeric formula? For instance for a formula that sums a number of values from fields in my table into a new numeric field, how can I generate a “$” in front of those values automatically, and possibly format the numbers with a comma for a thousands separator?This is my working sum formula: SUM({WC Revenue},{GL Revenue},{GH Revenue},{CP Revenue},{CA Revenue},{EPLI Revenue},{D&O Revenue},{PL Revenue},{KMLI Revenue},{LI Revenue},{HFLH Revenue},{EL Revenue},{GK Revenue},{ProL Revenue},{HO Revenue},{PA Revenue},{Bond Revenue},{Payroll Revenue}) Anything I can add to this to generate the “$” and/or the thousands separator? Thanks!

1062
C
I
isame90New Participant
posted in Formulas
Hotel room capacity/contingent planning

Hey everyone, I’m trying to create a hotel room capacity plan so I can see if we have enough rooms of a category left or if we’re overbooked. I have one table where I create rows for each speaker who needs a hotel room from arrival to departure date. In another table I have my capacity plan that applies for each day. Now I’m trying to make a capacity plan for each day for each room to see if we’re overbooked or have spare rooms. I know how to do it in Excel but am not quite sure how to continue here. I started with another table that has all dates and booked and available rooms but don’t know how to get all booked rooms within in the date ranges. Appreciate your feedback. Isabelle

150
I

Badge winners

Show all badges
Terms & Conditions