Formulas

I am trying to transfer my google sheets formula to airtable and I’m not sure how to do it. I have a form where people enter data. I want to create a possibility for me to look through that data from a certain date range and give me the sum. Here is the google formula, please let me know if you can help me transfer that to Airtable. Time log is a “sheet” or tab or page (whatever you want to call it) in my document =SUMIFS(‘time log’!E:E,‘time log’!B:B,C4,‘time log’!C:C,">="&A4,‘time log’!C:C,"<="&B4)

Hi… I understand spreadsheets but am new to databases. I have Base A that contains data about PhD students. The first field is Surname/FirstName. The base holds around 160 fields including 3 separate fields for 1) Principal Supervisor; 2) Associate Supervisor #1; 3) Associate Supervisor #2. I have a second Base that contains data about the Supervisors. I would like to create a field in this 2nd base that pulls the Surname/FirstName of every student from Base A where the supervisors name is present in Principal Supervisor; Associate Supervisor #1 or Associate Supervisor #2. Is this possible? In excel I think I would do this with an IF - VLOOKUP formula but I can’t see how to reference the other table.

Hello! I’m trying to write a formula to get a bell emoji if a record has been modified after the date we close our quarterly reporting by. I have the “last modified” field and the “report closure” dates to compare, but I am struggling to write the “IF” formula in the right way. Any help would be massively appreciated! :grinning_face_with_big_eyes: Thank you! Belén

Hi all, I’m trying to build a formula field that only displays the dates of records that have “Event”. My formula is: IF(FIND(“Event”,Name)>=1,DATETIME_PARSE(Date),"") My FIND() condition works, but outputs the ISO date format of {date}. My goal is to output the format exactly as it is in {date}, a date object. Anyone know what I’m missing from my formula? E.g. 2020-02-25T00:00:00.000Z should be 25/2/2020. Thanks all.

Hi there, I’m trying to work with video timecode and the DURATION field type doesn’t cut the mustard, as I need HH:MM:SS:FF (frames) formatting. See another post on this at Video Timecode Field I’ve found a great pair of Excel formulas to convert FRAMES into HH:MM:SS:FF and back, but I would really appreciated help turning this from EXCEL into AIRTABLE syntax please, where cell A1 could be another text field… =VALUE(LEFT(A1,2))6060*30+VALUE(MID(A1,4,2))6030+VALUE(MID(A1,7,2))*30+RIGHT(A1,2) Example: A1: 01:06:46;11 RESULT: 120191 =TEXT(MOD(INT(INT(INT(ABS(A2)/30)/60)/60),60),“00”)&":"&TEXT(MOD(INT(INT(ABS(A2)/30)/60),60),“00”)&":"&TEXT(MOD(INT(ABS(A2)/30),60),“00”)&";"&TEXT(MOD(ABS(A2),30),“00”) Example: A2: 112019 RESULT: 01:02:13;29 Thanks in advance!! (The formula above came a very helpful post at https://github.com/cgranier/Excel-Timecode )

Could someone tell me what I’m doing wrong? For some reason this formula is able to input the money amount for job 1, 2, 3, and 5 but it shows up blank for job 4. What’s going on? IF( {Job Type} = ‘Job 1’, “$ Amount”, IF( {Job Type} = ‘Job 2’, “$$ Amount”, IF( {Job Type} = ‘Job 3’, “$$$ Amount”, IF( {Job Type} = ‘Job 4’, “$$$$ Amount”, IF( {Job Type} = ‘Job 5’, “$$$$$ Amount” )))))

Hi guys, The result of my formula is not recognised as a date… Here’s what the formula’s supposed to do: Firts I set the project deadline date. To execute the project a few actions needs to be done. I made a single selection field to choose how much days or weeks in advance the action needs to be done. For example “1 week” in advance. The formula I created is fairly simple: [Deadline date] - [Select time] = New date This is the code I made: IF({Select time} = “1 week”, DATETIME_FORMAT(DATEADD({Deadline date}, -7, ‘days’), “DD/MM/YYYY”) Can anybody help me so that my result is recognised as a date? I tried leaving out the " " but it gave me an error. Thanks!

Example: I have 3 fields, 1 multi select, and 2 checkboxes Reason - Summary: Multi-Select Reason - Wedding: Checkbox Reason - Vacation: Checkbox What I want to do is be able to update the value of the multi-select field based upon the checkboxes that correspond to an option. For instance: IF( {Reason-Wedding} = 'True"), THEN(select “Wedding” from {Reason} options) If this is easier done upon importing the record using Zapier could you please point me in the right direction.

Hello, I’m also new to Airtable. I’m wrestling with one thing that is very easy in Excel (or Google Sheets), but I haven’t be able to find how it can be implemented in Airtable. I’ve created a Table. I add a record ever day to track the number of occurrences such as: Visitors to an establishment. Date Day Visits % Change 23.3. Mo 27 NA 25.3. Tu 28 = (28/27)-1 (in formula in Excel (C2/C1) - 1 26.3. We 30 = (30/28)-1 (in formula in Excel (C3/C2) - 1 27.3 Th 35 = (35/30)-1 (in formula in Excel (C4/C3) - 1 etc Where the % Change Column (or Field in Airtable speak) is, of course, calculated by a Formula. How can I achieve this with Airtable? Any help would be very much appreciated. Peter

I want to formulate a date if: under one column the answer is 3-day add 3 days to date under one column the answer is 2- day add 2 days to date under one column the answer is 1- day add 1 day to date I think I begin like this, DATEADD({Proposed Date of W/C},3, ‘days’) but how do I formulate the if this then that?

Hello community! I am in need of your help. I am concatenating text in various cells. When all cells in (Fields 1-6 is filled the result is perfect. However when data is missing from some of the Fields it generates a double underscore. How could I avoid this nuisance ? Formula being used: CONCATENATE({Field 1}, “", {Field 2}, "”, {Field 3}, “", {Field 4}, "”,{Field 5}, “_”, {Field 6}, Extension) Thanks you so much and stay safe!

I want to get the following result and I don’t know how to use the if function for that. If (record is blank), CONCATENATE(a&b), CONCATENATE(b&c)) What should I do? Thanks

I have a task I want to do that I think should be pretty simple…but I can’t figure out how to do it in Airtable. I have one cell with a list of numbers, separated by commas (“2, 3, 4, 5, 6”) and I want another cell to give me the average (“4”). In google sheets I was able to do it with [=average(split(A3,", "))] but I can’t seem to find anything similar in airtable. Thanks!

I am trying to write a formula, IF a field is not empty then return today’s current date and time. I have a form and when people upload an attachment I want to time/date it so I know when they submitted.

I have a sales pipeline on Airtable with a single select field for the different stages. e.g. Qualified Lead, Initial specification, Evaluate quote, WON etc. At the moment I have a filter on the table so if the project is WON it is flitered out of the pipeline. However I would like to create a formula whereby IF {STAGE} = “WON” then the row is deleted from the table Is this possible?

I love Airtable! My goal is to use it for genealogy research which can be complicated, so I started with something fairly easy to learn different ways of doing things. My first base is a Pet Meds and Vet visits. Below is what I’ve tried, but I’m stuck on #3. If there’s a better way to do what I’m trying to accomplish, let me know! I’ve used SQL before, but it’s been a long time ago. 1 - I have a Prescription table which lists all the meds each of my pets has gotten (vaccinations and meds they take daily (Active meds)). I have a checkbox column for the Active meds in the Prescription table. #2 - Then I have a Pets table with 5 pets. I linked the prescription table to the Pets table. Now, the Pets table shows all the meds a specific pet has ever been given in the Prescription column. I added a function column #15, which is pulling all the meds, but I want to limit it to Active meds, based on the check box in the Prescription table. #3 - I don’t know the formula to only list the meds

Hi! I am trying to create a directory of sorts in Airtable - we have providers and we capture if the users would recommend them. I then want to be able to easily be able to look at a provider (they are stored on their own table) and say X% of users recommend them. Any help is greatly appreciated!

I need some Airtable Formula help. I am trying to merge the below. I’ve been looking at it so long I can’t work out what is going on any more! This works: IF({Rejected}, IF({Rejection Reason}=“Unresponsive”,“Unresponsive”, IF({Rejected}, IF({Rejection Reason}!=“Unresponsive”,“Rejected”)))) And this does too: IF(Rejected}=BLANK(),IF({Status 1}+{Status 2}=2,“Done”, IF(Rejected}=BLANK(),IF({Status 1}+{Status 2}=1,“One of two”, IF(Rejected}=BLANK(),IF({Status 1}+{Status 2}=0,“Pending” )))))) However, if I put them together, then it only displays the results of the first 2 lines. If I switch it around so the 3 lines goes first, then it only displays the results of the 3 lines. Not sure why but any help gratefully received. :pray:

Hello! I have a Base with several complex formulas that I’m trying to pair down to be a bit more streamlined. If been trying to get the following formula to work for days with no luck. If anyone can help, it would be super appreciated… Here’s the setup: Title Category (Detail) / Single Select Field: Feature, Series (Comedy), Series (Documentary)… etc. Title Category (Main) / Nested IF Formula Field: This formula looks and the Genre Single select field and determines whether the record falls under four more macro-categories: Series, Feature, Sizzle, or Other. Title Release Date / Date Field: Date field that every record has formatted as YYYY-MM-DD Season Number / Single Select Field: Only records for series’ have this selected. If the record is for a movie it is left blank The formula field I need help with is titled Year/Season For Campaign Title. What I want this formula to do is the following: IF Title Category (Main) is Series, then use Concentrate to put parenthesis around the Seas

Newbie question, I have a URL column but also want a single text column containing the url but in plain text tried =""&{URL} but that just copies the hyperlink to the new column. any ideas?

Hi All, Was hoping to get some input on the formula below: IF({D-Phase Status} = “D1 - Concept”, DATEADD(TODAY(), 30, ‘days’), TODAY()) I am trying to put together a formula which will add 30 days to today’s date if D1- Concept is chosen in the single select menu of the D-Phase Status column otherwise, just to display today’s date. I can’t quite work out why the formula isn’t working and would really appreciate your help. Thanks in advance

The formula below works for a single linked field look up but I would like to add a second linked record and calculate the total cost. The scenairo is Linked field =unit /start time/ stop time = cost I am looking for linked=unit +unit +unit etc start time/ stop time = cost on one line IF({Field 18}<24,{Look up}*Duration/3600)

This is odd. The display field for one table in the base I’m working on shows a concatenated string that represents each record’s basic uniqueness. At the end of the string, there is a reference to a charge. Should look like “$121” etc. For most of the 1000 or so recs in the table right now, that’s how it’s working. But I noticed a few records that were displaying something like “$121.000000001”. Maybe 5% of my records are showing that, maybe fewer. The display field’s calc includes a reference to a field ‘ChargeX’, a formula field. ChargeX = HowMany * Rate. The values in HowMany and Rate are not always integers but they are usually integers and they are integers in the cases where this display problem occurs. So 1.1 * $110 returns ‘$121.00’. The formatting of the ChargeX field is set to Currency with a precision of 2 (ex: ‘$5.00’). So I’ve got a nice clean $121.00 sitting in the ChargeX field. But when the display field for the record grabs that value and sticks it at the end of the s

Hello, I am trying to concatenate 4 fields into one field with commas in between each field, only when the fields have a value in them (ie. no comma for empty fields). I’m using the below formula successfully to combine 2 fields, excluding the comma when a field is blank. But, I don’t know how to lengthen this formula to cover 4 fields. My 4 fields are, “Building,” “City,” “State,” & “Country.” I’ve tried several variations and can’t figure this out. =IF(OR(Building=BLANK(),City=BLANK()),CONCATENATE(Building,City),CONCATENATE(Building,", ",City)) Thank you for any help you can provide!

I would like to create a formula that could extract relative path from a full URL. Example: Column 1 domain1/landing-url-1 domain2/landing-url-2 domain3/landing-url-3 domain4/landing-url-4 Relative path: /landing-url-1 /landing-url-2 /landing-url-3 /landing-url-4 TRIM(MID({Column 1},SEARCH(“domain1”,{Column 1})+LEN(“domain1”),255)) I managed to create the formula that could extract from a particular domain, but I have 4 different types of domain in column 1. How can I combine them into one?