Formulas

Hi! I’m trying to make an aggregate of data in a table and wondering what might be a good strategy. I am trying to build a table of tasks and their “effort”. some tasks are associated with other tasks. How would I go about doing this? My initial thought was to create a linked field called “predecessor”, rollup the effort field, and then sum in with the effort for the current task. This works fine for current task and 1 predecessor, but won’t go beyond that. I thought about doing a rollup of the rollups, but that led to circular referencing. Is there a formula I’m missing to make this work?

Is there a way for us to set up a column that can count the number times of the Last Date Modified was updated. My Airtable is connected to Calendly through Zapier and everytime they book a call, the record is just updated. I would like to see how many times that record is updated (to know how many times they have booked a call). Any ways to achieve this?

Hello, I am new to Airtable and am trying to translate Excel formulas into the new syntax. I wrote a formula that works for number fields (below), and would like to create a similar formula for text fields. However, I am not sure how to substitute blank or not blank in place of the = and > in the formula. IF( AND( {2021 FIELD}>0, {2022 FIELD}>0 ), {2022 FIELD}, IF( AND( {2021 FIELD}>0, {2022 FIELD}=0 ), {2021 FIELD}, IF( AND( {2021 FIELD}=0, {2022 FIELD}>0 ), {2022 FIELD}, BLANK()))) This should be the equivalent of: if 2021 and 2022 are filled, use 2022. If 2021 is filled and 2022 is not filled, use 2021, if 2021 is not filled and 2022 is filled, use 2022, otherwise, blank Any help would be appreciated!

So maybe this is far more complex than I hope it is. I’m trying to build a television show format in airtable. I want the first item to be 0:00 (hh:mm:ss) and then to have a duration field where I can put the duration of the segment in, 0:30 (30 seconds). Airtable would then take that number and make the available start time of the next rows start duration. Trying to move objects around based on start / end durations, but it would be nice if the start of the next item was the time left off of the object before it. Can airtable do that without scripting?

Hi all - I have a Zap set up that sends info to Airtable from Submittable. It adds a “1” every time a reader adds a note in Submittable. This way, I can keep track of what everyone is reading every week. I’m trying to collect the running data somehow so that I can look at the information over time, week to week. So, for example: I need to know what Daniel has read every week and over time. It would be great to have another table where I can see (week to week) what the numbers are so I can fill out my reports. Does any of this make sense? I’ve been playing with rollups, but I’m not getting it. Thanks for any help you can give.

Hi I have the following formula in my table ID column, which works great, it puts together the customer name, date of photoshoot and type of photoshoot. Leaving it blank with no error message until the data is entered, but for some strange reason the date says 23/4/2022, but the ID cell puts it together as 22/4/2022 instead. See photo…… IF(OR({Customer Name}="",{Type of Photoshoot}="",{Date and time of Photoshoot}=""),"",CONCATENATE({Customer Name}," - “,{Type of Photoshoot},” - ",DATETIME_FORMAT({Date and time of Photoshoot}, “D/M/YYYY”)))

Hello, I’m trying to extract the 2nd letter of each word after a comma but I fail :frowning: I have : Hello you, how are you, good And I looking a formula to have this result : He_ho_go Do you have any idea ? Thanks a lot

Hi everyone! Is there a formula to count the number of records were added per quarter/year/month? I would like to know how many were added in general and how many records were added during a specific period by category.

Greetings! This is my first post here in the airtable community. I’m importing a data set that has a series of fields that are using binary (0’s and 1’s) to allocate tags to a record. Ie: The “green” field is either a 0 or 1 to tell me that the record is green. I’d like to create a formula or automation that will read these 6 fields of 0’s and 1’s into a single “multiple select” field that displays the associated tags. Any clues or articles I should start with? Very new to formulas and automations.

Hi, is Airtable able to do Finish to Start formulas that autofill dates similar to smartsheets? I’d love to have a sheet with the tasks and deadlines are autofilled based on the formula for how many days from Go-Live, so all I would enter is the Go-Live date and all tasks are automatically filled. Is this possible in Airtable?

Hi there, I’m looking for some help! I have a formula in a cell that basically creates an automatic Project ID. For this Project ID I use two fields: A list of teams, which is formatted like “B – Brand” or “PM – Product Marketing” Autonumber Field I’m now using the following formula: LEFT({Requesting Team:},2 ) & "-" & Autonumber This formula is great, except for the fact that I am sometimes left with spaces: The first Brand project becomes B -1 I would like for that to be B-1 (minus the space) Any tips on how to achieve this? Can I use LEN 2 unless the second character is a space, and then use LEN 1? Thanks in advance!

Hai, can anyone help me to write formula but based on single select column. If single select = Kg will be multiply 1 ( * 1) then If single select = Box will be multiply 5 (* 5) and then if single select = 200 gram will be divide 5 (/ 5)

Need help seeing why the Quarter is not pulling correctly. I have the following formula trying to pull the Quarter, but it shows Q1 for all the months. Example 12 is showing Q1 when it should show Q4. IF(OR(FIND(01,{Month}),FIND(02,{Month}),FIND(03,{Month})),“Q1”, IF(OR(FIND(04,{Month}),FIND(05,{Month}),FIND(06,{Month})),“Q2”, IF(OR(FIND(07,{Month}),FIND(08,{Month}),FIND(09,{Month})),“Q3”, IF(OR(FIND(10,{Month}),FIND(11,{Month}),FIND(12,{Month})),“Q4”)))) Here is a picture of the column I am pulling from and the results which you can see the 12 are not correct as they should say Q4.

We are membership based organization. We currently are implementing a new membership fee structure based on how old the member is: <20= 250 € 20-25 = 500€ 26-30 = 800€ 31-34 = 1.200€ 35 = 1.200€ (older and equal to) 40 = 500 € I have all the members “ages” but would like to create a column next to it with a function that will automatically calculate what membership fee they are with that age. Is there someone who can help with this? I have tried different things I found online through forums and youtube but im struggling to make it work. Thanks so much in advance

I am using Airtable to create naming conventions. I have used the concatenate function to put together the code: CONCATENATE({Country Code},"-",{Key Solutions Code},"-",{Digital PRG Code},"-",{Digital Platform Code},"-",Year,"-",Quarter,"-",{Purpose Code},"-",Description) Digital PRG Type options are Paid Social, Paid Search, Web Content etc. Digital Platform options are LinkedIn, Facebook etc. What I am trying to figure out, is that when Digital PRG Type is selected as Web Content, then how can I build the formula to remove the hyphen in the formula campaign name. As of now, this is how the code looks in the front end: GLB-PP-WC–2022-Q1-LG-TEST (you’ll notice the extra hyphen next to WC (web content) because the value in the table is blank (intended). What I want to show is the code like this when Web Content is selected. GLB-PP-WC-2022-Q1-LG-TEST Thank you!

I have a list of Holiday dates in a rollup field linked to another table. I tried to add that rollup list of dates to my WORKDAY Formula, but it’s producing an error. If I copy/paste the string of dates between ‘single brackets’ the formula works perfectly but I can’t surround the Holiday field in single brackets. Here is what I currently have: IF({OE Date},DATETIME_FORMAT(IF({Deposit Hard Date}, {Deposit Hard Date},WORKDAY(DATEADD({OE Date}, {Deposit Due (Days)}-1,'days'),1,Holidays)),'MM/DD/YY'))

I’ve used Airtable before, but never set up my own base before, so pretty new to this. I have two tables Students Daily Submissions Students is a fixed list of enrolled students that is linked to Daily Submissions Daily Submissions is fed by a form that collects a student (selected from a menu from Students), a link, and a description. When someone submits a form, it also collects Created Time in a field called timestamp. In Students, I want to create a field called Most Recent Share that will display the date of the most recent Daily Submission for each student. After reading Rollup field overview and topics including Formula to calculate the latest Date in an array and Roll up max date but show another field I tried creating a Rollup that collects ARRAYUNIQUE(values), which showed nothing, and MAX(ARRAYUNIQUE(values)), which showed a column of zeroes But I don’t know which of those is correct, or what the Formula (or is it Lookup?) in the Most Recent Share field should be, and h

I am using a software to autofill information in via a form where uses copy data from an existing tabl. There are linked record fields I want to pass into the appropriate fields onto the other table. However, I can only pass in one unique ID and not an array of multiple records. For instance, I have a record with this value of a lookup field with record IDs: rec7Nk1lhJ0VJ0PNa, recbWvD9TI1PkMpdM I need to have this in a formula where the output is: rec7Nk1lhJ0VJ0PNa This should also work for where there isn’t a comma-separated string, as if one record is already it should just pass the same value Frankly, it doesn’t matter if it’s the first, 2nd, 3rd, etc record, what matters most is I need to pass only one record ID into the URL of the form Is there a way to use Regex_Extract() to obtain just one instance? edit: I learned that regex doesn’t work on an array in airtable and you have to turn it into a string, but the formula doesn’t work that I’ve arrived at so far: REGEX_EXTRACT({ACompa

Need to remove word ‘Keyword’ from field ‘formula test’. I would like to have a separate column (Mostly a formula column) that would give me output with no word ‘Keyword’ in the list.

I need help building a nested IF formula with ORs that implements the following logic: If {Field A} = ValueA OR ValueB then return contents of {Field X}, If {Field A} = ValueC, then return contents of {Field Y}, if {Field A} = ValueD OR ValueE, then return contents of {Field Z}. Thanks!

I have a base I am using for rehearsing a play. I have several tables including a character table, which I linked to the scene table, inputting which characters appear in each scene, finally linking a rehearsal table which has which scenes are being rehearsed on any given day. In the rehearsals table, I am trying to find any characters that will appear in any of the scenes rehearsed that day, without duplicates. I have tried a rollup command and a function command. I have played with ARRAYFLATTEN and ARRAYUNIQUE to no avail. Can anyone help?

Hi Everyone, I had previously accomplished this in Google Sheets, but since moving to Airtable, I’ve had a bit of trouble replicating it. We’re a dog training company, and want to count the number of packages that each trainer sells in a given month. My table has a column with a date field that contains when the package is sold, a column with the trainer’s name (a linked record to a trainer’s table), and a column if the package has been paid for. Previously in Google Sheets, I used a table that contained the date range for each month (ex. 1/1/22 to 1/31/22). The formula would see if the date the package was sold was between these dates, checked the trainer’s name matches, and if the payment contained the word “Paid”. Would love some help trying to get this working with Airtable :slightly_smiling_face: Thank you

Hi Community! I am trying to create a formula for wind bearing and convert it to a text. This is the formula I successfully created in Microsoft Excel and I was trying to follow a similar logic for Airtable… =CHOOSE(1+ROUND(A2/22.5,0),“N”,“NNE”,“NE”,“ENE”,“E”,“ESE”,“SE”,“SSE”,“S”,“SSW”,“SW”,“WSW”,“W”,“WNW”,“NW”,“NNW”,“N”) This is the formula I unsuccessfully created for Airtable. ROUND({field1}/22.5),(“N”,“NNE”,“NE”,“ENE”,“E”,“ESE”,“SE”,“SSE”,“S”,“SSW”,“SW”,“WSW”,“W”,“WNW”,“NW”,“NNW”,“N”)) Note: {field1} is a integer field containing the wind bearing number. Any ideas?? Thanks!

I’m guessing this scenario has already been covered, but I couldn’t find it anywhere. Feel free to point me to the relevant article if so. I’ve got 5 status fields, one for each of 5 teams. When a new project is added, the person adding needs to identify whether or not each team has any tasks to do in association with the project. If so, they pick “Yes”; if not, “No”. Then each team is responsible to update the status in the field from there. I want to create an AutoStatus column that put out the following: “In Progress”: if any of the team statuses is “In Progress” “On Hold”: if any of the team statuses is “On Hold” “Complete”: if all 5 statuses are either “Complete” or “No” (ignore "NA "- I’m taking that one out of the list) “Not Started”: if all 5 statuses are either “Yes” or “Reviewing” This level of complexity in formulas is beyond me at this point, but I’m confident that someone here will know how to do what I’m looking for! This will be an awesome step forward from what we’re

I currently have an IF statement formula nested into a longer SWITCH formula, and am looking for a way to modify it some. Currently it is IF({Total Order Sold Price} < 15, 2.95, IF({Total Order Sold Price}>= 15, {Sold Price} * .2)) I want to first check for data in the {Total Order Sold Price} field. If there is data, I want it to run this set of IF statements. If there is no data, I want it to run the same IF statements, with a slight modification: IF({Sold Price} < 15, 2.95, IF({Sold Price}>= 15, {Sold Price} * .2)) I’ve tried writing it a few different ways, the best I came up with is this: IF({Total Order Sold Price}, IF({Total Order Sold Price} < 15, 2.95, IF({Total Order Sold Price}>=15, {Sold Price} * .2))), IF({Total Order Sold Price}=BLANK(), IF({Sold Price} < 15, 2.95, IF({Sold Price}>=15,{Sold Price} * .2))) Nothing is working so far though. Any help is appreciated