Formulas

Hello!I'm having issues with Airtable's default GMT timezone. I have a Date field (which I do not want to have time displayed for) as well as a formula field using the DATEADD formula to calculate the next time a task is due, based on frequency. In the formula field, I can specify timezone. In the Date field, I can't - without adding the time. Because of that, it's calculating incorrectly - everything is off by a day. Only when I add a time do the dates show up correctly. Is there any way to fix this? Do I need to add something to my DATEADD formula? Thank you!   

If I use an automation to update fields, does it update the fields in the order listed in the automation? So can I copy data from the existing fields first then update the fields with new data all in the same automation? If they update in order why can't I rearrange the order? just have to delete and reorder them all manually...

We have various sales/participation goals that change every year. My idea is to essentially store a unique formula for every goal, for every year, in a text field in a year table. Then in the main table, lookup that text field and somehow create a formula field based on that to do the actual calculation. Is that possible? Year Goal Name Criteria (single line text field) 2024 CP Sales Goal IF({CP Sales Rollup} >= 50000, 1, 0) 2025 CP Sales Goal IF({CP Sales Rollup} >= 60000, 1, 0)  Client Year (link) Criteria (lookup text field) CP Sales Rollup Points (formula) ABC 2024 IF({CP Sales Rollup} >= 50000, 1, 0) $35,500 {Criteria} should return “0” ABC 2025 IF({CP Sales Rollup} >= 60000, 1, 0) $75,750 {Criteria} should return “1”  In the Points formula field, how do I write the formula? I’ve tried just putting in a reference to the Criteria lookup field: {Criteria}, but that doesn’t work. I assume there has to be some conversion from text

Hi All, I try to fill a formula field with an email address based on another (email) field ( Source) using a nested formula:IF(Source = "Direct", "ulf@bayinvent.com" ,  IF(Source = "Oase" , "info@camperoase.de", "office@globalwingsreisen.de")Airtable accepts the formula but after saving, it reduces it to half of what it should do: IF(Source = "Direct", "ulf@bayinvent.com" ,  "Oase")What am I doing wrong here?must be a small mistake but I struggle to see it...Thanks for your help!

I want to create an automation that triggers different emails dependent on record conditions. Mainly status changes but also some with multiple conditions. Automation:Trigger - when a record matches conditions - if status not emptyActions - If status is ‘phone interview’ then send email ‘1’Otherwise If Status is any of Phone Interview, Forward to Site interviewand if Last Modified is 5 days ago then send email ‘2’Otherwise If Status is any of Phone Interview, Forward to Site interview and if Last Modified is 10 days ago then send email ‘3’Otherwise if If Status is Site interview set then send email 4Otherwise If Status is Site interview set and if Site interview date is tomorrow then send email 5Otherwise If Status is any of Forward to Trial, Completed interview and if Last Modified is 5 days ago send email 6Otherwise If Status is Site Trial and if Area is any of Motor Vehicle Maintenance and Repair (Light Vehicle, Parts), Accident Repair (Multiskilled, Paint, Panel, MET, VDA, Parts) a

Hi all!  New Airtable user here. I need help! i have a ongoing master list of every transactions each of my credit card makes.on another table i want to be able to track the outstanding balance of each credit card. i understand i need to use to rollup function, but i cant seem to figure it out!I would appreicate any help!Credit Card Dashboard (where the it shows outstanding balance for each card) Thank you.

Hi community! I’m trying to build a formula that generates a Pre-Processing Lot ID for each seed movement record. The logic depends on whether the movement is linked to a contract or not.1. When the seed movement is linked to a contractThe Lot ID should concatenate:{Final Initials (from Farmers)} {Program (from Contracts)} {Seed Variety (from Contracts)} The last two digits of {Crop Year (from Contracts)} {Contracts} (the contract number) The final Bin code (from {To Bin}) following these rules: If the seed is moved to one of these bins — HK 01, HK 02, HK 03, HK 48, HK 49 — the lot number should display the full Bin code (alphanumeric). Any “Bin ” text prefix should be removed. If the seed is moved to HPB1, HPB2, or GP WHPB, or the movement goes to a Cleaner (detected in {To Location}), the lot should take the Bin code from {From Bin} instead. Otherwise, it uses the Bin code from {To Bin}. Example (desired output):MCO-OH-FINOLA-25-070-022. When there is no contractThe Lot ID should

Hi! Trying to figure out how to make a field that turns these times into a 24hr format. I’ve tried 2 different time formats examples (01:00 PM - 7:30 PM) as well as (3-8 PM). I’m have a hard time figuring out a formula that will display that in a 24hr format? Any ideas? 

Hi there, I’ve been using Airtable for a while and using Pre-filled form for about 3 years now, but I’m having issue recently with some pre-filled form with linked records. I have 4 pre-filled linked record in this form : https://airtable.com/app3VaYZ9KK6HEimn/pag88B7PDY9L5izzt/form?prefill_Session=70&prefill_Formateur=Camille+Rabineau&prefill_Entreprise=Founders+PledgeAnd only 3 show up and the last one doesn’t. It does appears in the URL though, it’s just nor displaying it. Here is the formula I’m using : SUBSTITUTE(CONCATENATE("https://airtable.com/app3VaYZ9KK6HEimn/pag88B7PDY9L5izzt/form","?prefill_","Session","=",ENCODE_URL_COMPONENT({ID 2026}),"&prefill_","Formateur","=",ENCODE_URL_COMPONENT(Formateur),"&prefill_","Entreprise","=",ENCODE_URL_COMPONENT({Projet de formation}))," ", "+")I’m not too sure what’s wrong, Thank you so much for your help ! 🙏Céline

So I’m looking to employ a formula to provide a word count for my SEO keyphrases, which range from 2 to 8 words. Below is the formula that I thought would work, but I’ve had no such luck…does anyone have any thoughts as to what’s going on here? Thank you in advance! IF(({Keyphrase}=BLANK()),0,(LEN({Keyphrase}) - LEN(SUBSTITUTE({Keyphrase},’ ', ‘ ’))+1))

Hi everybody,Our company has 3 x sized boxes - small medium large.We fit the following quantities of our product in each  box:Small - 95Medium - 200Large - 300What I want to do is tell a user how many boxes each order will use. I’ve achieved this with nested IFs:IF({quantity} <= {Small Box (from Link To Products)}, "1 x Small Box", IF({quantity} <= {Medium Box (from Link To Products)}, "1 x Medium Box", IF({quantity} <= {Large Box (from Link To Products)}, "1 x Large Box",ROUND({quantity}/{Large Box (from Link To Products)},2)&" x Large Boxes")))This works well. My only issue is with the last statement when orders exceed 1 x Large Box. With an order larger than the size of 1 x Large Box, it returns a decimal of large boxes e.g. “2.67 x Large Boxes”. My question relates to the deicmal  - I now want my formula to say whether that residual decimal would fir into a small box, medium box or round up to another full large box.I’m used to nested IFs going in one direction, but no

I created a Check-In button that uses a formula, and it was working fine for a couple of weeks. However, it recently stopped pulling and autofilling the “Time Card” field. I also have staff use our interface to check in and log time for hours used on the time card.formula: "https://airtable.com/appLI7NsuW6OZb1VH/pag4DxG1LCOxskSQE/form"& "?prefill_Time%20Card=" & RECORD_ID()& "&prefill_Rental%20Date=" & DATETIME_FORMAT(TODAY(), "YYYY-MM-DD") 

I’m building a self-referencing task dependency system in Airtable. Each task can list its Predecessors, the tasks that must be completed before it,  and I want Airtable to automatically display the corresponding Successors, the tasks that depend on it, without any code or automation.In short, I’m creating a two-way view of task relationships so that linking one side (Predecessors) automatically reveals the other side (Successors). So in the image attached, “Conduct internal briefing meeting” should show up as a successor in “Project set-up”. If anyone can help this would be amazing!

Hi. Thank you in advance, I am wondering if there is a way to show numbers with thousand/comma separator if you combine it with a text in a formula field using concatenate. My Amount field is in currency format. Current Formula: CONCATENATE(Amount, " ", {asking-currency})Much appreciated.   

What is the calculation behind the Timeline Utilization summary?  I am trying to create a formula column that displays a resources utilization in the Resources table, to be used as a metric on a landing page.I tried ((({Task Count} * 8) * {% Commitment} ) / (40 * {% Availability)) , but it does not match what AirTable is showing on the Timeline view.  The task count is the number of tasks for the current day. Is there a way to simply access the calculated value? Thank you,

Am I overcomplicating this?I have an automation to create a time stamp in a Date Completed field when a Status single-select field is changed to Done. The Date Completed field is populated with the full date and time, but I need to utilize this information in a Reporting interface for my management so I’d like to be able to have them select the month from a Dropdown Filter. To that end, I’ve created Month Completed field that uses a simple formula to just show the 3-letter code for the month, based on the Date Completed field.The issue I’m having now is that I’m not able to use that 3-letter code (because it’s a formula field?) as a filter on the Dashboard.I could create another automation that’ll convert the formula field into plain text or even a single-select field, but I feel like I’ve now created a whole bunch of steps to do something that might be accomplished in a simpler method. Note that the Month Completed info isn’t being used anywhere else. The Date Completed will likely al

NEVERMIND - I filtered out the empty ratings. Thanks!

I have an Airtable that is populated from Jotform submissions. The Jotform has a configurable list that for someone to fill in their schedule for the week. The configurable list can’t be split in Jotform so I’m trying to split information in Airtable. I want all of the days of the week listed in the configurable list cell to be extracted in a Days Open column but can only figure out how to extract the first instance using regexextract.  For example the configurable list will come in as:Day Open: Monday, Opening Date: 2025-06-16, Closing Date: 2025-10-27, Start Time: 09:00 AM, End Time: 02:00 PMDay Open: Saturday, Opening Date: 2025-08-09, Closing Date: 2025-08-30, Start Time: 10:00 AM, End Time: 04:00 PM and I want Monday, Saturday to show up in the days they are open. 

Hi.We are using Airtable to display data about our system landscape. We want to share this data with our organization, and have therefore crated links and added these to our internal CMS system.If the link is shared to somebody outside our organization, the information should not be available. We tired to handle this with the “Access restricted to our email domain”, but found out that it requires that the use is an airtable user. If we don’t make any restrictions that link can be accessed by anyone how has it.In the perfect solution, we would like that only our Active directory users have access to the shared link. Is there any way to solve this?Thanks in advance.  

Hi there, I’m trying to put together an expenses claim form for my team including mileage claims. We previously uses Google API Distance Matrix on JotForm to calculate the distance an employee travels between two locations. I wondered whether there was a way I could add something like this to an Airtable form? The distance that came out between the two locations then calculated how much that employee is owed for the petrol used. Any advice would be appreciated. 

Hello Airtable Community,I’m receiving an array from a Fillout form via webhook into Make (see attached image)I would like to use this array in a Search Records module for Airtable to find all records where the primary field matches any of the values in the array.What’s the correct formula syntax in Make’s Airtable module to achieve this?Thanks in advance   

We’ve created an airtable base for submissions from teams, and then those need to be reviewed by assigned reviewers.  In the form, the review must select their own name. In another field, they need to select the project they are reviewing. I would like to filter the projects that are options to be just the ones the review is assigned to.  Is there a formula to say the “Assigned Review” field matches the name selected in the review name field?  As a work around, I was going to use filter by View -- but I’m also not seeing that option anymore. Was that moved? 

I am trying to set up an email automation that sends an email the next day at 9am after a status change. I have created a formula field: IF(  {Status Changed},  SET_TIMEZONE(    DATEADD(      DATETIME_PARSE(DATETIME_FORMAT({Status Changed}, 'YYYY-MM-DD') & "T09:00:00"),      1,      'days'    ),    'Europe/London'  ))The automation is triggered using a status condition and when the formula field is ‘today’. and This creates the correct time and date in the formula field, but it is not triggering the automation.  

I am looking to set up an automation based on when my date and time field are NOW Does not look like the automations date filter allows for NOW or takes formula code on it so how could I use a calculation field to look for any date that is now then tick a box or mark another field as True so that I can then base my automation filter off a column marked True? Not sure of the code that when time is NOW mark this fields as True etc…? Tried this but now working IF({Due Back Time Reminder} = NOW(), “True”, “False”)

I have this formula that is working: IF({Start date},DATETIME_FORMAT(DATEADD({Start date},14, 'days'),'MM/DD/YYYY'),"") I want to incorporate these conditions to it:IF(Category = "Playbooks",14, 'days')IF(Category = "How-to-Guides",3,’days')