Formulas

Hi, I am trying to clean some of my data but I’m not sure which formula to use. I have three columns (URL, Date, Data). I need to extract the strings from the data column, the date and the URL (which are always different). In the Date column, I have LEFT(Data,10) which returns the date perfectly. In my URL column, I have MID(Data,11,256) which returns the URL. I’ve set up a count of 256 characters to be sure I get all of it but I’m sure there is a more elegant way to do it. Now the main problem is that sometimes I get 2 identical URL in the data column but I only need one of them. So how can I be sure to always get the first URL and discard the second one if I get a duplicate one. Many thanks in advance!

Hi all, I am struggling to find the error in my nested if() statement. I am trying to create a ‘status’ field for project outputs based on their due dates, using a check box ‘complete’ field, and two date fields. If checkbox complete is checked, show “Complete” If not complete and the original due date has not passed, show “On Track” If not complete but original due date has passed but not the tolerance (adjusted) due date has not passed, show “Overdue within tolerance” If not complete, but both original and tolerance (two seperate date fields) have passed, show “Overdue exceeding tolerance” Currently the formula I’ve used is this: IF({Complete?} = ‘1’, “Complete”,IF(IS_BEFORE({Baseline due date (DD,MM,YYYY)}, TODAY()), “Overdue within tolerance”, IF(IS_AFTER({Tolerance (no Covid)}, TODAY(), “Overdue exceeding tolerance”, “On Track”)))). I am not sure if I should be using an AND somewhere. Currently the formula is accepted but only ‘Complete’ and ‘Overdue within tolerance’ are returned

IF({amount of discount}=0," ",IF({amount of discount}<0,{amount of discount})) I got this to do what I want - only show a value on a sales receipt if the value is <0 The problem is that the answer is considered a string answer and I cannot format it to display as currency. All fields leading into this formula are numbers (formatted currency) I want the results to be displayed as currency. Suggestions?

Hi SQUAD, Maybe this is a stupid question, but I’ve been wracking my brains trying to figure it out and couldn’t find a solution. My Scenario: Table 1 - list of 300 users with assigned devices by serial number. Table 2 - list of 1000 devices with other specs, including serial number. What I am trying to do Create a third table with all Serial Number listed on Table 1 (I can copy manually) and display on Field 2 the name of the user (record from Table1) and display on Field 3 Other Spec (record from Table2). On excel, it’s something simple that I can resolve with VLOOKUP (value, table, col_index, [range_lookup]). Any solution/idea for Airtable? Thanks so much!

Hi, I am trying to get a field to show either Priority Normal, Priority high or Priority Critical depending on how far away from today the “manufactured by” date field is. ie. if 3 weeks away priority normal if 2 weeks away priority high If 1 week away priority critical Aby help would be greatly appreciated.

Hello, I’m new writting formulas, I’ve read all the guides on airtable’s website and other posts in the forum. However I can’t get my formula to work. I only need that when the value of a cell is equal to a number a specific name appears. This is what I’ve wrote; IF (Total = “6.99”, Hernia discal, BLANK()) But airtable won’t let me keep the formula because it’s misspelled. Any advises? Thanks.

Hi. I’m trying to use DATETIME_Format() inside of a formula field to display the day in which an order was placed. These are the dates of the orders. For reference, the 18th took place on a Sunday. The formula block is a very simple DATE_FORMAT() function, showing the day. However, it seems as if the date format is incorrect and is defaulting to GMT time (adding 4 hours to the time fields, causing the time to roll over to the next day). I don’t know how to fix this, and I can’t change it because under formatting it says: Can someone please help?! Thanks

I am building a database for a music education charity which has five bands, about one hundred members and over a hundred musical instruments which we loan to members. With different tutors issuing instruments at different venues, I want to keep track of which members have what instruments, which instruments are in the store cupboard and which ones are elsewhere eg repair/service. I have various locations on a single select field so an instrument can be allocated to a location. Then, if “Existing Member” is selected, another single select field of member’s names comes into play. Each instrument on the [Instrument Stock] table has a unique ID directing to a form and profiling the Instrument ID and a time stamp. To increase compliance with logging instruments into the database, I have made some QR code labels which stick to the instruments and their cases. These open form refilled with the instrument ID and a timestamp. After about two weeks building the system, I became aware that for

I have created an awesome base to run agendas for my team. People can submit topics, I can print them a pdf of the weeks agenda, take notes on the meeting, and create action times. I also have views for each member of my team and meeting so I can run automations to remind them to submit topics two weeks out and the day before. I want the ability to be able to automatically carry over the notes & action items (each their own column) from the previous meeting to the next meeting so the agenda printed has those items in it. How can I create a formula that automatically carries over items from the previous row essentially into a column on the next row?

I’ve knocked my head against this for a while, including seeing a bunch of possibly related posts. My twist is that I’m trying to use two different data types. OK: I’m keeping track of something over time. These days, I can record the date it happens. For stuff that happened a while back, I only know the year. Therefore in a certain Table, I have one column for Dates (if known) and another column for Year (if date not known) . The Dates column links to a Table where each record is a date. The Year column is a multi-select dropdown list. What I want to do is make a column called Year Overlay that extracts the year from any date in the Dates (if known) column and joins it with the year from the Year (if date not known) column. CHAPTER Date (if known) Year (if date not known) Year Overlay (link to Dates table) (Multi-select) !! HELP!!` Ch 1 2020/10/22 2006 2006, 2020 Ch 2

I have a base that is set up to pull in a lot of data from our CRM, and then give my staff the ability to manage their event workflow with additional fields. It is set up so that there is one record created for the Parent and multiple records created for each “Dummy” event (long story). The Event Manager is linked to the Parent Event and each staff member is linked to one of the “dummy” events. I would like to write a formula that scrapes each of the linked staff records in the the dummy events and combines them into a new cell in the Parent event. It would end up looking like this: So ‘Matt’ had the “RXPO Real Game” and ‘Patrick’ and ‘Robin’ are on his event. In the field “Runners Assigned” it would list Robin and Patrick as linked records as well. Any help greatly appreciated.

Hustling bookseller back with another puzzler. What formula would I use to take data in my dad’s {Publisher MLA} field (NY: Atheneum, 1979) and separate it out into three formula fields ({PublishPlace}, {Publisher}, and {PublishYear})? Bonus for a formula that can change NY to New York too. Please and thank you. :slightly_smiling_face:

I try to filter by text field with quotes in formula AND(OR(owner="\\"Fname\\" \\"Smith\\"",owner="Foo"),OR(type="audio")) and items with quotes is missing. I got Foo only not “Fname” “Smith”

How do I change the #ERROR to null? How do I change the #ERROR and NaN to null?

(topic withdrawn by author, will be automatically deleted in 1 hour unless flagged)

Hello, I need to create a formula to include permanent (fixed) class hours in our airtable school calendar :blush: . Classes are repeated on the same days at the same time , depending on education levels throughout the school year (from September 1, 2020 to July 5, 2021, eliminating the following school holidays: • From Saturday October 17 to Monday November 2 • From Saturday 19 December to Monday 4 January • From Saturday February 20 to Monday March 8 • From Saturday April 24 to Monday May 10 • From Thursday May 13 to Monday May 17

Hi, I would like my primary field to be autonumbered, starting with “PW10001”, then +1 value to each new record (so PW0002, PW0003, etc). I think I need a hidden Autonumber column to give me each new sequential number, but I’m stuck on how to string the formula together in the primary field to bolt the “PW10000” to that. Can anyone help?

Hi Guys, Having trouble finding out how to write a formula to check if one field contains another field’s data. The specific use case is data being pulled from an ecommerce site. Each order comes from a page dedicated to a suburb/postcode. Each order populates a record with this suburb/postcode in one field as well as the delivery address in another field. I am trying to create a formula that will check if the delivery address contains the same postcode in the other field (and if so label it “true”). Any help would be much appreciated! Thanks in advance!

First - I am pretty new to writing formulas. I know enough to get myself into trouble. :slightly_smiling_face: Here’s my problem … I have a table which is a listing of tasks that are assigned and have associated dated. The fields in this table includes: Date field that includes the time field (Start Time) I created an automation that would send an email when a particular person is added to a record (assigned a task). This email includes in the body of the email, the name of the task and the date and time that this task is scheduled to begin by inserting data from the Start Time field. The resulting email looked ok except that the data from the Start Time field was ugly (for lack of a proper technical term). So I learned how to convert the data from the Start Time field into text by creating two new Formula fields using the DATETIME_FORMAT function: Field name: Date STRING // Formula: DATETIME_FORMAT({Start Time},‘MM/DD/YYYY’) Field Name: Time STRING // Formula: DATETIME_FORMAT(SE

I’m a new Airtable user and still learning the ropes. I have a nested if statement that keeps getting kicked back as invalid and I cannot figure out what the problem is: Here’s what I need the formula to populate: IF{Appra Waived?} = ‘YES - Waived’, “N/A”, IF{Appra Waived?} = ‘NO - Needed’, then: a)IF{Appra Accepted By Due Date} = ‘NO’, then insert a date 4 days after {Appra Order Complete} not including weekends and holidays. b)IF{Appra Accepted By Due Date} = ‘YES’, then insert a date 2 days after {Appra Order Complete} not including weekends and holidays. Here’s the formula I wrote that does not work: IF({Appra Waived} = ‘YES - Waived’, “N/A”,IF({Appra Waived} = ‘NO - Needed’,IF({Appra Accepted By Due Date} = ‘NO’, DATETIME_FORMAT(WORKDAY({APPRA ORDER COMPLETE},4,2020-10-12,2020-11-11,2020-11-26,2020-12-25,2021-01-01,2021-01-18,2021-05-31,2021-07-05,2021-09-06,2021-11-11,2021-11-25,2021-12-24,2021-12-31’),IF({Appra Accepted By Due Date} = ‘YES’, DATETIME_FORMAT(WORKDAY({APPRA ORDER

I am trying to get values that follow a defined string in incoming emails. Example: I have zapier setup to bring emails of interest in from UPS and I want to put the "tracking number in a new cell from that email. FIND("Tracking No.: ",{UPS Email})+11 what i get in the cell is 11 not the next 11 characters that I want.

Hi guys, I have a question about a formula regarding a URL. I want to have the domain without all www. etc before the domain and without the .com or whatever. LEFT( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE({Website URL}, “http://”,""), “https://”,""), “www.”, “”), IF( FIND("/", SUBSTITUTE( SUBSTITUTE( SUBSTITUTE({Website URL}, “http://”,""), “https://”,""), “www.”, “”))=0, LEN( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE({Website URL}, “http://”,""), “https://”,""), “www.”, “”)), FIND("/", SUBSTITUTE( SUBSTITUTE( SUBSTITUTE({Website URL}, “http://”,""), “https://”,""), “www.”, “”))-1)) My script is working on the left side and it gives me the URL: airtable.com, which is my goal. Now I want to delete the .com, what do I need to add? Thanks in advance!

If i have 2 tables one has rent of different location and in other if some one booked from to some location when from and to matches in both tables, in booking table give rent amount from rent table. Is it possible or any way to do this action and get result.

Hello everyone, for an automation with Zapier I would like to count in a formula field in Airtable how often the respective mail address already appears in this grid view. For example, if xxx@gmail.com is already two times in this view, then it should display 2 next to the mail address in the formula field. The email field is the primary field. Unfortunately I didn’t find a solution here in the community, I hope you can help me! Thanks a lot!

My linked table has 17,000 records. I want to look up the record I need but I only can look up one of these options: Keyword A Phrase (“keyword A keyword B keyword C”) What I want is I want to be able to look up “Keyword A Keyword Z” where keywords are not part of a phrase. In the picture below - I want to be able to find the entry for Baby Carriages & Buggies by searching keywords Home Buggies. Right now I have to either search Buggies or search Hearth or search the expression “Home & Hearth” or search expression “Baby Carriages & Buggies”. Are there some operators or wildcards I can use?