Formulas

can you please help me convert this to the proper duration format? OR maybe into Seconds.I have the formula below:VALUE(LEFT(SUBSTITUTE({Fulfillment Time}, ' minutes and ', ':'), FIND(' seconds', SUBSTITUTE({Fulfillment Time}, ' minutes and ', ':')) - 1)) But it will return an incorrect number or 0 seconds if the Fulfill

Hi all, apologies if this question has been asked and answered already. [EDIT: attached a screenshot since the embedding images don't seem to work.]I am creating a table of contacts for relationship management. For each person, I have the fields "Introduced by" (whoever introduced me to them) and "Introduced me to" (whoever they've suggested I talk to). I want these fields to mutually autopopulate. For example, if John Doe suggests I talk to Johnny Day and Jane Smith, and I've put Johnny and Jane in John's "Introduced me to" field, I want "John Doe" to automatically appear in their "Introduced by" fields, instead of having to do it manually:Ideally, it should work the other way around as well. If I put "John Doe" in Jane and Johnny's "Introduced by" field, their names should automatically appear in John Doe's "Introduced me to" field. The logic is

I want to know when an approved campaign goes back in to revisions so that I can review it again. I have multiple other reasons to want to know when a campaign status changes and what the previous status was; e.g., trigger subtasks for team members, notify leadership etc.I use the following statuses: planning, assigned, in progress, completed, approved, scheduled and launched.and two nonsequential values (revisions, missing inputs) that can be used at any point in the process.What I currently set up:The status field is a single-selectAn automation that captures every change in campaign status and populates another tableThat status tracker table is linked to the campaigns tableThe status tracker table has a 'status field' last modified date fieldWhat I want is a way to compare the previous status field to the current status field so I can trigger actions based off of the new status. What I cannot figure out is how to connect the most

I am trying to figure out the formula to populate the date of a specific day in relation to a designated date.Below are some examples of what I mean.I have a date field titled EVENT DATE. I want the column titled PAYMENT DUE to populate the date of five Fridays prior to the event's date aka EVENT DATE.It seems like this should be so simple and/or already a question asked. But I am having a hard time finding a question similar to this.

I have a “Campaign Name” table that contains a “Product” field (which uses abbreviations).The “Campaign Name” table has a formula field which uses the “Product” values (along with other fields) to generate a campaign name.Our request form (on the “Campaign Name” table) uses the “Product” field, which shows the abbreviations in a picklist. You can only choose one Product per campaign.I have created a new “Products" table which maps the Product Codes (abbreviations) with full Product Names.For example:Product CodeProduct NameWDGWidgetWhat I would like to do is have the form display the Product Name to the user but still use the Product Code when generating the campaign name. Is this done just with Lookup fields, or is another formula required to do the mapping? And then what product field am I r

Hi,Let's say I have a People table with Name A, Name B, Name C.Another Table "Sports", which links Name A with Football, Name A with Football (again), Name A with Basketball, Name B with Basketball, Name B with Basketball, etc.In the People table I want to create a (rollup?) field that returns e.g. for Name A: "Football (2), Basketball (1)" as a string.Haven't been able to figure it out playing with COUNT, ARRAY, etc in Rollup field and searching this forum. Any ideas?

I am trying to create a formula to calculate the number of days a specific status is active. For example, how many days the ticket is "On Hold". The need is to calculate the number of days the ticket was actually active, in other words the difference between the {Closed Date} and the {Created Date}, minus the number of days the ticket was in a "On Hold" status. However, I am not finding a way to keep the counter, when the status changes. Currently, the counter resets to 0 when the status changes. If you could advise me on how to do this, that would be great!

HiIs it possible to count the number of changes or modifications that have been made to a specific record?Rgds

Hi there and Happy Friday!I am trying to calculate the time it takes an individual to get a specific type of number at the time they enroll in our program.The formula I have is essentially, "If the individual does not have a preexisting number, calculate the time difference between the date they got the number and the date they enrolled in the program." The formula is: IF({LO has FTN at time of Enrollment}="No",WORKDAY_DIFF({Enrollment Date},{Turned in FTN})) That works but if “Turned in FTN” is blank, the calculation results in "#ERROR" and I hate "#ERROR". So, that means I need to add something like, “If “Turned in FTN” = Blank, then “””. I tried adding that a few different ways but am still getting #ERROR. Any ideas how to get rid of the #ERROR? Thanks in advance!  

Ok, I have been trying to do this for a while on my own with no luck.I have a list of serial numbers. These serial numbers have more than one entry. I group the view by serial number so I can see all entries for any particular serial number. What I would like to do is figure out a way that I can see only the last entry for every serial number. My thought is to have a third field for a conditional formula. Something that just posts the number 1 if it is the most recent date. Then I can use a view to only see everything with the number 1 in that field. The problem is that I need to pull the most recent date for EVRY serial number group. Not just the most recent date over all. Hope that I am explaining this right. Here is an example of the a sample base: Please help! Thank you.

Hi guys!I'm using Ubiqod for attendance tracking in our company. When you scan code, you click on the correct button (arrival, break, going home). After you submit it, the data comes to google sheet and with automation it gets to the Airtable. Now the problem occured. The timestamp from the google sheets it's not the same in the airtable. My guess is that this has got to do something with the different timezones. In googlesheet it's correct timestamp, in airtable it's 2 hours later.This is timestamp from google sheet:This is timestamp from AT:How can I transform this date in AT so that the hour will be correct (it was supposed to be "14:07" not 16:07). Thanks! 

Hello!I have two fields that I want to meet certain criteria to return a value in another field.Return Value "Late" if field 1: Advanced Record contains "ADVANCED" and Field 2: If Date/Timestamp is after 10p EST otherwise return "On Time" if Date field is empty have this field stay empty Thanks!

Hey I am trying to set up a formula for the below problem. I am provided with a list of names in the 'home' column and certain entries I need to change to end up like the 'bot name'. I have tried switch/If functions and I just get errors.  

Hi!I'm using an airtable base to calculate my working hours as a freelancer who works in live entertainment and I'm trying to figure out the best way to have a flexible system for different employers' rules for minimum paid hours of a shift, after how many hours before OT and DT, and also how some allow for DT after midnight. There is also the possibility with some for Sundays to be considered "premium days" regular time=time and a half all day. Right now there is a tick box per "shift" record that allows me to say this shift is "premium".Right now I have a table of "billing rules" with lookup values for each parameter.Each shift is linked to a "billing rule" record, and these values are lookups. The lookup values are then used to determine how many hours fall under each.

DG mw me nahi hai na to be a great day hoga to be a

im accautn is Block support of the day is the best way to get

I would like to create a script that checks if my employee has already taken an advance on the same day. If it has already been taken, the new record should be deleted. I do this in the form. How can I get the current record ID from the form and delete it or add it if it was the first time. let table = base.getTable("Zaliczki"); // Zamień "Nazwa_Tabeli" na nazwę swojej tabeli// Pobierz dzisiejszą datę w formacie ISOlet today = new Date().toISOString().split('T')[0];&nb

Hi all,I have a base table with the transaction history which lists each profile's contribution amount, date, and the fund invested. Each profile is allowed to invest in many funds and can also invest in the same fund multiple times. How do I create a view that would sum the investment total and sort by funds at the same time?Here is the transaction history table (base):I created a "List" from Airtable with Level 1 as Funds and Level 2 as Profiles, but I was not able to sum the values from the same profile:As you can see below, there were multiple transactions from the profile that starts with "2...." within the same fund

Hi,i am using python-requests and trying to use f-string with filterByFormula here is a simple implementation:hard coding the name worked:   url=f"https://api.airtable.com/v0/{base_id}/{table_name}/" headers={'Authorization': f'Bearer {api_key}', 'Content-Type': 'application/json'} query={"filterByFormula":"({full_name}="some name")"} response=requests.get(url, headers=headers, params=query) data=response.json()    but when i tried to use f-string it didn't work. here is a a piece of code where the problem occurred:   url=f"https://api.airtable.com/v0/{base_id}/{table_name}/" headers={'Authorization': f'Bearer {api_key}', 'Content-Type': 'application/json'} employee_name="some name" query={"filterByFormula":f"{{full_name}}={employee_name}"} response=requests.get(url, headers=heade

My text fields imported from excel have a question mark where an apostrophe should be.  Is there a simple formula to correct this?

Hi everyone - I have a Airtable based that hosts events across states. And another base that hosts emails and location of users. I'm trying to create an automation that send emails weekly event reminders to users based on the event location that matches the users chosen locationsIn the email list base - a user can input multiple location based on their preferences. In the event based - events are listed based on state. I think I should be doing the automation in the email list and link that to the event base. I hope to send a list or grid of event to X user name and email. I was able to setup the first half of the automation using find records but not sure how I can automatically send the emails with limited manual work. could someone please help me out? Happy to share my base to assist 

I have a URL field that I need to grab specific text from (in bold). Does anyone know if there is a formula I could use to easily grab just the string in bold without pulling in additional data from the 3 possible formats?Here are the multiple formats that the URL could be in:Sample format 1:https://www.website.com/en/others/A1087A50-AB73-48AA-B056-A3A472711A9ASample format 2: includes ?ABC=1 at the end https://www.website.com/en/others/A1087A50-AB73-48AA-B056-A3A472711A9A?ABC=1 Sample format 3: includes 2 URLs only separated by a spacehttps://www.website.com/en/others/A1087A50-AB7

Hello,I am working on a base that includes artists' names and a lot of them have diacritics in their first and last name. Is there a formula that I can use that could identify if there are diacritics used? It can be a simple True or False formula that I can then use a filter to isolate everyone with diacritics in their name. For First and Last name I am used simple Single line columns.A list of names is important because it is used for a remapping feature on our website, so the end result for airtable needs to be a filtered view of names. I appreciate any help you can provide with this. 

Is there a script/formula I can use to extract zip files as separate files and have them saved in another field?

Hello everyone,Me and my team are working on a database for my business, to be frank we're new to Airtable, and there's something we can't quite figure out. There's this one table that is for recapping all of our clients' consultation sessions. After each client consultation sessions, that session will get added to this table as a separate entry, and be given a specific Consultation ID. The goal is to make something like this:Consultation ID: EVA/COU/2024.06/000004-001The things that matter here are the field "Unique ID" which is 000004, and the three digit after the dash, which in the example is 001, which should signify the amount of sessions they have with us, 001 here meaning it was the client's first ever session.The field "Unique ID" is tied to the field "Participants" which is linked to a different table filled with details of our clients. Each Par