Formulas

My phone number field has rows with different formatting. e.g. 12824673737 and (510) 890-9699 How can I force all the phone numbers to be of one format: namely, a 10 digit number without any punctuation in between

I have a board for projects and one for tasks. Team members are pretty good about entering their project due dates on the project board, but we need to have due dates on the tasks as well. Id like the task due date to default to the project due date, with the ability for override. Ive created a lookup field on my tasks board to find the project due date (default date), but often the tasks need to be done before the project is due so i would like it if the field could be manually overwritten. I was thinking if i created another column in my task table with a formula that allowed team members to overwrite the lookup field this could work. It would say something like {lookup date} OR manual overrride.Is there a way to create something like this?

Help! I'm trying to get an estimated calorie count using workout time, weight, and intensity. My workout time field is a lookup field formatted as duration. The weight is 160, the duration is 39:00 and it is moderate intensity. The formula is returning 16,770 instead of 279.5.  What am I doing wrong?Original formula:IF({Workout Type} = "Primary", IF({Workout Intensity} = "Low-Intensity", {Primary Workout Duration (Minutes)} * (4 + (({My Current Weight} - 125) / (185 - 125)) * (6 - 4)), IF({Workout Intensity} = "Moderate-Intensity", {Primary Workout Duration (Minutes)} * (6 + (({My Current Weight} - 125) / (185 - 125)) * (8 - 6)), IF({Workout Type} = "High-Intensity", {Primary Workout Duration (Minutes)} * (8 + (({My Current Weight} - 125) / (185 - 125)) * (12 - 8)), 0))), 0)

I have a field that sometimes has a mentor's name in it, and other times (usually) is empty.I am showing that field in a timeline - which is fine when there's a name, but otherwise it shows up as "(empty)" which is annoyingI want to create a formula field that shows the value when there is a value (a name), but shows something innocuous - like an asterisk - when it's empty.I feel like this is an IF statement - but the value of the field is different all the time - Joe Schmoe, Jane Doe, etc.IF({Mentor (from Link to Forms)}, "", "*") is what I'm trying now, but it's giving me a blank where I want the name. What is formula for "the value of the field

Hi there- I have a list of around 200 e-mails that I would like to search for in my table, so the “filter” function is not going to work here. Is there a simple workaround that will allow me to create a grid view of just these 200 e-mails, from a list of thousands? Thanks!

My challenge is to do a search that looks for records where my input is part of the company name. So essentially I am looking for a "contains" function.Example searches:1.Input: "sephora"Target: "company" field in record: "SEPHORA Inc."2.Input: "GoSports"Target: "Go Sports Inc."I guess the second example won't be possible but my plan is to just get all the matches based on company name, then name of the record and description and then let AI do a matching by probability and my other form inputs.I am using the search module in Make.com.My guess was to use either "SEARCH", "FIND", or "REGEX_MATCH" but no luck so far with my tests.

Hello, I have a lot of text in a cell LONG TEXT (I extract a little bit of this), I need to create a new Formula column to extra only the telephone number, what can i do? Buonasera, Chiedevo disponibilità per parziale permuta con mia autovettura MASERATI 4200 SPYDER del 2002 ASI - TARGA ORO 37.000 Km circa colore Argento scuro capotte e interni BLU. Macchina sempre tagliandata da rete ufficiale kilometri REALI io sono il 3 proprietario. La settimana scorsa ho inserito annuncio su AUTOSCOUT 24 .. chiamano in tanti ma tutti senza soldi.. Cordiali salutiRispondi ora:MARCO PxxxxNI E-Mail: praxxxxxx@gmail.comTel: 339xxxxx00===Veicolo===========================================

Hi there, I have used the following formula based on a guide, however, I would like to use standard fiscal years/quarters and this formula is not accurate somewhere. For instance, I have a date set in my table as 11/9/20 and it displays the quarter and fiscal year s Q1 FY’21 - the formula is below: IF(OR( FIND(01,DATETIME_FORMAT({Date},‘MM’)), FIND(02,DATETIME_FORMAT({Date},‘MM’)), FIND(03,DATETIME_FORMAT({Date},‘MM’))),“Q1”, IF(OR( FIND(04,DATETIME_FORMAT({Date},‘MM’)), FIND(05,DATETIME_FORMAT({Date},‘MM’)), FIND(06,DATETIME_FORMAT({Date},‘MM’))),“Q2”, IF(OR( FIND(07,DATETIME_FORMAT({Date},‘MM’)), FIND(08,DATETIME_FORMAT({Date},‘MM’)), FIND(09,DATETIME_FORMAT({Date},‘MM’))),“Q3”, IF(OR( FIND(10,DATETIME_FORMAT({Date},‘MM’)), FIND(11,DATETIME_FORMAT({Date},‘MM’)), FIND(12,DATETIME_FORMAT({Date},‘MM’))),“Q4”)))) & IF(AND(VALUE(DATETIME_FORMAT({Date},‘M’))>=6, VALUE(DATETIME

Hi everyone,I desperately need help as this formula is driving me crazy! Basically the formula works except for Jan - Feb and March 25 and shows them as Q2 24 instead of Q$ 24. This is the fiscal breakdownQ1: april24,may24, juen24 ->  Fiscal year (e.g., for May 24, Q1 FY’24)Q2: July24, August24, September24 → Fiscal Year (e.g., for July 2024, Q2 FY'24)Q3: October24, November24, December24 → Fiscal Year (e.g., for October 2024, Q3 FY'24)Q4: January25, February25, March25 → Fiscal Year (e.g., for January 2025, Q4 FY'24) Here is the formula:"Q" & ROUNDUP(  (MOD(MONTH({Month})

Hello Airtable community! I need your precious help as I’ve been spending the last 2 days trying to figure this out and can’t seem to do what I want done here… In one table I have a field name “Country” and another field right next it that’s named “States”. I am trying to show a selected number of options in the “States” field based on what has been selected in the “Country” field. e.g: I choose “USA” between all other countries available in the (single-select!) field “Country” and I want the field “States” to return ONLY the results corresponding to States with the USA. So, depending on what’s selected in field “Country”, it needs to show only a few selected options in the field right next to it. If I choose “USA”, I want the column next it to give me the 50 states (Choosing between California, Nevada, etc) If I choose “CANADA”, I want the column next it to give me the canadian states only! etc… I have tried creating a different table with all i

Hi there, I'm trying to bring a value from a previous row into another row, matching on two conditions.For context, I am ultimately trying to compare the difference in revenue between two rows, submitted by the same company, in adjacent quarters.Does anybody know how this could be done? Thank you!

Hey Gang.I know there's probably multiple ways to do this but I'll ask.If there's an automation way of stripping/grabbing two(as there will always be two) linked records in a field and placing the first in the "first instructor) field(email type), and placing the second in the "second Intructor" field(also an email type).Obviously, I'd need to grab, each of those and place them in their respective field.Simple enough, right?Or if just placing a formula in a field to reflect those emails, that'd be fine too, I guess. thanks

I am new to airtable. I used this  Standard Deviation STDEV - Quick Reference - Airtable Community to calculated the standard deviation. However I have 5 fields in this table and sometimes only 3 or 4 values are entered. I would like to only consider the fields that have a value entered. for n I added a field called number of runs, which helped, but it still considers the fields that are empty and includes them as the value "0". How can we fix this? 

Hi All, I am trying to create a button that one clicked will launch a web page that uses an internal url (or external) as the prefix with a cell value as the suffix. For Example: https://trsau.reecenet.org/pos/other-stock-on-hand/products/12345 The first part (https://trsau.reecenet.org/pos/other-stock-on-hand/products/) will be common in the formula with (12345) being the part that changes. To achieve this i have been try to us the following formula: The issue is that once pressed the i get the following error in the address bar.

First post, so I am sorry if this is not clear as to what I am trying to do.  My goal is to have a formula in a field determine if 3 different fields are either blank or not blank.  Depending on whether they are blank or not return a message to that formula field outlining what is missing and required.   See picture below.  I have tried to nest IF functions using blank or not blank, but I keep getting errors.  I have also tried to use the AND and OR functions in combination with the IF function with no luck.   A formula would work best for us.   Any ideas would be greatly appreciatedthanks Bob 

Hi there, I am trying to calculate employee hours worked over 40 within the span of a worked week (Sunday - Saturday) for overtime calculations, and can't figure out what the formula would be for this. I have start dates and times on my sheet already.

I am trying to come up with a calculation of the parts I need to batch-produce a product.I currently Have tables for: Products, Product Variations, Parts, and Production Plan.For example, If I need to make the product "Tea Light", this comes in small, medium, and large sizes, two different patterns, and two different wood types. These are in the Product Variations table.Each tea light has the following parts: one bottom piece, two different top pieces, and then eight sides. These are in the Parts table.In the Production Plan table, the "Quantity to Produce" field should take into account the current stock of that product which is in the "Product Variations" table.I've used ChatGPT to get 90% of the way there. But for some reason, it can't figure out the formula needed to calculate the numbers of each part to produce given the quantity of product needed.Hopefully, that makes sense. Thanks!

Very new to Airtable and having some fun with formulas.I have project records with a simple status field (Planned, In Progress, Completed) and some basic date fields.I created a formula that checks if the project is "On time" or "Overdue" based on the completed (planned or actually completed) date field vs today.I don't need this formula to calculate on Completed projects so i'm looking for a formula that sets the field to blank if status is Completed, but calculates if Planned or In Progress.Current checking formula:IF({Completed Date} < NOW(), "Overdue", IF(IS_SAME({Completed Date}, TODAY(), 'day'), 

Hi Airtable Community,  The problem is to check in Airtable whether a value in a column (e.g. column 2) occurs **for the first time** or whether it is a duplicate. If the value is the **first occurrence**, a value from another column (e.g. column 4) should be displayed in another column (e.g. column 3). However, if the value is a duplicate (i.e. it already occurs in column 2 in an earlier row), column 4 should remain empty.Happy for any ideas to solve this 🙂  Thanks & bestMirko

Hi guys....I have two columns "Old Price" and "New Price".I want to achieve something like this:When "New Price" updates, then the previous "New Price" before the update becomes the "Old Price".Here (below) is how the code I want to achieve look like in programming.Var = Old PriceVar = Prev New Price = 0IF(New Price updates)Old Price == Prev New PricePrev New Price == New Price 

Hey everyone!I’m running into a challenge while using Airtable in conjunction with Fillout forms. When using the duration field in Fillout, it only provides the exact times (start and end) rather than a length of time in between. To simplify things, I’ve limited entries to a start time and end time, assuming the event will occur on the same day.Here’s what I’m trying to do:•I’d like to create a formula in Airtable that takes the start date (provided in a date field) and combines it with the end time (from the duration field, which doesn’t have an attached date).•The goal is to format the end time as part of the same day as the start date.For example:• Start Date: 2025-01-14 08:00 AM•End Time: 10:30 AM

I am wondering what Record comments can be used for. It looks like Record comments aren't accessible by formulas, automations interfaces, etc. That is, I can't create a formula that tells me if there are comments, that returns, the comments, etc. I can't rollup all comments of a record. I can't write an automation that emails me when a comment is made in a record.Is that all true — are comments really this isolated? If so, I wonder why the heck they exist — nothing in airtable seems this isolated. 

Hey everybody,I am trying to match 2 columns to see if a name from column 2 appears in column 1 (list of names). The answer I need is simply true or false.Column 1 is a lookup field with a name.Example: “Smith, Jack”.And column 2 is a list of names. Example:“Green, Mike”, “Smith, Jack”.But neither with Find nor Search do I get a match. My formulas are:Column 1:Column 2:Formula:FIND({Mitarbeiter (from Urlaub bestätigt 3)},{Kolonnen Zusammensetzung}) Maybe you have an idea, whats wrong here. Best greetings

Hello, I am stuck on something that seems simple, and I've tried navigating the answer here, but still can't get my formula to work.I have two fields I'm working with, a check-in field, which is a dd/mm/yyyy field, and a check-out which is also a dd/mm/yyyy field.  I don't always use both fields, though - sometimes one is blank, and sometimes both are blank.I'm trying to calculate the # of nights between the two dates, and the formula is working fine, but returns "NaN" when there is no date.  How can I correct the formula to simply leave that field empty if there are no dates? Also, it seems to be wanting to calculate a sum, which I don't want it to do.  Not sure why the Sum and Infinity symbols? Here is my formula: DATETIME_DIFF({Check-out}, {Check-in}, "days")Thank you!

I have a table with all our fiscal weeks, and their corresponding start and end dates.  ("Fiscal Weeks")I have another table with projects, and the start date of each project. ("Projects")I need to add a column to the Projects table that tells me which fiscal week the project belongs to, based on the start date of the project. Nothing I've tried seems to work. Any thoughts?