Formulas

I would like to capture three dates: When a new record was created (Date Added to Accelerator) When the status in “Approval Status” was changed to either Approved / Not Approved (Date Approval was Made) The difference in time between those two dates (Approval Duration) *The format for Approval Duration should be in days and hours. Example “2 days, 3 hours” (Or something like 2 d 3 h, that format isn’t as important as it is to show the amt. of time in days and hours) My concern is that because I’m using two different functions (Created Time and a Formula) that it’s having trouble computing the difference. Please let me know if I need to change of of my existing formulas or what I can use for the Approval Duration column to make this formula work. Thanks!

This closed topic answered my question about accurate time stamps for imported records but i can’t figure out the formula… what is the operator for “not blank”. This is all very new to me and i am learning as i go… IF({Original Time Stamp}=BLANK(),"{Time Stamp}",IF{Original Time Stamp}=ISNOTBLANK, {Original Time Stamp}) https://community.airtable.com/t/created-date-time-field-and-imported-data/30941

In my database of garden plants I use the field WETENSCHAPPELIJKE NAAM, it has two or three strings, the genus, the species and/or the variety of the plant. the variety always between apostrophe’s. I like to create a formula-field which automatically creates a CODE, the first character of each string in the field WETENSCHAPPELIJKE NAAM, without the apostroph. I tried but without the result, can somebody help?

Is there a way that we can automatically calculating based on different conditions For example IF Service: Deep tissue massage Time: 60 minutes Result: Commission: 80 IF Service: Deep tissue massage Time: 90 minutes Result: Commission: 150 IF Service: Pain Relief massage Time: 90 minutes Result: Commission: 140 Thank you so much :pray:

I’m new to Airtable and databases in general. My database will be used to organize a dance competition. I’m trying to construct a formula to calculate how many medals I will need for each competition. The number of medals needed depends on the type of dance and the number of competitors, a few of which are shown in the image below: My formula keeps returning an error (Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.). I have been through many Airtable articles, tutorials, and forum posts, and still do not know what I have done wrong. Any help is much appreciated. IF( AND( OR({Dance} = “Reel”, {Dance} = “Slip Jig”, {Dance} = “Single Jig”, {Dance} = “Light Jig”, {Dance} = “Treble Jig”, {Dance} = “Hornpipe”, {Dance} = “St Patricks Day”, {Dance} = “Humours of Bandon”, {Dance} = “Job of Journeywork”, {Dance} = “Blackbird”, {Dance} = “Garden of Daisies”, {Dance} = “White Blanket”), {Number Entered}>=4), 1, IF( AND({Dance} = “Reel & Set”,

Hi, I’m having issue with calculating meds left. Example, I take PILL-A MORNING/AFTERNOON/NIGHT like this 0/1/1 and after 10 days I will use 20 pills. If I have 40 pills I will need refill after 20 days. PILL-B I will take eg. 2/0/0 and if I have 50 pills, after 25 days I will need a refill. How to calculate this? I have found date for refill but multiplying with ZERO returns infinite. Airtable Grid view - Airtable Explore the "Grid" view on Airtable.

Hello, Is there a way of adding all of the values within a multiple select field type cell? Any help or suggestions would be appreciated!

Hello lovely Airtable Community. I’m building a CRM system - I have full profiles built for leads from Instagram and Facebook in one table. I have just added a new table inside the base and I am sending information from Zapier to Airtable for customer purchases. Does anyone know how I can look up / roll up the information so that the products and the amount spent in the Shopify table show up in my original CRM table? I hope this makes sense! Thank you so much

Hi - I have a column {Count of Amounts Paid of $250+} and I want to divide it by 9 because the number of scholarships available is determined at 1 after every 9 enrollments that are $250 and over. In an adjacent field - but because, for example, 51 divided by 9 is 5.6666666667 I want also want to ROUNDDOWN so that the scholarship fiel doesn’t display more scholarships than are actually available (only when it gets to numbers evenly divisible by 9. For the scholarshps field I tried: ROUNDDOWN({Count of Amounts Paid of $250+}/9) but I get an error message. I imagine my syntax is off in someway but I can’t figure it out. Thanks for any help you can offer!

I’ve been pouring through the other IF threads, and still can’t find a solution that returns the correct results. Instead, I’m getting #ERROR. Here is what I want to do: I need to set a date in the “Next Update Due” field. It should be 35 days from “X” date. I need the {Item Start Date Manual Override} field to have priority over the others, unless {Item Start Date Manual Override} is blank. SO: If {Item Start Date Manual Override} is not blank, then i want to add 35 days to {Item Start Date Manual Override} otherwise If {Last Keyword Insertion Date} is not blank, then i want to add 35 days to {Last Keyword Insertion Date} otherwise i want to add 35 days to {Date Item Added to Air Table} Here are the two versions of the formula as I’ve written them, but it gives me an error unless there is a value in the {Item Start Date Manual Override} field IF( and( {Last Keyword Insertion Date} = BLANK(), {Item Start Date Manual Override} = BLANK() ), DATEADD( {Date Item Added to Air Table}, 35, ‘

I have a Table in which I track all my contacts of organizational partners. I log the date I contacted them, and them I have a frequency that I set for that partner (the frequency can change based on time of year or other circumstances so each record needs the frequency). There are 5 different cycles (3 Weeks, 6 Weeks, Quarterly, Bi-Annually, Annually) and there are three options that I need to return NOTHING in the formula. I believe this is a nested if statement, but the formula is throwing me off because of the date contacted column needing to have different amounts of days to return a specific date in the future. Any help would be appreciated. I’m happy to answer questions. I’ve included screenshot in case it helps at all.!

I would like to flag a new record if it arrives and the email address of the new record matches an email address of a record already in the base. I have gotten as far as lowercasing the email addresses to ensure the capitalization doesn’t throw it off, but I just want to have a visual flag immediately showing as an output in a field. We have the pro version, and the dedupe block, but we want to just be able to see quickly if a record exists before wasting time deduping it or outright ignoring the new record.

Hey everyone, question related to automation…and maybe rollups? Let me explain… In my airtable base, I have 2 tables… one table has the submission data from a form I created. Let’s say it’s an internal company feedback form. The fields are NAME and FEEDBACK_SCORE. In another table, I have columns with NAME and FORM_COMPLETED. I’m wondering if there’s a way to automatically change FORM_COMPLETED from ‘no’ (default), to ‘yes’, when someone submits that form. I’m thinking this requires some type of rollup and other formula, but I’m not experienced enough to know if this is possible. Any ideas? Thanks!

Hello, I use this formula to compute the projected funding date for a borrower. the Funding date is usually 3 days after the borrower signs the final documents. This formula does it just fine, but I need to exclude Saturdays and Sundays from the formula. for example, if a borrower signs on Monday the 15th, then he should be funded on Thursday the 18th. However if the borrower signs on Saturday the 20th then he should be funded on Wednesday the 24th. Please note, signings can be done on saturdays, but funding can not. existing formula. DATEADD({Signing Dt/Tm},3,‘day’) thankyou in advance

IT’S ME AGAIN, I would like to add a column that contain code based on fields. This is the code with the field names inserted. But how would I get airtable to pull those fields and write out this code? <a href=“Instructors page Link”>name medium Date Range FriendlyTime slotsBottomline Thanks to all of you that help us undereducated Amy Brakeman Livezey Acrylic March 25-27, 2021 9-4 $325.00/3-day workshop Results in

I have only 2 numbers 15% 161.80 I have to get after the deal is done $161.80 and the the other guy have to get 15% of the deal. So the total deal will be $190.35 - (161.8 is a 15% decrease of 190.35.) How can I get to this number ($190.35) in Airtable, when I have only the 2 other numbers (1. 15% 2. 161.80) In Excel I will make this as follows =161.8 / (1-15%) but I can not make this work in Airtable Thanks in Advance

I have a base of events where I am tracking event updates. I am trying to write a conditional formula that will display up to 3 different possible date fields based on 11 outcomes using data from fields already existing in each record. I have tried nested IF formulas using IF( AND( as well as IF( OR( and have not been able to correctly solve the problem. I am either getting a formula that produces a date that is not 100% reflective of all criteria or an error message. I looked into SWITCH formulas but got lost… Fields within this base include: Field Type | Field Content Date Field | Original Event Date Single Select Field | Update to the event (cancel, postpone, reschedule) Date Field | Announcement of Update Date Field | Rescheduled event date Single Select Field | 2nd Update to the event (cancel, postpone, reschedule) Date Field | Announcement of 2nd Update Date Field | 2nd Rescheduled Date I want to display the appropriate date for each record based on the following criteria: Return

I’ve been incredibly focused without achieving what a simple VLOOKUP in Excel allows me to do. What i am missing This seems rather simple a priori this table is created by a form, fairly simple Then another table from another form. I just want to have a Y/N in the column “exists on enrol table” I’m ready to use integromat if needed, i just need some guidance for this The only matching key is email and cannot be exposed in the 2nd form for privacy reasons

It appears that the DATETIME_DIFF() function rounds down to a whole integer when specifying hours regardless of it decimal is specified to two places.

Hi all, I have read through the other threads regarding this subject of blank cells in a formula but wasn’t able to successfully implement a solution. In my primary field, I have a formula combining the Sponsor Name with the date the Ad Copy was submitted. The two are separated by a space. I would like to change this so that if a Sponsor Name cell is empty, that part of the formula returns “?”. Same with the “Copy Ready Format.” If that is blank, I’d like it to return “?” too. Can you help me write the formula the way? Thank you in advance!

Hello, I am trying to create a formula to track of the aging of a record. I am using date fields for this formula, the Booked date and the Funded date. I was able to come up with a formula that calculated the age, but it only calculated the age once the funded date was updated. I need to be able to keep track of the ongoing aging of each record from the date it was booked all the way to the date the record is funded. I also need the formula to stop keeping track of the aging once its been funded. for example, if a record was booked on June 1st and today is June 17th,and the record has not been funded I need to see that the record is 17 days old. However if the record was booked on June 1st and funded on June 16th, then even though today is the 17th, the formula should still show that the record is 16 days old.

Hi! Can someone tell me why my formula is not working? IF({Rating}=1, ” :star: ”, IF({Rating}=2, ” :star: :star: ”, IF({Rating}=3, ” :star: :star: :star: ”, IF({Rating}=4, ” :star: :star: :star: :star: ”, IF({Rating}=5, ” :star: :star: :star: :star: :star: ”, ))))) Basically, I want to convert a lookup value of integers into an emoji. Many thanks.

What would be the correct way to have the duration / age of a ticket in Airtable being any status that isn’t “Complete”? In the “Days Open” field I currently have got to this formula : IF(LAST_MODIFIED_TIME(Status), WORKDAY_DIFF( TODAY(), LAST_MODIFIED_TIME({Status}) ) ) What’s the best way to stop it counting once the ticket is completed?

Hello! I am having trouble creating a formula to calculate my service fee. Our fee is 10% of the order total (min fee $10, max $25.) We charge and extra $20 IF the order is for delivery. Please Help!!

I am trying to manage a data dictionary and generate XSD (XML schema definition) as part of managing API definitions in our project. However, I keep getting blocked by this error. Here is an example base (I stripped down all columns that are not relevant to the XSD generation): Airtable Airtable: Organize anything you can imagine Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free. The formula for column XMLElementDef of table Attribute should be: IF(IsPrimitive, "<xs:element name=\\"" & Name & "\\" type=\\"xs:" & DataType & "\\"/>", DataTypeXMLElementDef) but the else part of the IF - DataTypeXMLElementDef - cuases the circular reference error. Recursive structures are necessary to many of my bases because many of the data I am managing is naturally hierarchical in nature (XML, JSON, folder/directory paths, org structures, etc). I understand that end