Formulas

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

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

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’) F

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’,

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?

if ( {revenus ou dépense}=‘revenu’, LEFT({catégorie}, 5), {revenus ou dépense} ) i want my key field to display different things according to what is written in another field thanks girls and guys in advance

Creating a table that will be split into different views for different online marketplaces, as they each require different fields. The Status field at Abebooks corresponds to the Update Delete field at Amazon, though, so I’m making the Update Delete a formula field that converts the language like this: ‘’'SWITCH( {STATUS}, “For Sale”, " ", “On Hold”, “Delete”, “Sold”, “Delete” ) My question is how can I add a statement that will look at the last modified time field I’ve named Updated and return the value “Update” only if that Updated field has today’s date? So that I can get only the updated today records into their own view to be downloaded as a CSV files I can use for the online marketplaces? I feel like this is a thorny one, but this community has surprised me on multiple occasions so maybe I’m just too much of a newb to recognize real Airtable thorniness.

Hi, I have managed to fetch latest modified date to AirTable, to see when a task was completed - now i want to add a field displaying WHO finished the task - is that possible?

Hi All, I’d like to create a unique number for a record based on the year and week. So for example, I have a date field which I use as YEAR(Date)&" - "&WEEKNUM(Date). So if the date was today this would give me an output of 2020 - 43. Now, I want to add a counting number for each record with a date in that week in order of when the record was created. So the first record created with a date in that week would be “2020 - 43 - 1” and the second record created would be “2020 - 43 - 2” and so on and so forth. But when the week changes to the next week I want the counting number to start again, so it would then go “2020 - 44 - 1”, “2020 - 44 - 2” and so on. I just don’t know how to do this and would appreciate any help thanks.

Thanks for being here! I’m trying to calculate weekly sales as a percentage of monthly sales goals. I’ve made a conditional formula to populate sales goals according to month, and my formula is accepted but only returns errors. I’m also wondering if I can bypass the separate sales goals field and embed the formula into a calculation, i.e., can I say that IF {Month} is MM/YYY, {Current Month Sales}/‘XXXXXX’ If not, I’d like to just be able to calculate simple division, but that doesn’t seem to be working either. Heather

Hi, I need some help, I am counting the total hours worked of my employees, But I need a formula that won’t count the lunch time or that some how if table (lunch in) or (lunch out) is empty it won’t say NaN. some people take lunches, some don’t this is what I have it like this. I have it to count hours worked before lunch and after lunch and add that all up. Screen Shot 2020-10-19 at 1.25.01 PM|700x99