Formulas

Hi Community! I’m working on a table that allows my company to easily track and time rental property application processing times. Fields I have are listed below: Unrelated fields are hidden. We want {Hand Over Timer} to start timing once a record gets updated to “Processing”. We don’t want to track time over weekends. For this, we decided to implement the help of the WORKDAY_DIFF() function. The problem is, this works horribly when time is involved. I’ve had to create this monstrosity to make it accurate: IF({Processing Activated (Automation Requirement)}, IF( {Application Tagged As Handed Over}, IF( (WORKDAY_DIFF({Application Started Processing}, DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'Australia/Brisbane'), 'M/D/YYYY h:mm') ) >= 1), (((WORKDAY_DIFF( {Application Started Processing}, DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'Australia/Brisbane'), 'M/D/YYYY h:mm') ) * 24) * 3600)) - 172800, (((WORKDAY_DIFF( {Application Started Processing}, DATETIME_FORMAT(

Hello, I have set up a integration of airtable and asana with integromat. Now i would like to update task in asana with a comment on the due date. I would like to add this comment at 5 pm on the day of the due date since our office closes at 6 pm. The assignee could then reply with reason why the task was delayed. Currently, the Due date column doesn’t keep time enabled and i would like to keep it that way. Default is 12 am i think. Now, i created the same column ‘due in’, but i want it to be in hours which become zero at 5 pm on the day of the due date. In Integromat, i can simply filter all records where the ‘due in’ is zero or less to add a comment. Using, Now () in formulae in returns in hours but i want it to be in my own time zone. I am using this formulae. DATETIME_DIFF(NOW(),{Due date},‘hours’) Please guide.

Hi I found a lot of information in this website, thank you very much to all the helper ! In fact, I practicly found my answer in this thread : Parsing Text without Split() But I’v still have a problem. For different class, I’v student. Sometimes 3 students, sometimes 4 or 8. It depend. So I’v a form for student who want to apply to the course. Here the result for example : Brad Pitt, Christian Bale, Matt Damon, George Clooney I want a column “Brad Pitt”, an other “Christian Bale” etc… @Bill.French help me a lot with the thread and i’m globally ok except for the last name who never comes up. It’s a lookup field btw. For the first name I have = MID({xxxx (from xxx)}, 1, SEARCH(",", {xxxxx (from xxxx)})-1) For cut the last name = RIGHT({xxxx (from xxxx)}, (LEN({xxx (from xxxx)}) - FIND(",", {xxx (from xxx)}))) Then the second name = MID({xxxx}, 1, SEARCH(",", {xxxx})-1) The again for cut the second name = RIGHT({xxxx}, (LEN({xxxx}) - FIND(",", {xxxx}))) I put a maximum of 15 student so I

We have a table we’ve created where we need to bring a SUM of multiple records in another table over. For instance, we’ve grouped users in TABLE1 which then shows us the SUM spent for that user. In TABLE2, we want to show the SUM of that group in a field so we can export it with the additional information contained within TABLE2. Any ideas?

Hey everyone, I’m trying to add a field that will return either a calculation or text based on whether another cell is empty or not. I have a working formula for the difference between two dates, but I want to put that inside an IF formula that will return the value of the difference in days if the field is populated or a text “Not Yet Resolved” if the field is empty. Here’s what I have at the moment: IF({Resolved} != BLANK() , VALUE(DATETIME_DIFF(Resolved, Created, ‘days’)) , “Not Yet Resolved”) But it is returning an error. Can somebody provide a solution? Thanks

I can get a portion of the formula to work properly, however, I need additional steps and I’m stuck with the formula portion. My goal is to use this setup to generate an email using zapier. My current setup Current Formula: IF(DATETIME_DIFF({Request Status} !=“Pending Quote”, TODAY(), ‘days’) <= 3, ‘Send Reminder Email’) I only want the formula to work if Request Status is “Pending Quote”, but, it’s currently reading the blank fields too. My current formula also is set for a Reminder email at 3 days, but I’d also like to send an email at 5 days, and every day thereafter if the Request Status has not updated. Also, is there another way to run zapier without creating a view? I prefer not to move these records to a different view only to send an email. Any help is appreciated.

I’m trying to put together a formula IF statement to extract certain parts of a URL depending on what is found in a specific cell for that record. In plain language: IF {Published on/by} is “Website.com” pull 6 characters after “post=” in the {URL or Permalink}, OTHERWISE, IF {Published on/by} is “YouTube.com” pull 15 characters after “watch?v=” in the {URL or Permalink}, OTHERWISE say “no URL found” I tried this but it doesn’t work: IF({Published on/by}=“Website.com”),MID({URL or Permalink},FIND(“post=”,{Published on/by}), 6), IF({Published on/by}=“YouTube”),MID({URL or Permalink},FIND(“watch?v=”,{URL or Permalink}),15),“no URL found”) HELP! Thanks! IF({Published on/by}=“DBC/YouTube”),MID(FIND(“watch?v=”,{URL or Permalink}),15),“not known”)

Trying to workaround the fact that can’t do both OR and AND filters by using an IF statement. This is just the beginning but I can’t get it to throw the correct result. Suspicious it is because it doesn’t work for Fields that are linked to other tables, but not sure why it wouldn’t. Am I missing something else? Perhaps because it is a string? It seems like a really easy IF statement, but it throws “No” when it should throw “reponsible”). Help! If this can’t be fixed perhaps you have other ideas for how I can show only those entries for which a specific entity is either responsible or accountable (ideally with the ability to group by which they are)/

Hi! In my base I have a list of my customers by phone number. We are trying to link if they attended a zoom session. I have a script that can link if it find name or phone number matches. Sometimes though customers dial in by phone. Registration reports from zoom provide numbers in a string of digits with a country code at the front. Ex. (123) 456 - 7890 would show up as 11234567890 For my script to work I need to have the two columns that have the same values. Is there a way to format using formulas either column to make them match. Any ideas? Gabi

Hi… First post here… I’m trying to simply search a lookup field for a certain Order ID number (397147) and have the formula field display “VIP” if it is found, and “NON-VIP” if it is not found. The formula is only showing “VIP” in those records where the Order ID number that is being searched for is either the first or last Order ID in the lookup field. If 397147 is in the lookup field, but not the first or last Order ID, it displays “Non-VIP”. Here’s the formula: IF(FIND(“397147”,{Product1 ID (from Orders)}), “VIP”,“Non-VIP”) Thanks in advance for any help!

Want to create a Full Name value from name elements. Wrote this simple formula: CONCATENATE( {First Name}, " ", {Middle Name}, " ", {Last Name} ) If there is a value in all three fields, it works fine, returning a result like (say) “William Jefferson Clinton”. But it also works fine if there isn’t a Middle Name value – and that is contrary to what I expect. If Middle Name is left empty, it will return “Bill_Clinton”: I’m using an underscore there to represent a single space. What I expect is for it to return “Bill__Clinton”, with two spaces in there. Why doesn’t it? What am I missing?

I have a base now where I track events and rooms the events occur in (not that I’m doing much of this during coronavirus). Each event is given a start date and end date. Usually 1 or 2 days. Each event is also tagged to a Room. Like say Room 1 or Room 2. What I’d like to do is essentially track room utilization over the quarter. So if I have 4 events, and each one lasts two days (so the start date is say 1/1/20 and the end date is 1/2/20) and each is tagged to a room, how would I craft a formula to total up the days per room?

I’m trying to create a custom formula, where it takes the first character in each word from another column. Is that possible?

Just noticed this. I have a timestamp field (that is, a date field that also shows time). I want to extract just the time. The result of the TIMESTR() function seems to add 8 hours to the result. For example, MyTimestamp = 01/27/2021 10:12am TIMESTR( MyTimestamp ) = 16:12:32 What’s up with that? I’m in Texas, six hours behind GMT/UTC. (It’s 5:30pm here, 11:30pm in Greenwich.) So if the time zone offset from GMT were involved here, I’d expect the result to be 14:12:32. William

Recently discovered Airtable and love what it can do as I track several things across a good amount of Google Sheets. I have been wanting to consolidate them for a long time and Airtable looks the part. I have a very long and complicated if/else statement that I need help with. I have never written script before and over the last three days I have tried to learn as much as I can about it. With that being said it is still over my head on how to accomplish what I want. I tried to do this with a very long nested if statement with no luck and after some research it looks like a script would easily do want I need. Maybe someone smarter than I can get it done with a formula instead of a script. Just not sure what is the best and easiest way to get this done. Our company pays out a year end profit share bonus based off the number of training hours the teammate(employee) completes. The basic formula is long but fairly easy to follow. Where things get complicated is if the teammate joined us

Hi all, I’m literally just starting out, but trying to make my primary field choose the correct investor (either personal or corporate). Is there a way to make this work in something like this format: IF( {Company Name}!=BLANK(), {Company Name}) ELSE( {Last Name}) The first part of the string works, but I can’t get the second part to work. Any suggestions would be greatly appreciated.

My current formula works. IF({Post Date}<{Today’s Date},‘READY’) But if {Post Date} is empty it is also saying ‘READY’ What should I add to the formula that ignores a field if it is empty.

Hi, I’m trying to come up with a formula ({FIELD} + 30 days - today’s date.) This seems to be an easy one but I’m missing something. Any ideas?

Hi everyone :wave: I’m pretty bad at Airtable but made a worklfow with Calendly and Airtable for a fitness coach to allow people to book lesson Now want to limit the number of booking (at 3) per people in a week. I don’t know want feature to use : Formula (of course), maybe Count…anything else ? Here a screenshot of my database

We have to deliver our assets to our agency 4 business prior to a launch so I’m curious to know what kind of formula I put in to create these dates.

This is handled in two tables Items to order (here I manage the status of whether the product can be shipped or not) Orders What I am looking for is to be able to have the sum of the total amount only of what is going to be shipped. Currently I have a Rolllup type field in the orders table that brings the data of the items to order that makes me the sum of everything (shipment + pending to be shipped). Is there a way to get the calculation only for those products that are marked to be shipped?

Hello, I have a text string of which I need to know how many times a word appears. For this example “Enviar” appears 3 times and “Pendiente” 4. With what function do I do this count? Thank you very much for your support

Excel has the IFERROR function to elegantly catch when a formula fails. Airtable doesn’t seem to have an equivalent. Is there a way to emulate this functionality? I have an airtable with a list of books that I’m reading. They have a start date and an end date, but I don’t write the end date until I’ve finished the book. I group books by end date and would like the group to be named “Currently reading” instead “#ERROR!”. Thank you

I have a formula field that includes a linked record field {Supplier}. The result is the text in the linked record field has quotation marks. Is there a way to remove the quotation marks from my results? DATETIME_FORMAT({Purchase Date},‘YYYY-MM-DD’) & “-” & {Supplier} 2018-03-31-“Supplier ABC - Your Town, WA - Unknown Address” Many thanks :pray:

I have created a concert info collection and attendance tracker. There are 18 performances. For each performance the students use a form to enter their name, pick the performance from a drop down, and enter a unique phrase key of the day. This feeds a table that is grouped by performance and sorted by Name. From here I take the names into another table that has a master list of students names and concert dates. For each concert date it compares the list of those that signed in against the master list of students. If a name is missing, it returns a “NO” to the date/name column. QUESTION: For every concert a student misses, they need to do a makeup paper. In excel I would put a “COUNTIF” statement "COUNTIF(C1:T1, “NO”) at the end of the row, (“Johnny, D”, “Yes”, “Yes”, “Yes”, “Yes”, “NO”, “Yes”, “Yes”, “Yes”, “NO”, “2”) so the students would be able to track how much make-up work they need to accomplish as they are going along. I have spent days looking for an answer. HELP :slightly_smi