Formulas

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

Hello!I am trying to create a formula that categorises ships by type, then size category, and if they meet both of those criteria they are assigned a value that is annual fuel consumption appropriate for the vessel's size category. If not, the IF test continues through all size categories and finally ends on "NA" if nothing it true. I would have thought I would be able to nest like the below example, but for some reason it only generates the correct answer for the first two size categories, then everything higher in size gets the same second highest fuel value:IF(AND({VESSEL TYPE} = "SHIP TYPE1" , {SIZE CATEGORY}<=1000),"XX Fuel consumption value",IF(AND({VESSEL TYPE} = "SHIP TYPE1" , {SIZE CATEGORY}>=1000<=1999),"XX Fuel consumption value",IF(AND(

I am setting up a new system for our company to track our projects and record how many days each designer has worked on a project and, equally, how many days a project has been worked on. The calendar view doesn't work for us, as date ranges don't work here. We also don't want to have to fill in time sheets anymore, we just want the table to calculate the number of times a record (project) appears in the design schedule. The rollup function isn't working here to add up each instance of a record.Can you help me figure out how to set this up so that the "Projects" table, and the "Design Schedule" table link to count up how many times a project appears across the 5 days. Ideally it would be great to figure out how to show how long each person has spent on each project but ultimately the goal is to work out how much time has been spent on each project total. I don't know much about functions and their language so I assume this is wh

Hi Folks,I am essentially trying to automate quota calculations for the sales team in Airtable. As I explore setting up the tables, I'm running into constant brainfogs.1. Im dividing the "record" (quarterly target) by "3" (to break this into a monthly target) but i'm receiving a #ERROR. Can anyone help me fix this?2. The way I set up these records is to have the employee ID + Month when the plan start date is; Therefore, each employee has 12 records. How can I build a reference table so I can link the records and specifically link the values into the current table? I currently calculate the quotas in Gsheet to then copy and paste in the current process but I'd like to automate this. Additionally, if I need to link the record to each of the 12, how can I automate that? Seems like automation setup could address this issue.I thou