Formulas

I am building a table that creates a record for monthly billing. The billing happens on the same day each month. I have a Date Paid field and a Next Date Paid formula field, and an automation that triggers after the bill gets paid to create a new record with the Next Date Paid from the old record populating into the Date Paid field of the new record.The problem I'm having is with the DateAdd() function. My current formula is: DATEADD({Date Paid},1,'month')  I thought this would just add one to the month, but it actually just adds 30 days which means that my dates won't match up. I tried adding a field with a static number(MonthDay Paid) and doing something like: DATETIME_FORMAT(DATEADD({Date Paid},1,'month'),'M/{MonthDay Paid}/YY')  but the dynamic slug breaks the DateTime Format function and returns "8/{8thnt121amy Pami4}/24". I'm trying to automate this as much as possible so that the user doesn't need to really access the data layer much, so a

My workflow is based on priority levels, so I'm trying to create a "Due date" field based on the last time a record was updated. What am I doing wrong is there a better way to create this date? 

I've created on a base a table template with several formulas that I'd like to duplicated for the various projets that I managed but when I try to copy / sync  the table to another base, all my formulas are not transfered and the columns with the Formula Field Type are changed to a Number Field type.Is there an automatic way to copy the formulas or do I have to manually copy/paste them on each new base ? I've left some some images for reference, as you can see the field type is completely different and dont include my formulas.Thanks,

Hi! I'm new to Airtable, and my company just started using Airtable to build our merchandising assortments. We currently buy products from both US and AU with different duties and transit costs associated with each region. Previously when working in google docs we used IF formalas and vlookups to reference those costs depending on the type of buy. Is there a way to add that data set somewhere in Airtable to reference? I've added a screen shot of the data set of the duty and transit costs and our google doc formula for reference as well! Appreciate any help!

Hi. Thanks in advance for any help.I'd like to take a table with dated records in them, then group them by month-Year.How do I take a date format and make a field that will allow me to show the latest records at the top for a particular month? For example:2024 - 2 (all records follow)2024 - 1 (all records follow)2023 - 12 (all records follow)etceteraThen of course as we add newer records at the top, they'll continue.Thanks!  

I'm creating a script to compare two rollup fields and add the values both share into a third column. The fields roll up linked record fields and return them as just the text in a comma-separated list. Airtable documentation says rollup fields are an array; it looks like this is technically true, but it seems to be an array of a single string. When I log.console the cell value (note, I'm using getCellValue, not getCellValueAsString), I get ["Category 1, Category 2, Category 3"]. I want to get an array of multiple strings: ["Category 1", "Category 2", "Category 3"]. I'm very new to Airtable, so before I try to figure out ways within my script to separate the one string using the commas and all that, I want to make ask:1. Is this single string output the expected behavior, or is my script calling it incorrectly?2. If it's normal, is there way to configure the rollup fields to instead keep them as separate items?

I have a table with two date fields- "date created" and date "completed". I would like to create a formula that calculates the number of dates that passed from the date the record was created to the date it was marked as "completed". What's the right formula for this?

Hi, I have two tables in a Database.One table: called "People". It has list of peoples and their Emails.Second table: called "Mailing" Has email from table one.I would like to set an new field in "People" (table 1) that marks "1" if an email is present in both tables. I called this field "Did_I_found_Email" (1 = yes, 0 = No).How do I do this?    

I have two numerical values, A and B, that I am using to create a range field. Here is my current formula:CONCATENATE({A}, "-", {B}). So far so good. The tricky part is that not all of the entries have a B value, in which cases the result will look like {A}-. Ideally I would like it to just return the single value from A without the hyphen in this scenario. I have tried using an IF to say IF(BLANK({B})) but here is where I get lost.  Any advice is appreciated! I am very new to formulas... 

Hello everyone, In Germany, there is a regulation in the Occupational Health and Safety Act that states workers who work on Sunday or on holidays receive an additional allowance on their salary. This involves a complex formula, which even ChatGPT struggles with. Maybe there is someone here who is smarter than AI.My table includes a date field including time field named {Start} and a date field including time field named {End}. I want:1. Formula field = formula that calculates the hours/minutes that fall within the period of Sunday.Example : Max starts his shift at saturday 8 PM and works until Sunday 4 AM. The formula should now calculate 4 hours.2. Formula field = formula that calculates the hours/minutes that fall within the period of holidays. The holiday dates should be defined in the forumla itselfs = ( 01.01.2024 / 29.03.2024 / 01.04.2024 / 01.05.2024 / 09.05.2024 / 20.05.2024 / 03.10.2024 / 25.12.2024 / 26.12.2024)Example : Max s

Hi! I created an Airtable to help plan my wedding. I have a table that's for Guests and in this table I have fields for their first and last name and their title (Mr., Mrs., etc.). I created a field that adds all of these fields to create their formal name on the invitation envelope. Right now the formula reads: Title & " " & {First Name} & " " & {Last Name} & " " & "&" & " " & {Guest Name (from Relationship)}  Here's where the problem comes in, for my single guests it puts the "&" after their name, for example, "Mr. John Smith &". How can I fix it so that it adds the "&" for the guests that live in the same household but doesn't add the "&" for the single guests? I was thinking an IF() statement but I'm not sure how to work it for that. I've also attached a picture of how it looks currently in my

I am trying to generate a formula where:1. If date in field x is greater than 1953, then 1 is put in field zOR2. If checkbox is checked in field y, then 1 is put in field zI came up with this formulaIF(OR({NO DIGITAL} = 0), ({CODE-DATE} > 1953), "1")However, this is producing a statement that reads 'true' if {CODE-DATE} > 1953, and '1' if {NO DIGITAL} = 0 (that is to say, box in {NO DIGITAL} is checked)Any idea why this is happening or how to get the results I want?

Hello! I have a formula to count how many workdays are left between a data and “today” and when the reference data is “after today”, the output returns a negative number (as it should). I am looking for a way to get my formula to only return “0” when that situation occurs. I haven’t figured out the nested if statement logic to make it work. Does anyone have a solution to this? Thank you!

Hi! I am trying to summarize a range of fields from one record. I know that there is a formula Sum({Column 1}+{Column 2}+{Column 3}…). However, I am looking for a formula to choose all the columns between Column A → Column X. I will have a database with many columns and I need to summarize multiple records quickly. Is there any formula that can help? Or what are the possible solutions for this? This is an example ! Thanks for any help!

I’m currently working on a setup where I need to reference data from the previous record in my Airtable base. Specifically, I want to pull data from fields in the previous entry to use in calculations for the current record.Here’s what I’m trying to achieve:I have a field called Current Tank Level in each record.I want to automatically populate a field called Previous Tank Level in the current record with the value from Current Tank Level of the previous record.I’ve tried setting up linked records and using rollup fields, but I'm facing challenges with automating the process to ensure the Previous Tank Level field always reflects the correct value from the previous record. How am I able to grab the previous record entry "Tank Level value" and automatically add it to my "Previous Tank Level" record?

Hi all,Wondering if there's a filter or formula solution (not sorting) to filter all results down to only the "Soonest one" ranked according to date field. I have a form in one table linked to a view in another table, ideally so people can only sign up for one linked choice (it's a monthly event registration). Currently I have the filter set for Date within the next month which mostly works, but a day or two before the event, both the current and next month's start displaying (because it's only 28 days away, etc.) Perhaps my logic brain isn't on today, but I can't think of good filter terms to only include the soonest event. The other thought I had was a formula which would assign numbers or filters to the nearest date (-1 for past, 1 for soonest, 2 for next, etc.) and then I could make that the operative filter, but I don't see an obvious example of that in the Formula Reference Base or guidance. Any advice? 

Hi all, I was wondering if you could help me create a formula that would output an alphanumeric ID based on variables in other cells in the row. I'd like to create an ID that include:First, if Cell A = xYx then add "Z" to start. If Cell A = YxY then add "ZD" to start.Then, take first letter from name and surname, and then add these. So John Smith becomes JSThen, add first 4 digits from date field (DOB). So, 14/11/1980 becomes 1411So, as an example, ZDJS1411.Thanks so much community!

My Rollup field has (correctly) returned this array to me ARRAYJOIN(values) by looking at linked records (4 People) and their dietary requirements (dietary preferences are determined by a multiple select field):None,Allergy to Nuts (other than Peanuts),Gluten-free / No beef / Allergy to Nuts (other than Peanuts),NoneAnother formula field formats this array into an easier to read arrangement:1 x None1 x Allergy to Nuts (other than Peanuts)1 x Gluten-free / No beef / Allergy to Nuts (other than Peanuts)1 x NoneWhat I would really like is to find a way to combine any duplicated items and show the correct count for them, such as:2 x None1 x Allergy to Nuts (other than Peanuts)1 x Gluten-free / No beef / Allergy to Nuts (other than Peanuts)Any advice on how to do this please? I want any formula to work dynamically with the content of the array, as this will make it easier to update the multiple select options without needing to rewrite any formulas that contain verbatim references to e

Hello, community! I need to create a formula (or find some other way) to keep track of changes to individual cells in "column A" via a second column that auto-populates with first entries in column A but does not change if column A changes after that. So for example:  Column A = Project Start DateWhere a user manually enters the date they begin a project.Column B = Original Project Start DateWhere it auto-populates with all existing dates in Column A + new dates added to empty cellsBut does not update itself when a user manually changes the date in Column A after that. The goal is to be able to allow users to adjust or move out their start dates as needed but also keep a record of what the original date was without adding any other manual steps. I really hope this makes sense! TYIA for any help or ideas! 

I have a linked table called "Voucher Codes" and as you can see from the attached screenshot, I can click the + and select a voucher code from this linked table and assign it to a record in the Order Fulfillment table. This is great, it works, however it shows everything in the view "Available" in the "Voucher Codes" table.  Is there a way I can have it ONLY show voucher codes in the "Available" view if it matches the "Course" field in the "Order Fulfillment" table as the attached screenshot shows? For example, if the Course says ITIL 4 Foundation w/TAKE2, it should only show voucher codes that are titled ITIL 4 Foundation w/TAKE2.  And if it the Course says ITIL 4 Foundation, it should only show voucher codes that are titled ITIL 4 Foundation, and so on. Is this possible?

I have various contracts and various contractor invoices that I need to review, and I want two formula fields to confirm whether the billing periods covered by each invoice - and the invoiced amount - are "valid". My table is pulling in the contract start and end dates, as well as the budget balance and total invoiced, from another table. Right now, I have 3 manual entry fields: 1 for Billing Period Start date, 1 for Billing Period End date, and 1 for Invoiced Amount. I created a formula field to confirm whether the billing period falls between the contract start and end date, but it currently shows billing periods that start or end on one of those dates as invalid. IF({Billing Period End}<{PoP End},IF({Billing Period Start}>{PoP Start},"Valid","Outside PoP"),"Outside PoP")I thought I could adjust the < to <=  and so forth, but figured there must be a more elegant way to do this. I also want a field that will confi

I'm building a base to help track my company's product lineup and I've built an Interface for each one of us 3 founders have our own To-Do interface, populated from a To-Do table. This To-Do table gathers information from other tables such as customer's name, product selection and other items. I want a column "Extended Task" to populate with a short checklist automatically, based on a selection of a "Task Group" field (which I've made a Single Select drop-down field in an effort to keep the names consistent). I thought of using an IF formula, where IF "Task Group" is "Quality Check", then the "Extended Task" field would get populated with "Verify Unit Powers On. Verify IR Remote Works. Visual Examination. Inputs Tested.  Doors Open Smoothly. Lights Operational." in checklist form. But not only can I not figure out how to do this complicated IFTTT formula, but I am also not sure if this is the best way to go about it.Here's an example:In this example, I've copy and pasted

Hi all, So I have created a base here (it is a gallery view)https://airtable.com/appPv62y3frc8BYJA/shrVTQGn7420FaGvUEverything in the base works as it should. The single select dropdown works great, but in the embed view, the single select dropdown doesnt exist. As in, there is no dropdown feature. Any ideas?   

How do I convert a duration into seconds?I would like a formula for the "Seconds" column that automatically calculates this. Understand this is probably so basic, but would appreciate any help. Thank you!

Hi all,wondering if you could help with the below, I need to give 1-5 rating to certain value ranges. Its daily revenue and you get rated based on your daily performance.This is the formula: =IF(AND(K2>=0,K2<=30),"1",IF(AND(K2>30,K2<=80),"2",IF(AND(K2>80,K2<=120),"3",IF(AND(K2>120,K2<=200),"4",IF(AND(K2>200),"5",0)))))K2 in Airtable is: TotalI cant get it to work, so any suggestions would be greatly appriciated.Thank you!