Formulas

Hi,Hoping that someone might be able to help me with a formula question (I'm quite new to formulas and have been fumbling along until now, but have reached the limit of my knowledge!)...I have a table of payments that have been made, each with the payment date. These Payments are associated back to a project budget and are rolled up so that, at any point, you can see the total Actual spend to date. I've got this all working well.I am now trying to add in a new field in the Project table which shows total Actual spend as at the most recent quarter end - and I'm stuck! I've managed to write a formula in the Payments table which formats the payment date into the year and quarter (so turning 2022-02-04 into 2022-1) but I now want to work out how to pull this through to the Projects table. I know that I could build a series of fields in the Projects table that rolled up (respectively) all Payments in 2022-1 and then all Payments 2022-2 etc etc, but this doesn't

Hello,  I am trying to create a formula to put together a date we expect something to be completed.Date to Client is entered by my team and is the first part of the equation.Client Timing is pulled as a lookup field from another table dependent on each client as is based in days.  Formula I have is Dateadd({Date to Client}, {Client Timing}, "days").  This equation is taking the client date (ex 12/2/25) and always subtracting one (ex 12/1/25.Is there a different formula I should be using for this type of transaction?

Hi - first timer here!I have 2 tables in 1 base.  Table A contains info about Recipes I'm creating. Table B is where I schedule those recipes for newsletter publication. Recipe names are pulled from Table A into Table B by linking fields. There are 3 recipes per date, so 3 linked fields. So far so good.I want to have the newsletter date in Table B pulled into the corresponding recipe record in Table A. See attached illustration - what formula do I need to replace the question marks with dates?

I have a table in Airtable with two fields: "Child" and "Mother" (just an example to explain the structure easily). The "Mother" field is a linked record to the same table. Here’s a sample dataset:ChildMotherRaviSnehaKaviSnehaBalliPrernaMalliPrernaSnehaMotherPrernaMotherI want to add a new field,

I'm looking to send out an email when a record is updated to interface-only collaborators. I want the email to include a link to the record in question.I've got a link to the record using the following formula:"https://airtable.com/appxxx/pagxxx?xxxx=" & RECORD_ID()  But I need to link to the Details section of the record with this link. I.e., my Interface uses the 'Sidesheet' details pane. When I click the email link, I want to be taken to the correct Interface and correct page and have the sidebar pop out too. What do I append to the above formula to make the correct sidebar pop out?

Hello everyone.I'm a Korean user, but there are still few AirTable users in Korea, so it's hard to find a solution, so I wrote to the community for the first time.I'm trying to use automation to send messages to Slack that mentions multiple users.I've tried several ways to do this, but while I know how to mention a single user, I haven't found a way to mention multiple users yet.I've tried the following formula and automation, but it didn't work.1. @Mention using a Slack username   <@first.last>   2. @Mention using Slack user's ID   <@memberID>   3. Dynamic @mentioning   CONCATENATE("<@", {Slack member id look

Hello, I am attempting to create a “email merge” document template using Airtable that allows me to: Use rich text formatting (bold, bullets, etc. for email formatting), and Use reference fields in the text (for example “Hello {First Name}, your User Name is {User Name} and your Password is {Password}.” If I try to do this in Page Designer, I can do #2, but not #1 that I know of. If I try to do this in a Long Text Field, I can do #1, but not #2 that I know of. Any ideas? Thanks, ML

I'm wondering if a formula has a way to reference the field name.  For example I want to name a field the number 10 and then use a formula which calculates if {other field} < {thisfield.name}.Is that possible?

I'm managing 5 websites, and have created one base with 5 tables - each reflecting the blog planning for one website (syncing with the website-specific bases). I've now created a table that collects all the posts from all the websites, so I can see where everything is at in a blink of an eye. What I can't wrap my head around is the dates: I now have 5 date fields (lookups), but I'd like to be able to sort things by date (or display them in a calendar, etc) - regardless of which website it's for. I created a formula field to merge the dates, but that displays them as "2015-01-01T00:00:00.000Z"... I know there's a DATESTR([date]) formula, but that gets me an error message (am I using it wrong?). Any help would be greatly appreciated! 

Hi,I have one column in my Jobs Table that pulls the Job address from my Table of Customers, its a Formula and seems to work fine it puts the correct Job address into the Jobs Table if the customer has a different Mailing address, it looks like this: IF({(J) Address?} ="Yes", {Mailing address (from Customers)}, {(J) Street} & " " & {(J) City,St,Zip})I have a second column in the Jobs Table that I need to JUST have the City Name appear in.  I have tried to write the formula, and it just returns an Error Message, it looks like this: IF({(J) Address?}="Yes"

Hello there smart folks! I am building a base for small film productions, and the industry has some odd practices I have to work around. One of those is tracking scenes on scripts in page length in fractions - specifically 8ths.For my personal tracking I have a column that just holds the size in the total number of 8ths (so instead of showing 1/8 page it is 1, 1 and 3/8 pages is 11) which I can then total into total number of 8ths.I now need to convert that back into pages in fraction form to display on one of my reports. So say I have 10 scenes over 5 3/8 pages (so my total is 43). How do I create that 5 3/8 to display?I hope this makes sense!

Hi, I am very new to scripting and managing a base for content production.  Want to create a script that will update a series of dates based off the publication date changing.  I'm getting errors and not sure why.  Any help would be appreciated.  ThanksHere is the scriptlet table = base.getTable("Deliverables");async function updateRecord() {let inputConfig = input.config();let recordId = inputConfig.recordId; // Fetch the record ID from the previous step// Check if recordId is valid (a string and not undefined)if (typeof recordId !== 'string' || !recordId.trim()) {throw new Error("Invalid or undefined record ID. Ensure the input provides a valid record ID.");}// Fetch the specific record from the tablelet record = await table.selectRecordAsync(recordId);if (!record) {throw new Error(`Record with ID ${recordId} not found.`);}let publishDate = record.getCellValue("Publ

I am working on a job board project and have this challenge. I have gone through some of the community posts but cannot figure out a solution.The marketplace is continuously updated with titles, urls etc.I want to convert the URLs to hyperlink. As well, I want to create a solution that will apply to the entire field. I saw a post but I think that is valid for permanent  urls, not where every record has a new url. Is this possible? Any help will be greatly appreciated.

Hi!I have a table where I am tracking when a certain item (Lot) needs to have its lab testing updated. Each record has a {Lab Status} single select field, a {Latest Lab Date} date field, a {Next Lab Due} field that calculates a date 6 months out from the date field, and a {Send By} field that calculates a date 1 month prior to the next due date. The rest of the fields are linked to records in another table of the same base but are not pertinent to this.Record   Lab Status   Latest Lab Date    Next Lab Due     Send ByLot AA    Up Next        08/01/2024          02/01/2025        01/01/2025There are two functions I would like to implement, if possible, either into the table or with automations:1. (automation or formula?) When we enter into a given month, I would like

Hey everyone,I’m excited to share a new tool I’ve been working on: Formula AI.Formula AI is an AI-powered assistant that helps you create Airtable formulas instantly. No more struggling with complex formulas—just describe what you need in plain English, and Formula AI will generate the exact formula for you.Key Features:Natural Language to Formula: Type something like "Calculate total sales this month," and get the perfect formula instantly.Smart Suggestions: Get tips to optimize your formulas and follow best practices.Time-Saving: Reduce the time spent on formulas from hours to seconds.How It

Hello,I have a very simple formula that is taking the values calculated from other fields and giving a total.  The problem I am running into is unless I have the formatting set to 5 decimal places the results will round.  For the work we are doing we need the data to not round, but the issue is for the exports we have to do to input the data into another system we have to remove the extra decimal places. Is there a way to keep the 2 decimal place formatting while preventing the rounding?For example if a value is 2543.54546 we would like it to be 2543.54, but if I change the formatting to two places the number ends up being 2543.55 which for reconciliation is a problem.  The values change daily so the direction of rounding also changes.  My formula is super simple {value1} - {value2}I did try adding the ROUND function, but that didn't seem to help at all.  ROUND({va

Hi there,I have two tables and an interface with a form. The first form is used to add an entry to the table. I have this displayed as a nice gallery.I wanted to add a button on the gallery, so the user can click the button on the record and it will take them to another form to request a change to that record. I've got about 90% there, but getting the field to prefill with the record_ID has been miserable. I've tried every combination and it just doesn't work.On the form I added a new field "Link to another record". I select my primary table and select some columns to bring in via lookup. It looks great, the user can use the drop down to view all submissions and select the one they want. But the prefill doesn't work to auto select it. https://airtable.com/HIDDEN/HIDDEN/form?prefill_Vacancies=["rec1YlesldDRjs06V"]

I'm sure this an easy one - but I am stumped.  Here is the scenario: I want to create a view so I can send out a daily report that shows all records that are on tomorrow's service schedule, yesterday's service schedule, and 2 weeks ago service schedule.  The current airtable filter options of tomorrow and yesterday work great until it comes to the weekend - meaning on my Friday report, the Monday activity is not showing up and on my Monday report, the Friday activity is not showing up.  I have created formula fields that calculate the next work day but Im still stuck on how to filter it.  I tried to filter using "is after" "today" but then it gives me all records in the future when I just want the one next workday.  Maybe I am approaching this all wrong and making it more difficult than it is - but my brain is not computing 

i have a record table like this (simplified)idcreated at (datetime)category(text or link to category table)contentcatrgory record count (?)12025-1-2cat-1 222025-1-1cat-2 132025-1-1cat-1

Hello! I have a field in every record for zip code (number field) I am looking to have a sheet to count up how many records I have in a specific zip code. Interfaces can give me a chart on the zip count but I can not figure out how to get a table of it. I can group by zip code in the table by I need that data in it’s own table.

I am trying to track when i should expect a project back based on the amount of days given to return. The first return length is 70 days or 10 weeks. The second time it is submitted is 30 days return. Column: Submitted date: (constant)- user entered Columns: Expected Project Return (date) DATEADD({SUBMITTED Date }, 10, 'week')) This checks out good.  when I have a Resubmittal column(date) I would like the Expected Project Return Date to now show DATEADD({SUBMITTED }, 30, 'day')) So in short i want the expected return date column to update once the resubmittal date is updated. 

I have and “hours” table whose rows consist of the following relevant fields:An email address of an event participantNumber of hours participation in an eventYear of the event participationThere will be separate rows with the same email address for each event where hours are recorded.I want to create a query (filter, group by,…) that answers this question.  Which people (email address) participated in at least one event in both 2022 and 2023.Following one suggestion, IGrouped the rows by email addressCreated a new field, called “yrs active” that is defined by the formula “arrayunique(year)”.  On the email address group by line, it indicates the number of unique values found so I assumed I could apply formulas or filtering that operated on just those unique values.Created a second field, “In 2022 and 2023” defined by the formula, AND(FIND("2022", {Yrs Active}), FIN

Hi all,I have some entries in my data table for multi-year contracts. I have one field that shows the number of months the contracts span (using the formula (DATETIME_DIFF({End}, {Start}, "days"))/30). I used days instead of months because if a contract ran from 1/1/24 to 12/31/25, it would count that as 11 months.I also need to know the total of months in every year of the contract (e.g. 12 months in 2024, 12 months in 2025, 6 months in 2026). Can anyone help?Thank you!

We have a form which pulls the initial data, but there are situations where we need more information. We want to put a button in the interface that connects to a different form, but the url address is dynamic, not static. Ideas on how to accomplish our goal: Make the url address static so it goes to the form each time.Viewer/commenter could openUse an automation button that populates the form and updates the record it was triggered from. Can someone with viewer/commenter access use this button?Create a template that will link to the record, but don't think this data will be able to be pulled for reporting which is not helpful long-term. Can someone with viewer/commenter access use this button?

Hello guys,I'm struggling to achieve the desired results here. First: I have a formula that return a number between 1-156 based on the created time of a record:(VALUE(DATETIME_FORMAT(CREATED_TIME(), '0.smh'))*(156-1))+0000 Although I want this formula to return an integer and not with decimal values.What to I need to change here? Thanks in advanced.