Formulas

Hi everyone! Kindly asking for your help for formula in airtable. I’m trying to keep track of our team’s first response time and completion time duration for our Airtable tickets but we would like to exclude the count for non-working hours, weekends and holidays; our working hours will only be from Monday to Friday, from 8:00AM to 5:00PM GMT +8. Currently, I’ve already set it up like this: First Response Time duration in minutes DATETIME_DIFF({First Response Time},{Opened date & time (PHT)}, 'minutes') and Overall completion Time duration in minutes DATETIME_DIFF({Resolution Time},{First Response Time}, 'minutes') This is it looks like at the moment. Hoping for help from anyone! thank you in advance!

Hi, on “IiloscZlotowekTydzien” i have formula =iloscZetonow*2. Field iloscZlotowekCalosc I need to update each week by adding iloscZlotowekTydzien to iloscZlotowekCalosc. Any idea how to do it?

I cannot edit any of my formula fields. I even created a new database in a new workspace just to see if there was some sort of reference error in my existing database. No luck. In all instances the formula field is simply greyed out / inactive. No error messages. First time poster. The same question was posted in April but it was never resolved and the thread is closed. Anyone know of a solution?

Hey awesome community! Working on a new setup and cannot wrap my brain around something. I have an inventory management setup, with a table for all Inventory items and a table for Purchase Orders. As items are received I would like the amount to add into the “Quantity On Hand” total…but only once. Using the 12ga Sheet Aluminum as an example, I would like the On Hand to update to 10 units with todays (06/30/2022) received order, but I don’t want the formula to look at the received amounts every time…just pull it in the one time. We do a yearly inventory to update what we have and I don’t want to worry about the formula confusing things. I hope this makes sense to someone who can help make it make sense to ME!

Hi. I’m looking to track how much time (in days) a lead spends in each stage in a pipeline. I’m basing it on start and stop dates of time in stage. The formula I’m trying to accomplish is this (hope it makes sense): Is the start date blank? YES: Do nothing, stay blank. (end) NO: (ask) Is the end date blank? —1. YES: Subtract today’s date from start date —2. NO: Subtract end date from start date I came up with the following but I just get an error that the formula isn’t correct. IF({Start Date} = BLANK(), “”, IF({End Date} = BLANK(), DATETIME_DIFF(TODAY(),{Start Date}, ‘days’), DATETIME_DIFF({End Date}, {Start Date}, ‘days’) What’s not working?! Definitely new to AirTable and formulas in general so if there is another way to approach this, I’m all ears! :). Thanks!

I have a concatenate function in a table’s primary field. One of the fields it combines is a rollup of companies in a Companies table. Typically, it’s one company per record, occasionally two. To strip the pesky quotes I get when there’s comma in a company’s name (e.g. Habor Lights, Inc.), this formula only partially works: SUBSTITUTE({COMPANIES}&"", '"', '') When two companies are rollup up comma-separated, the formula strips that comma which is in fact needed Company 1: Toyota Research Institute Company 2: Abbot Ventures Rollup: Toyota Research Institute, Abbot Ventures With formula applied: Toyota Research InstituteAbbot Ventures Can I improve on this formula or is there a better approach altogether? Edit: tried the in-rollup approach suggested in this thread – Close, but no space between company names. SUBSTITUTE(ARRAYJOIN(ARRAYUNIQUE(Companies)), '"', '') Result: Toyota Research Institute,Abbot Ventures

Hi, I can’t find a solution to this: I have a Lookup field that consists of multiple text values separated by a comma. I’m adding a seperate column to indicate the status based of that Lookup field which should contain of 3 possible values: IF FIND “Paid” = Completed IF FIND “Pending” = Pending IF FIND AND “Pending” & “Completed” = Partially Completed Now it seems that AND doesn’t work in combination with FIND. Has anyone found a solution for this?

Hello! Thanks in advance for any help. I am trying to make a formula that will extract a text string from a URL. In this case, the URL is a playlist on Spotify, and the text string is the playlist ID number. Spotify Hang on Little Tomato Denny P · Playlist · 1 songs The ID comes after the playlist/ and before the ?, so in this case it would be 5napFAiwbcMwne6jUfmspg I know this should be fairly easy with REGEX or something, but I’m not up to speed on that stuff yet, so any help would be appreciated! Thanks!

I have a formula that needs data from the record beneath it in its view. The next record in the view could be arbitrary, so this formula should look for the record below the current record for the values it needs. In a spreadsheet, this would be easy enough, just select which cell I’m targeting. However, this is tricky because this is dependent on the view configuration. Switching to a different view or changing the sort order would change the value of the field. Is there a way to do this?

I am showing a database for timesheets. We collect data by day and by hour. But our administrative department processes the information by period. Sometimes some employees have outside contracts and so their hours change. The number of hours for a period goes from X to Y and back to X. For the moment, each case is automated according to conditions. (Information coming from the employers) But good boss, sometimes wants to change the total hours of a period directly and that all applies to the period. In the example, he wants to change directly the 70 and not the 7… any idea ?

Hello All! I’m using a formula to concatenate different dates an area was worked on as the record ID. In one tab I have all the offices and all of the associated data with them, and in the other I have a submissions page that’s linked to the office page. My problem is some of them concatenate as “AreaName” - Date and others are AreaName - Date. I have attached some screenshots for a visual. Thank you for your help!

Hi! I am trying to build a formula but I haven’t quite found the solution in the community. I have a table with different customers information, with fields such as “name”, “surname”, “email”, “phone”, “company”… I would like to create a separate table in which I can count how many customers I have from the same company. So let’s say I have: Table 1: customer 1 — Company A customer 2 — Company A customer 3 — Company B customer 4 — Company C customer 5 — Company C customer 6 — Company C In table 2, I would need to show: Company A — 2 Company B — 1 Company C — 3 I have the company field built as “single select”, since I work with a limited and specific list of companies. Not sure this makes any difference in terms of formulas. Thanks a lot in advance!

Hello Community! I am trying to figure out the following with a nested IF formula- returning a value based on the name of the project. I have 8 different projects to categorize into one of 3 categories. I keep getting an error message saying to check the formula - what am I doing wrong? IF({Project}=”Hours”,“Personnel”, IF({Project}=”Sales”,“Finance", IF({Project}=”Marketing A”,“Finance”, IF({Project}=”Marketing B”,“Finance”, IF({Project}=”Expenses”,“Finance”, IF({Project}=”Diversity”,“HR”, IF({Project}=”Life Event”,“HR”, IF({Project}=”Executive”,“HR”,"Unknown”)))))))) Thank you!

Hello community, my record has a date and looks up a roll-up like the below example. I’m trying to figure out the right formula to extract the string below: Rollup: …5. 04.05 Vila Falo,6. 05.05 Alvero,8. 07.05 Gjirokastra… Record date: 05.05 Current formula: MID({Rollup}&“”,FIND(DATETIME_FORMAT(Date,‘DD.MM’),{Rollup}&“”)+5,XXX) Output: Alvero,8. 07.05 Gjirokastra… Desired output: Alvero XXX is the part where my monkey brain fails to come up with a solution to determine the length of the string to be extracted. Your advice would be appreciated. :slightly_smiling_face:

Just started working with a dental lab and I’m trying to create a base for targeted outreach to dental offices. After being out in the field today I have entered my notes and assigned a rating to each office. I would like to use 4 stars or 5 stars ratings to trigger an email automation to respective teammates about these particular offices.

Hi!, I am trying to use the REGEX formula, but I cannot use it propertly. I need to extract names from a string. To make it easier the names are written after a hashtag #. This are examples #TOM write the essay of a client Complete the packing list for #Peter From an old post I managed to use this formula REGEX_EXTRACT({Transaction Name}, “#[^\s]+”)) written by Justin Barret This formula kind of works, but is giving me the answer with the hasthag in front #TOM #Peter I needed it without the hashtag. Any clue about how to do it? I am getting lots of trouble to understand the sintaxis of the REGEX formulas. Thanks!!

Is it possible to round up those really long decimals? Thank you!

I have been searching and searching but can’t quite find the answer. I have text strings in languages that are not Latin script. I see that languages aren’t supported, but that seems to be referring to the interface. I also see the set_locale function, but that seems only to work with datetime_format. Is there a way to set_locale for all of these languages for plain text? Currently, my long text fields are outputting a lot of garbage when I copy/paste into them. I tried converting to rich text, but this had the effect of chopping off half of the text. I also tried using set_locale with the appropriate language, but that just threw errors. Thanks!

I’ve been using Airtable for some years now and would consider myself pretty knowledgeable. However, I’ve never been able to figure out how to, or even whether, I can use the automated functions at the bottom any Base Window which gives a variety of information about the column above it, for eg. Sum, Average, Median, Min, Max etc. Is this possible, if so, how? Thanks.

For example, I have a date field, some of which are filled in, some of which are not. I want to make a second date field that is a time offset from the first. I want this formula field to stay in date format so it can be exported to a calendar, etc: However, when the first field is blank, the second field shows an error instead: How can I change my formula to output “absence of date” when the first field is blank? Using IF(BLANK( to return "" or 0 breaks the date formatting:

This is probably a stupid question, but why is my result not a number in the following If statement? IF({Course Order}>0,{Cost-1st Attendee}+{Cost-2nd Attendee}+{Cost-Additional Attendees}, “”) It actually works, but I can’t format it, and the Summary fields don’t work. When I click on the Formatting tab, I get the following message. “Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.” How can I get the results to be formatted as currency (I assume that fix will make the Summary fields work)?

Airtable Auto Fill - Airtable Explore the "Auto Fill" base on Airtable. Is there a way for me to auto tabulate from “Inventory” description to the respective field under “description” column for all the rows? Title: enter titleYear: enter yearType: enter typeDimension: enter dimension

I am working in a base built by someone else. There is a Leads and Clients table, a Products table and a Sales Log table. Product prices are listed in the Products table and I imagine this should feed into the Sales Log which is then linked to Leads and Clients. There is a rollup field for lifetime value in the Leads and Clients table but it’s not working. Unfortunately, I can’t see what it is supposedly rolling up so I can try and fix it. I know it’s pulling info from the Sales Log but how can I see exactly which field it’s rolling up so I can fix it?

I want to convert my Created Date field into Text like “Latest to Oldest” and “Oldest to Latest”. I have checked all previous articles and had tried DATETIME_FORMAT and SET_TIMEZONE but it doesn’t return text it returns time.

Hi there, I want to return a value of “Morning news” when the time from a live date falls between 5AM and 9AM. This needs to be irrespective of date, so I made a field that parses the time from the live date field. Currently I’ve got: IF(AND(VALUE(DATETIME_FORMAT({ADMIN - Time},‘Hmmss’)) <90100, VALUE(DATETIME_FORMAT({ADMIN - Time},‘Hmmss’)) >45900), “Morning news”, “Other”) But that’s returning an error. I tried it without the Value parameters too.