Formulas

Hi there! I have an AirTable sheet where I am collecting data from a form colleagues use. One field in this form is monetary values, but I’ve switch the receiving column to a text format because users don’t always enter integers only and I have no way to constrain the field in the form. Values can be ‘$100’, ‘100USD’, ‘100 USD’ when what I need is ‘100’. So what I’d like to do is still receive the data in text format column that accepts all entries, and then grag this data, cleanse it, and keep only the numbers, to have an end result that’s an integer. So ‘100USD’ would become ‘100’. I’ve looked at formula documentation but I can’t figure out what to do, would anyone have an idea that could help me please? Thanks!

Hi there. I’m a newbie to Airtable formulas I have a field {DOW} which holds a value like Thursday. I also have values in a different table {Start Date} and {End Date} based on a foreign key. How do I obtain a list of all the dates between {Start Date} and {End Date} that is the given {DOW}. Eg Start Date is 28/Jan/2022 and End Date is 8/Apr/2022 and DOW is Thursday. I want to find all the Thursdays between the given dates. The list may be stored in 1 field (preferred) or have 1 date stored per field (hence having 10 columns for the 10 Thursdays found in the date period). Thank you.

PROBLEM In our TABLE 1 (PROJECTS): We want our concatenated field to read: P1234 (Joe Smith - Smith, Joseph & Jones) What we get: P1234 (“Joe Smith - Smith, Joseph & Jones”) Where: Joe Smith - Smith, Joseph & Jones is a linked field to our TABLE 2 (CUSTOMERS) table. WHY: Airtable is concatenating a linked field that contains one or more commas, and for whatever reason surrounds the entire string with quote marks. You should see that any concatenated fields containing linked fields without commas do not show the quote marks. EXAMPLE DATA / FORMULAS (Solution Below): This example shows Table 1 concatenating Table 2’s primary field (which is also a concatenated formula). Even if your primary field in Table 2 is a write-in field containing one or more commas, the result & solution are the same. TABLE 1: PROJECTS Column 1: Project (formula field: Concatenate({Project Number}, " (", {Customer}, ")") This is the field that will show the unwanted quote marks beca

Goal: take Rollup results, and summarize the unique values with a count of each unique value. For example, instead of showing [10, 10, 10, 2, 2, 1, 1, 1, 1, 0.5], I want to show [3 x 10, 2 x 2, 4 x 1, 0.5]. Or, if needed, I can show [3 x 10, 2 x 2, 4 x 1, 1 x 0.5]. Or, similarly, instead of showing [carrot, carrot, carrot, potato, potato, onion], I want to show [3 x carrot, 2 x potato, onion]. The ARRAYJOIN seems to be working as designed so far, but I’m hoping to summarize the results in a way that is a bit more user-friendly. I suspect there is some way of using the ARRAYUNIQUE function to help with this, but I haven’t found a solution. PS: this is my first post here, so feel free to let me know if I’m doing anything wrong and I’ll work to correct it. [edits: spelling correction; new example and explanation]

How do I find the record with the maximum entry in a certain field. Note, I do not want the maximum NUMBER in this field, rather the record it corresponds to. For more info: I have a list of names, with how many tests they conducted. I want to find who has done the most tests. However, doing this once isn’t enough. I want to be able to replicate this given certain conditions. For example, of all the people with a specific bank (I have a field for this too), who has completed the most tests? I’m more than happy to provide more info if needed,

Hi, I need to insert data in a column of table 2 in table 1, automatically, if there is the same value (text type) as the NAME of table 1 in table 2. I’ve tried some formulas and I couldn’t. Please help me. Attached sample images! Thanks!

I extract only year from date field with DATETIME_FORMAT(Date, 'YYYY') and assign it to new field. If I order by that field it isn’t recognized as number, but as a string (the option is A-Z or Z-A). How can I change it to a number?

Hi AirTable Community, I am trying to create an IF formula to format a number field to an abbreviated format depending on if it is 100,000 or 1,000,000… eg. “780000” becomes “780k” “1000000” becomes “1M” Purpose is because I am trying to Concat the abbreviated number together with other fields. Does anyone know how to write such a nested IF statement? Thankyou in advance!!

I have a marriage date for some people and no date for others. I created a field called THIS YEARS ANNIVERSARY as shown below. IF({Marriage Date},DATETIME_PARSE( DATETIME_FORMAT( {Marriage Date}, ‘MMMM D’ )& ’ '& YEAR( TODAY() ), ‘MMM D YYYY’ )) I need a formula for NEXT year’s anniversary. If THIS YEARS ANNIVERSARY was a simple date field, I know how to use the DATE_Add formula but I am struggling with how to use the DATE Add formula to this formula field so it is also seen as a date so I can use it as a date field in a calendar. Any help would be appreciated.

I have a measurement form. Users can add numbers into the appropriate fields (labeled number only fields). I think have a formula field that formats the numbers into something that can be readable. Each field is added on its own line in a formula field with a * by it, so the end result looks like this (sample formula below): Height: Bust: Waist: Thigh: CONCATENATE( IF({Height}, "* Height: " & {Height} & "cm\n"), IF({Head Circumference}, "* Head Circumference: " & {Head Circumference} & "cm\n"), IF({Neck Circumference}, "* Neck Circumference: " & {Neck Circumference} & "cm\n"), IF({Chest Circumference}, "* Chest Circumference: " & {Chest Circumference} & "cm\n"), This works great. However I have an “Additional Measurements” field that is a long text field, where a user can add any missing measurements. For example: Calf Circumference: Hip Circumference: I am wanting to know if I can add a * to each new line like in the other formula? The above user in

Hello Everyone, Maybe somebody form the community will help with something I’m struggling with. I’d like to make an API call with a Filter formula on a lookup field, which includes comme separated dates, see below : As you can see, there is a simple calculation field that is : IS_BEFORE(TODAY(),Booked_Dates) that doesn’t seem to be very happy with the multiple item field on Line 1. However, when I’m filtering lookup fields with conditions in Airtable it seems to work perfectly fine (don’t notice both conditions, only the second one): And the result : So my question is : Is there a formula I can use to have the same behavior than Airtable’s lookup filter so I can make the appropriate API call in the FilterByformula query ? Or is there any workaround that one of you might suggest ? The idea here is to look for an available timeframe in a booking system… Thank you for your help. Best, D

I am making an automatically repopulating task list. I can’t use the Workday function because we work on Saturdays and have a non-standard holiday schedule. The formula I am using checks a field which adds days based on a daily, weekly, monthly assignment to the old due date. I have a formula that successfully checks that new date against the holiday string rollup, but it can only add 1 date at a time- so if there are multiple holidays in a row, it doesn’t recognize that. Is there a way to make this formula keep checking the Date Calculation 1 sequentially against my rollup of days to skip until it reaches a workday again? IF(FIND({Date Calculation 1 String}, {Skip String Rollup}&""),(DATEADD({Date Calculation 1}, 1, ‘day’)), {Date Calculation 1}) I’m guessing I need to change the Dateadd value of 1 day, but I’m not sure how. Any help is appreciated.

Hola, tengo un campo que indica el numero de vencimientos. ¿Como crear en otra tabla tantos registros como es el numero de venciimiento? O sea si tengo 3 vencimientos que cree en otra tabla tres registro para gestionar el pago. Gracias

Hello. I have a table that it is filled using a forms. there are two fields coming from two other tables that are selected by the customer. I need a formula column where it analyzes the two filled fields and returns the result (remuneration) based on a third table (remuneration_table). below I am attaching a photo to illustrate. basically I need the formula to return the candidate’s remuneration based on the sector and position he will be hired. Could someone help me?

Hi! I would like to set-up a formula where if Column A is not blank, Column B says “8.0”, and if Column A is blank, Columb B will say “7.0” Is there a formula to do this? Thank you!

I have a date formula to work out the number of days between 2 dates, if either or both of the dates are blank, it returns NaN, how can I amend the formula to only perform the calculation if both fields are populated? (Long overdue a lay-in and my brain is not functioning by itself, need help… :slightly_smiling_face: ) DATETIME_DIFF({Finished},{Started},“days”)

Hi I have a lookup field that is displaying values from a multiple select field, which I have converted into a comma-separated format. Let’s say the values in that field (field A) are: Cat, Dog, Antelope, Lion I have another field (field B) that has the possibility of having at least some of the same values as the first field. It might look like: Lizard, Elephant, Cat, Eagle The order of the variables is, well, variable because the user may enter them in whatever order they wish. What I need to do is evaluate whether any of the terms in field A match any of the terms in field B. In this case, it would return true because ‘Cat’ appears in both. Thanks!

Is it possible to sort records numerically in a roll up field?

Hi, I am trying to generate a “Date” based on the month input available in my airtable. I tried using the If function, however, the result is not being recognised as a date. A little more information on my use case. I’m creating a database from a form that asks people to enter their preferred month, these months are provided as dropdowns. So, for example, if my table has October as a Month - I want to be able to create an additional column that tells me the first date of the months, i.e October 1, 2021, should be the value. Any ways to do this?

I’m working on a table where I have different currencies (in separate columns) and need to convert them all to one currency. I have a column marking the currency used and so the conditional if statement uses this to have a different calculation for each statement. However, for some reason, the formula doesn’t work but it does not return an error, it simply returns a blank Cell, so I have no idea what I’m doing wrong. Please HELP. IF({Currency} = “MXN”, {Amount in MXN}/{FX}, IF({Currency} = “EUR”, {Amount in EUR}*{FX}, IF({Currency} = “USD”,{Amount in USD}))) I know about the Currency converter app, but due to the Terms and conditions/privacy settings, I can’t use it.

Its probably a simple formula…but here goes… I have a record in airtable that looks like this: The A, B, C represent the responses to survey What I want to do is COUNT the number of each letter - so for example A = 3 B = 1 C = 2 E = 1 I have got as far as: IF({Col1}=‘A’,1,0) and that returns the 1 (in this example) What does the formula look like to check in Col2 - Col7 contains ‘A’ and then give me the total of 3? I hope that explains what I am trying to do clearly :grimacing: :nerd_face: Thanks in advance.

Consulta: Si tengo un campo donde ingreso la fecha de nacimiento de una persona, cómo debo formular otro campo para que me devuelva la edad de esa persona y que se actualice automáticamente cada vez que consulte la tabla. Gracias a todos!

Hi, Is there in Airtable a (cumulative) normal distribution formula or work arround besides writing the full formula ? ie : Excel norm.dist function equivalant with cumulative option set as true. Thanks

I have 21 lookup fields that are used by different team members, which may or may not be filled in for a given Airtable record (i.e., one record might have lookup items tagged in 3 of the fields, another record 5 of the fields, yet another just 1, and so on). I’d like to summarize, with a formula field, all of the lookup items tagged for each record across these 21 fields. When I just use an & operator, I’ll end up with something like this, if the record has items tagged in more than one of these 21 fields: Tag 1A, Tag 1BTag2A, Tag 2B, Tag 2CTag 3A …so I want to add a comma and space separator between the tags from each field, but only when values are actually present in any of the fields so as to not have redundant commas. I tried doing something like this: {Field 1} & IF(AND({Field 1}, OR({Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}, {Field 7}, {Field 8}, {Field 9}, {Field 10}, {Field 11}, {Field 12}, {Field 13}, {Field 14}, {Field 15}, {Field 16}, {Field 17}, {Field

Hi community, I need your help. There is something i don’t understand with the formula bellow : IF( SEARCH("Jungle", {Order Line Items}), "Jungle", IF( SEARCH("Baby Plantes", {Order Line Items}), "Baby plante", IF( SEARCH("Pets Friendly", {Order Line Items}), "Pet's Friendly" ) ) ) I want to write a 4th condition but it never work. What is the importance of the space between the condition and the “)” ? How can i built easly this type of formula ? Thank’s a lot, Matthieu