Formulas

I am running an event/webinar series, and I’m wanting to identify the total number of people attending the event for the first time (as to get an indication of growth). I have two linked bases The event series the attendees Key information I believe required (that I have): Date of each event Event name Attendee name Attendees assigned to events Total attendees at the event My thinking was to create some sort of formula as you see in this image below: (However, you cannot dynamically link a date from the base into the date field in this image. ~ Essentially my logic is: Count attendees if this is their first event. – Get dates of the events –List dates that attendee was at an event. – Get an event date –If the attendee Does not include dates before event in testing. Count. ~ Aggregate and sum of growth is printed. Any tips on how I can make this happen?

I work for a small college, and we want to give Airtable a try for tracking our applicants and student financials. I’m running into problems when students are registered into more than one program, notably our “double diploma” program, which is actually two programs bundled together. (I also want to see if I can use it to make our scheduling simpler, so the Program Group is an important level for that) The problem comes when I’m trying to calculate the amount of tuition that a student owes at any specific point in time. If I lookup Tuition costs for a student, I get two results concatenated, and the formulas do not like this. Even if I can get the formula to calculate so that I get concatenated answers, I can work with that (ex. Tuition 1, tuition 2—formula magic—>due “now” (actually start+/-weeks) 1, due “now” 2 ) If I set up another table, from what I understand, I have to go through and manually enter the links. I’ve already made links between three different tables at this point

I’m looking to create a datestamp field based on if a Single Select field is either COMPLETE or CANCELED, otherwise it should remain empty. I’ve searched through this site and tried a variety of options but have had no success. IF(OR(Status=“COMPLETE”,“CANCELED”,LAST_MODIFIED_TIME())) **This returns all cells as blank Thanks in advance!

Hi, I am getting an Error back on the very basic SUBSTITUTE, which I have used numerous times in other tables of my base. SUBSTITUTE({Record Name}," “,”-") {Record Name} is a FORMULA FIELD that pulls the text from a LOOKUP FIELD (in the same table) that pulls the text from a FORMULA FIELD in another table. Any ideas? Thanks in advance. Tobias

Hello, I’m NEW to Airtable (so TY for being patient & detailed) I have a base with 2 tables in it, Item & Value. I’m trying to collect all of the notes for each items. All of the tables are linked, and I have been able to bring each of the notes into one cell (example: 0, 17, 37). But now I need to multiply those numbers together not sum them. I can’t do a PRODUCT (Values). Here my setup Thks

Hi, Link This workbook has a sheet called ‘Test’ which has fields that include stops & mileage. These are the x & y axis fields of the associated table ‘Sliding scale’ Y = mileage x - stops I am struggling to write a formula that can find the associated rate based on the number of stops & mileage. Note, that mileage could be 112 and stops could be 73 so the formula would need to look in between nearest numbers to associate the correct row/column combo. Answer to above should be 2.07 Can anyone help? Please

What I’m trying to do is what I describe in the title. I have a field with a value. What I want, is to transfer that value to a field of ANOTHER record on the same table. Of course I want this to happen only if that value is a negative number, but this is not my problem.

Hi, in a link to multiple records field, i collect several strings (image urls) to one product output of this field {Images} in page designer would be like “url1, url2, url3” I need to split these urls linewise in page designer, some boilerplate added, e.g.: “url1.jpg” “url2.jpg” “url3.jpg” While i have several ideas on how to solve this in the event of just one string, iterating the array of multiple records is a challenge (at least to me). Any ideas? Thanks in advance! Best Marcus

Hello! I am looking for help creating a formula for data entry automation. Screenshot 1: Table: Activities Category column: Multiple select Grants column: Links to another record Workplan project column: Links to another record Screenshot 2: Table: Grant 1 Deliverable: Long text Activities: not sure what field type to use, formula? Workplan projects: not sure what field type to use, formula? I will enter data in the “Activities” table, including 1 or more categories for each activity, 1 or more grants for each activity, and 1 or more workplan projects for each activity. In the example, I have titled the activity “test webinar” categorized it as “webinars” made it apply to “grant 1"” and “WP project 1.” Since it applies to Grant 1 and the “webinars” category, I would like it to then automatically enter the Activity Name under “Activities” in the “Grant 1” table in the “conduct at least 5 webinars” deliverable column. I would also like it to automatically transfer the accompanying workpl

Is it possible to edit a specific cell of one record in a particular formula field?I have, Contract Start Date and Contract End Date (formula of Start + 1 year). Some contracts are 18 months or 2 years. Can I adjust those specific instances accordingly? Thanks!

Hi there, I have accomplished a few formulas through my airtable base but I have trouble with this one: In a library, I want to return “not in stock” for items that are signed out and have not yet been returned. In writing this would be: If SignOutDate contains a value and ReturnDate contains no value, return “not in stock”, otherwise “available” IF( AND( {SignOutDate}<>””,{ReturnDate}=BLANK() ), ”notinstock”,”available” ) As I am new to the AND/IF business, I might have severe problems in here already? If not, could it be that the date fields are the problem, they are set as ISO? Any help highly appreciated. Best, Sisi

I’m trying to send an email (using Zapier) to a contact once a record has been on the page for 1 week. for example: someone submits a report- record is added. Once 1 week elapses a field changes to trigger a Zap to send an email to a contact. Any ideas of how?

I know that I am probably approaching this formula wrong, or maybe it isn’t possible with the way MIN()/Dates/Fields work in Airtable - but I figured I would ask the community for a little help on this one. Context: Need to assign work order due dates based off content Air Date. More specifically, I am trying to take the earliest date found for each month then subtract 28 weeks (dateadd -28 weeks), and use that Min Date - 28 weeks as the Work Order Due Date for every record that has an air date with the month that calculated it. Example: Record | Air Date | Work Order Due Record 1 | 04/22/2023 | 9/19/2022 Record 2 | 04/14/2023 | 9/19/2022 Record 3 | 04/03/2023 | 9/19/2022 Record 4 | 04/19/2023 | 9/19/2022 Record 5 | 07/22/2023 | 12/29/2022 Record 6 | 07/25/2023 | 12/29/2022 Record 7 | 07/13/2023 | 12/29/2022 In the example above, the four records have an Air Date in April, but the formula would calculate the Work Order Due Date as 28 weeks prior from 4/03/2023 since its the minimum val

This might be simple, but I fail to get my head around it. Help, please. We have 2 tables; Profile - name, image, etc Availability - date/time, profile linked record 13/2/2022 4:57pm 20/2/2022 4:57pm 23/2/2022 4:57pm 11/2/2022 4:58pm On Profile table, we need to roll up the Min(values), but only display date/time past NOW. If now date/time is 19/2/2022 4:45om the correct answer needs to be: 20/2/2022 4:57pm

I want to make a formula or automation to do the following task: Step 1 When a single or multiple select field has a status “Revisit” :two: Step 2 I need to automatically have a :four: Date 2 for the specific record with the status “Revisit” :two: , 30 calendar days after :three: Date 1. Note 1 The issue I face when trying to create a formula field is that the secondary date applies to all records, which I don’t want. Just for “Revisit” :two: . Note 2 I am not too sure if I can create conditional logic automation to perform this action. This is what I have attempted to no avail. Trigger When a record is updated with the status :two: “Revisit” Conditional Action If :three: Date 1 is not empty Update :four: Date 2 +30 Days Any Suggestions? This is the structure to the logic: :one: Address (Main) :two: Single or Multiple Select Field :three: Date 1 (European) :four: Date 2 (Formula or Automation dependent on :three: )

Hi community! I am trying to track the status of payments which are made in multiple installments. For instance, we may make a payment for $400,000 but pay quarterly installments of $100,000. I would like to have the Total Paid field auto calculate using the payment schedule in the table. Total paid would equal {payment one} + {payment 2} + {payment 3} + {payment 4} but only when the corresponding payment status field has been marked as “complete” AND the corresponding payment date be in the current year (01/01/22-12/31/22). Any ideas? Thanks!

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

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

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!

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

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.

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

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.

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.

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.