Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,225 Replies
5939 Topics
M
MattWeberNew Participant
posted in Formulas
Date formula to show date of tthe first day for current month and year

Hi there - Looking for a way to have a formula show the first of the month for the current month and year. I.e. the formula fields says 2/1/2022 when it is Feb, then when it is March 3/1/2022 and so on…it would update on the first of each month Any help out there?

102
M
C
Cass_SeydaNew Participant
asked in Formulas
What is the formula for BMI (in/lb)?

Hello there! I was just reaching out to see if there was a chance that someone, literally anyone can provide me with a BMI formula, please! I am trying to calculate BMI using height in inches and weight in pounds, and I’ve been searching for weeks for one that works, but it never accepts it. I’ve seen many articles and tips on conditional logic, but I don’t need it to tell me whether or not they’re overweight, I just need the actual BMI number. I’ve exhausted my personal and searchable resources, and since I’m also pretty new to this, when I DO find a formula that I think may work, I have trouble converting it from kg and meters or centimeters… I would be VERY grateful for a formula that I can copy and paste into the formula spot. My height and weight are both formatted to integers in inches and pounds and the result I would need for BMI would be for example Height: 64 Weight: 160 BMI: 25.7 Despite my many attempts I have become very frustrated with this and for some reason I cannot fi

1522
C
D
David_OhanessiaParticipating Frequently
asked in Formulas
Blank Linked record into conditional formula

Hello to the community, And happy 2022 for everyone who reads this topic :winking_face: Maybe I’m the only one to face this situation but I’ve created a pretty simple formula field which should provide a simple 1/0 boolean answer based on 3 conditions. One of those conditions is if one linked field to another table (Order_ID) is still blank/empty. IF(AND({Payment Status}!=“paid”,Order_ID=BLANK(),REGEX_MATCH(Email, “(\W|^)[\w.\-]{0,25}@[A-Za-z0-9.-]+\.[A-Za-z]{2,}(\W|$)”)=1),1,0) Guess what ? It doesn’t work. I’ve also tested the NOT(Order_ID) based on what I’ve read and it doesn’t work either BUT it works on a separate formula cell (see below the second line) : Other 2 conditions work perfectly. I assume this is due to the structure of a linked record by itself but I’m not sure. Thank you for your help. Best, Y

311
W
T
Thomas_D_HoeNew Participant
asked in Formulas
Formula - remove text

Hi, I want to remove the text “project to archive” in my primary field. Can I do this with a formula? This is a primary field, I don’t know if that’s an issue?

701
W
T
Tonya_NeuweilerNew Participant
posted in Formulas
Populating a field from one of two date fields using DATETIME_DIFF?

Hi, I have two date fields - one for a birthdate I actually know, and the other for an estimated birthdate (horse rescue here and sometime we know, sometimes we don’t…). I want to populate a third field to show “current age”. One or the other of the birthdate fields will be empty. I have the following but it doesn’t work. Can someone tell me how to fix it? Thanks for your help! IF({Foal Date}, DATETIME_DIFF({Today},{Foal Date},‘years’), ELSE(DATETIME_DIFF( {Today},{Estimated Foal Date}, ‘years’))

114
T
Y
Yoav_ShotlandNew Participant
posted in Formulas
Suspected Bug in filterByFormula feature (AirTable API) (thoroughly tested)

Hi there, I have written and tested a formula (it works) - AND(IS_AFTER({Invoice Date}, “12/31/2020”), IS_BEFORE({Invoice Date}, “12/31/2021”)) Where invoice date is a valid date field. This works perfectly well on the table interface, returning 0s and 1s as needed. When I use the exact same formula on filterByFormula parameter in the API, it returns (NodeJS) - AirtableError { error: ‘INVALID_FILTER_BY_FORMULA’, message: ‘The formula for filtering records is invalid: Invalid formula. Please check your formula text.’, statusCode: 422 } I couldn’t find any documentation saying that AND could not be used. *note - I already succeeded using only one date formula - IS AFTER

20
Y
A
Aysia_SaylorKnown Participant
asked in Formulas
Configuring DateAdd formula with multiple conditions

Hello, I have 1 single select field called “Status”. This Status field has the following options: Awaiting Information (Outreach 1) Awaiting Information (Outreach 2) Awaiting Information (Outreach 3) On Hold Each of these options has its own date field connected to an automation that date-stamps when the option is selected in Status. Outreach 1 Date Outreach 2 Date Outreach 3 Date On Hold Date I need 1 formula that returns a date with the following considered: If Status = Awaiting Information (Outreach 1), add “2” business days to Outreach 1 Date. If Status = Awaiting Information (Outreach 2), add “2” business days to Outreach 2 Date. If Status = Awaiting Information (Outreach 3), add “3” business days to Outreach 3 Date. If Status = On Hold, add “30” business days to On Hold Date. This is what I’ve tried so far with the first 3 dates conditions: IF(Status=“Awaiting Response (Outreach 1)”,DATEADD({Outreach 1 Date},2,‘days’)) & IF(Status=“Awaiting Response (Outreach 2)”,DATEAD

214
A
Z
Zaib_KhanNew Participant
posted in Formulas
Add custom text after formula

I’m using the following formula to show date and time for “Facebook event_time field”… DATETIME_FORMAT({Date},‘YYYY-MM-DDTH:mm:ss’) I want to add a letter “Z” to the end of the field… so at the moment its returing the event_time as: 2022-01-10T13:02:00 I want it to look like: 2022-01-10T13:02:00Z

591
ScottWorld
D
Dan_BurkeNew Participant
posted in Formulas
Maintenance Schedule for equipment

Hello Everyone, I’m looking to get some fresh thoughts on this project I’m working on. I have a working base, but I feel like it will be too bulking as the tables grow. The goal is to create a notification message or message string in a record when a piece of equipment reaches a certain mileage or hours. Think of a car manual. In the maintenance section is a schedule of suggested repairs to be made once the car gets to a certain mileage. ie…at 30,000 - replace cabin air filter, replaced engine air filter. at 60,000mi replace the fuel filter, check belts,… I have a base with 3 tables - Equipment, PM Record, Repair History I’ve linked all 3 together using Equipment as the main working table. PM Records is used to capture mileage and hours at the time of maintenance. I then use two rollup fields on the Equipment table to display the Max() Miles and Hours record. I also have columns on the Equipment table per repair that display a repair message based on the rollup field. Then use anoth

432
D
A
Andrew_AversaParticipating Frequently
posted in Formulas
Iterating over records in a formula - is this possible?

I’m using AirTable to describe all products my company sells, or has ever sold, in our shop. Each record is a product. The tricky part is that some products are technically product bundles. To help describe this, I have a field called “Bundled Products” which can link to other records, for example: Product X (bundle) Bundled Products: Product Y, Product Z Here’s where it gets tricky. The way our store reports revenue, every product (including bundles) has sales recorded separately. But for analysis purposes, I want to be able to consolidate the revenue from Product Y with the relative percent of revenue from Product X, the bundle that includes it. I’m struggling to figure out how to create a formula or rollup for this. What I’ve done so far is define an additional field called “Part of Bundles”, so Product Y now has a reference to Product X, that contains it. If I were writing a program for this, here are the logical steps I would need to calculate this “Master Revenue” figure for Prod

13316
kuovonne
Y
Yaakov_ZehnwirtNew Participant
posted in Formulas
Changing WeekNum value to 52

I wanted the Weeknum of an end date field. The week number of those records that have 12/31/2021 is 1 instead of the last number of the year. How can I turn that into 52. I was trying to do a formula like this IF({End Date} = ‘12/31/2021’, “52” , WEEKNUM({End Date})) but it wasn’t working. I am new to formulas so if you can give me guidance I would appreciate it.

133
Databaser
J
Joel_Andrews1Inspiring
asked in Formulas
Creating a %Complete Field based on selections in several single-select fields

Hi all! I’m trying to create a %Complete column in a project table that includes several single-select fields used to track 1. whether or not a particular team has tasks associated with the project, and 2. what that team’s progress is on those tasks if so. I’m thinking what I need to do is create a formula column for each one of these, assigning a number to the values selected, a column that totals these up, and a column that calculates the % complete by dividing the total complete by the total that needs to be complete. Maybe use the SWITCH formula to assign numeric values? I just don’t know how to set up conditional formulas that complex at this point. Can anyone help me? Thanks very much!

3811
J
L
Lucas_TjahjadiParticipating Frequently
asked in Formulas
Formula Primary Field ( Recorded Time)

Hai everyone, how to write formula on primary key column to capture Recorded time. I know there is column with type created time, but for primary key I’m not using so I think will be better if use for recorded time but with formula Thank you

83
Databaser
M
Maya_Hajj_HasanNew Participant
posted in Formulas
Convert date to week number starting with a specific date

I have a field called “Campaign Date” in my database where I start my experiment (November 1st). I would like to create a field where my first week of experiments is labeled Week 1, and so on. Can someone point me in the right direction?

111
G
S
Sam_BradleyNew Participant
posted in Formulas
Formula to reveal duplicate rows

Hey, I hope you’re well and might be able to help. I’ve created a table which records safety test results for some equipment. Each time a test is completed, it’s created as a new record and there’s a lookup field to link each test back to the piece of equipment that has been tested. It’s all working nicely. However, as we start to add more and more tests, there’s times where the same piece of equipment is tested more than once. This is causing some problems with some of the reports we generate, as the reports should only show the most recent test result. Does anyone know of formula I can create that can check if there are ANY other records in the table with identical values in the lookup field and IF SO, display a “TRUE” or “FALSE” result according to whether the particular record is the latest one? Thanks in advance, Sam

6507
O
K
Keith_LandaleNew Participant
posted in Formulas
Formula Error issue

Hi Sorry if this seems dumb, but im a complete beginner. I have a task id field with a simple concatenate formula that puts together three fields data in the same table. And shows {Customer Name} - {Type of Task} - {Date of Task} and it adds the date time format D/M/YYYY. Its shows eg: John Smith - Photoshoot - 21/7/2022 This works brilliantly, but has a very minor annoyance of returning ERROR when adding a record because the three fields are blank. Once the fields are filled in it works a treat. Is there any way the formula can allow for the blank fields without showing error? Thank you in advance

54
K
A
Anne_GonzalezKnown Participant
asked in Formulas
Using a formula calculated date and DATETIME_FORMAT

I’m trying to use DATETIME_FORMAT to format a date calculated by a formula to the way I want it to be displayed. I have to use DATETIME_FORMAT rather than the settings button because I’m inserting it into an automated email, which defaults to YYYY-MM-DD format instead of the settings. It seems like this should be possible but I keep getting an error. Here’s my current formula: DATETIME_FORMAT((DATEADD(TODAY(),14, “days”), ‘DD-MM-YYYY’) The center formula DATEADD(TODAY(),14, “days”) works properly on its own, so I know it’s something about adding the formatting piece. Is it possible to do this in one formula?

42
A
M
Mike_BuckParticipating Frequently
posted in Formulas
Using IF for conditional averages?

Trying to figure out a formula to run a conditional average: Within the table, each record is a single asset. One field tells me what asset it is (unique ID), one field shows me where its physically located, another shows me the type of asset (may assets, although all unique are the same TYPE, etc.). I’ve got another field that contains a formula that shows me the average use each asset receive within a given time sample - that field/formula work perfectly. What I’m trying to do is average that use for the assets, but ONLY where the location and type are the same. Example - Location #3 has 4 assets. 3 are Type 1, the last is Type 2. The use formula is returning values of .25, .50 and .75 for the three Type 1 assets. I’d like this formula to average those 3 values together to return .5, BUT ONLY for Location #3, and only for Type 1 assets. The Type 2 asset should remain blank because its the only Type 2 asset at that location - there is nothing to average it with, etc.

153
Databaser
A
Andrey_LNew Participant
asked in Formulas
Non-typical dynamic count

Hi everyone, Please advise how to create a formula for a field (let’s call it “CityCount”), which would automatically display the number of records in this table which have the same value in the field “City” as a current record? Here is an example: there are 3 records in the table with City=London, thus each of the record should have CityCount=3. If I add one more record with City=London, then each of such records would get updated with field CityCount=4. Any help or ideas appreciated!

174
A
P
Paul_JangNew Participant
posted in Formulas
Different result between airtable and excel in Multiplying decimal Formula

Hi, I have some problem in calculating CO2 calculation. Calculation result values as below. Excel : round( 8.110000 * 0.000039 * 56.100000 * 1.000, 6) = 17.925857 Airtable : round( 8.110000 * 0.000039 * 56.100000 * 1.000, 6) = 17.743869 Each data in Airtable and excel is in the field (decimal 1.000000) It would be appreciated to help me. I think excel value would be right calculation result. Regards JP

42
H
B
BazoekiNew Participant
posted in Formulas
Want to add another seperator to a row of unique values

From a look up field a have a row of values which are in my case dog breed subscriptions. I cropt this row by using the Array unique functions so it only shows unique vallues. Some rows have just one unique value of some have for example 3. I want to seperate always the last value in a row with an & or and instead of a comma. For example Bernese Mountain Dog, Rhodesian Ridgeback, Labradoodle → Bernese Mountain Dog, Rhodesian Ridgeback & Labradoodle Rhodesian Ridgeback, Labradoodle → Rhodesian Ridgeback & Labradoodle Does anyone now a solution for this problem?

22
Alexey_Gusev
1
111136New Participant
posted in Formulas
Can I use a prefill function for attachment file?

Someone already asked above question, though. I couldn’t find the answer. I know how to use “prefill function” for text but I want to use it for attachments file. is it possible?

10
1
K
Kosta_KondratenInspiring
posted in Formulas
How to get related text field from Rollup field

Hi, I have a rollup field for ‘Last Contact Date’ to fetch the most recent Communiques (which is my unique name for Contact Points) date and display it in my Contacts table. So far so good. Now - here is the challenge. For the sake of convenience I just thought it’d be cool to display the related text for that particular field in another field - like “Last Contact Notes” - this would give me a quick refresher on that contact if I was going to call them again. Just an idea. And wondering if it’s possible? I made a video to clarify: And… something that will make me absolutely jump for the moon is if it were possible to have a field that would pull the notes from my Communiques table and arrange them by date as so: 14/7/21 - CALL Spoke to Bob - he said he’ll get Amy to call me back. 18/6/21 - CALL Spoke to Bob - he said Michael will have to approve. 1/6/21 - CALL Spoke to Bob - he said Cartright will have to review. Does that make sense? Because right no

215
ScottWorld
J
Justin_KaplanNew Participant
posted in Formulas
Don’t have dropdown to create formula

Either I am crazy or I cannot find a drop-down to create a formula in any of my columns. This is my first time using the app I created a table from scratch.

15
M
Pete_Lindley
Pete_LindleyParticipating Frequently
posted in Formulas
Subtracting multiple fields from a total

Here’s one for you. We have a purchase order for a job, with a total amount in one table. We have multiple work shifts each be a varied amount depending on hours and rates of pay in another table. I’d like to be able to summarise all shifts and subtract them from the total purchase order to check we are under budget. Is this possible?

61
kuovonne

Badge winners

Show all badges
Terms & Conditions