Formulas

is there a formula to count the number of days between two date fields?

Hi All, I’ve got a formula that tells me when a job post is about to expire, when it is expiring today, and when it’s expired. I would like it to display an “Acvite” status for all other days, is this possible within this formula? Any help would be appreciated! IF(IS_AFTER(TODAY(), {Expiration Date}), “ :x: Expired”, IF(IS_SAME(TODAY(), {Expiration Date}), “ :firecracker: Expires Today”, IF(IS_AFTER(TODAY(), DATEADD({Expiration Date}, -3, ‘days’)), “ :raising_hand_man:t2: Expires Soon”))),

Greetings, I put together a video that illustrates what I am looking for.

Hi! I’m new to regular expressions and I want to create a formula in Airtable which switches the text left from the ‘-’ to the right and visa versa. So U27Y11/12-U13Y07/08 needs to become U13Y07/08-U27Y11/12 Basically swapping the right and left side. (the ‘-’ is always the separator) Can you guys help me out?

Hi everyone So I have this simple IF formula IF({Method}=“Digital”,1,0) Where {Method} is a multiple choice field, and can contain different type beside “Digital”. My problem is that this formula returns 1, only when {Method} is strictly equal “Digital” and nothing else. And I want it to return 1 if one of values is “Digital”. For example: Digital, Offset, Heatpress

Hi, I’m setting up a bookkeeping system for my business. What I need is that when I select the option “Expense” from the Single Select option in the Type field, I want to add a negative sign with the Received Amount field so that it is subtracted from the sum. I’m attaching a screenshot for clarity. I’d really appreciate it if someone can help me with the formula for this. Thanks a lot :slightly_smiling_face:

Hi, I’m trying to come up with a formula that extracts the first name, last name, and email that I’m getting into Airtable from another application. The format of the UID I’m receiving is shown in the couple of examples below: John_Smith_john@example.com Tracy_Jones_tracy@example.com Basically, it’s going to be first name underscore last name underscore email. I need to write three formulas, in my Table for the First Name, Last Name, and Email columns. I’m not sure how to proceed especially because the UID string length will be dynamic based on users’ names and emails, so I can’t use a LEFT or RIGHT formula to count and extract the text. I know there should be a way to do this, but could really use some help with the formulas. Thank you.

Hi! We’re trying to figure out what formula is to be set in place for this scenario: We have three forms, all connected to autopopulate on Airtable. Column A = Form A Column B = Form B Column C = Form C Basically, what we have a column with the formula that determines what form a client has filled out based on what Column has been autopopulated - let’s call it Column X. What we want to happen is = if Column A has been populated, Column X says “Form A”; if Column B is populated, Column X says “Form B” and so on. Only one column will be populated at a time per row (ex. Row 1 Column A populated, Row 2 Column B etc), so I hope that makes the formula creation easier. Thank you in advance!

Hi! Is it possible to use a formula or function to remove repeated words in a sentence?

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.

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

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 th

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”)