Formulas

Hi all - I’m relatively new to Airtable, so I apologize if this question has a simple fix. I’m creating a social media calendar and I have one campaign that has promotions in various channels. For example, this one promotion will be featured in a FB post, an Instagram story, Linkedin, and Twitter. I’m trying to see if there is a way to display the campaign on one line in Airtable with multiple selects to show where it will be featured and the number of placements that correspond with each channel. (2 FB posts, 1 Instagram story, 1 linkedin post). Based on what I’m seeing, I don’t see a way of doing this. I can obviously do a multiple select field with the different channels, but I can’t break out the individual number of posts on one line. I’m trying to avoid not having to list the promotion multiple times to show the number of placements for each channel. Any help is appreciated. Thanks!

I am hoping somebody can point me in the right direction. I think REGEX_EXTRACT and SUBSTITUTE are the right way to go here. I have titles that all have the word “BPM” followed by a space towards the end. I want to extract everything to the right of "BPM " (and the space). Examples of the titles I am working with: DAM301_203 PULSE WITH SYNTH 80 BPM A FLAT what I’d like to extract is “A FLAT” DAM100_001 PULSE DEEP END 105 BPM F SHARP what I’d like to extract is “F SHARP” DAM201_400 PULSE TICKING CLOCK 125 BPM C what I’d like to extract is “C” So I am thinking I need to “substitute/extract” everything up to and including "BMP ", and I should end up with what I’d like to keep, regardless of length. Is my thinking correct? If yes, how would I do that? I have not managed to come up with a regular expression that captures what I"d like to capture. Thank you so much in advance.

Here is the formula I’m currently working with: AND( IF( DATETIME_DIFF(TODAY(),{Date Status was Modified},‘days’) > 30), IF({Status} = “Open”)), “No” I’m trying to say if the {status} field contains “Closed” AND if the {Date Status was Modified} is more than 30 days ago, then populate the formula field with “No”. I think I’m close, but I can’t get it to work. Any help would be greatly appreciated. Thanks!

I like to create a “whatsapp” link based on the following I have a column called “Phone” it used the US numbering phone # (555) 555 - 5555 i want to create a whatsapp link in which the phone number is stripped of its formatting and adding the digit 1. In addition to create the whatsapp link will need: WhatsApp.com Open WhatsApp WhatsApp Messenger: More than 2 billion people in over 180 countries use WhatsApp to stay in touch with friends and family, anytime and anywhere. WhatsApp is free and offers simple, secure, reliable messaging and calling, available on phones all over... So in this case it will be https://wa.me/15555555555 This data in this whastapp column will be automatically added based on the phone number entry.

Is there a simple way (in free version)?

I sent this as a feature request to Airtable support, but I thought I’d post here too. Perhaps there are other workarounds. The workday_diff function doesn’t handle a blank value for the “holidays” argument. Currently a blank value causes an “#ERROR” Use case: I’m using the workday_diff function to calculate my working days in a month. Here is a screenshot of a simple example of this: Since not all months have days off, the function returns an error for those months. My work around is to wrap the WORKDAY_DIFF in a IF: IF({Days Off}, WORKDAY_DIFF(Start, End, {Days Off}), WORKDAY_DIFF(Start, End)) It would be best if the function just handled a blank holidays argument itself. Perhaps someone has found a better solution than the IF wrapper?

I am not sure if this is even possible - I have a table that has 3 name fields - {Name 1}, {Name 2} and {Name 3}. A formula field {All Names} combines the content of all 3 name fields separated by commas. I am using this formula: IF({Name 3}, ({Name 1} & ', ’ & {Name 2} & ', ’ & {Name 3}), IF({Name 2}, ({Name 1} & ', ’ & {Name 2}), IF({Name 1},{Name 1} ))) Is there a way to have the name “ENDING” always be at the end of the string, regardless of the name field it was entered in? So in my example, the second row would read: “Michael, ENDING”. If this can’t be done in one formula, maybe a second formula field can re-arrange the order. The name “ENDING” will be consistent across the table. I can of course make sure that “ENDING” is always the last name to appear in any of the 3 name fields. What I’d like to do is make sure that it is indeed always the final entry in the combined string, regardless of what users enter where. Thank you so much for pointing me in the

I was receiving an error on all fields when trying to use REGEX on a lookup field. Using CONCATENATE () solved this issue. It doesn’t seem far-fetched to me to default or at least allow a formatting option or auto format lookup fields so this isn’t necessary.

In my example, the fields “Name” and “Code” should start with the exact same 10 digits. I’d like to create a formula that compares the first 10 digits of both fields and displays a message if they match. Since the content of the “Name” field gets entered independently from the “Code” field, I’d like to use this formula as a way to check for errors. I started with the following formula: IF( AND( REGEX_MATCH(Name,’\w{10}\b’), REGEX_MATCH(Code,’\w{10}\b’) ), ‘Codes Match!’,’’) I am realizing now, this formula only looks for 10 digits, regardless of content. Not good. How do I specify that the first 10 digits of both fields have to be identical to display a positive result? Is using LEFT(Name,10) and LEFT(Code,10) the right way to go? If yes, how would I work them into a formula? I can’t seem to figure that out. Thank you in advance!

I currently have the following formula running since January that I want to be active for all bookings up to up the end of Mar 2021. {Extrachildren} * MIN(({Weeks FM/Zoom}+{WeeksSR}), 10) * 5 For April until June I want this formula to be active {Extrachildren} * MIN(({Weeks FM/Zoom}+{WeeksSR}), 11) * 3 Can somebody tell me how I achieve this.

Hi, Looking to use the FIND function for a column and return a result in Zapier. My column ‘Name’ contains an entry 'Data’ I want to return a result, even when a partial match exists for 'the data’ I’ve got FIND("the data", LOWER({Name}) & "") but this returns nothing. Is there something along the lines of an ‘If Contains’ formula that I can apply to Zapier via an Airtable formula? I appreciate that this might mean entries with “the” would be considered e.g. ‘the number’ but thought there must be a way to find the closest match?

Looking for a way to quickly find the last record. I can do this in a view with a filter, just not sure of a formula for the newest record. Really what I am looking for is a way to find a record older than 60 minutes. This is a dispatch log, when there is no communication between a security officer and dispatch within the last hour. Dispatch is required to radio the officer and make contact to be sure everything is alright. Or any idea to show the last record, or only show if it is older than one hour. There are many cars patrolling, and a ton of communications. This makes it difficult to find the last record for all patrol officers. Looking for any ideas.

Hi everyone! Looking to create a formula that will show the status of requesting copy. I’d like it to cover the 3 fields I have (all checkboxes) so when a team is done providing a component of copy, they check the box and the status appears. Field 1: Copy Requested Checkbox Field 2: Ready for QA Checkbox Field 3: QA Approved Checkbox Hoping the formula can mirror this: IF Copy Requested is checked then the Status is Copy Requested IF Ready For QA is checked then the Status is Ready for QA IF QA Approved is checked then the Status is QA Approved I have it figured out with just one field in the formula, but to add multiple is where I’m getting stuck. Thank you in advance!

I thought this was simple, but for some reason, it’s not working, no matter what I try. I am trying to set up a formula field that looks at three specific fields in my table and if these three fields are populated and not empty, the formula field should fill in the word “Tagged”. If one or more of the three fields are empty, the formula field should stay empty as well. What am I doing wrong here? IF( AND( {WRITER(S)} !=BLANK(), {PUBLISHERS} !=BLANK(), {GENRE + SUB GENRE} !=BLANK() ), ‘Tagged ‘,’’) Thank you so much in advance.

Is there a formula for checking a image file size? Using a IF statement, when a image size is over 5MB (I put 4MB for the sake for example), then the formula will return as a text “ERROR 5”. I tried something like IF(LEN(Image) > 5, "ERROR 5") to no avail.

Hi all! I’m looking to create a formula along the lines of: If Field(Dropbox Link) is Empty and Field(Review Link) is NOT Empty THEN mark the status as “ :x: Incomplete :x: ” If Field(Dropbox Link) is NOT Empty and Field(Review Link) is NOT Empty THEN mark the status as " :white_check_mark: COMPLETE :white_check_mark: " If Field(Dropbox Link) is NOT Empty and Field(Review Link) is Empty THEN mark the status as :white_check_mark: COMPLETE :white_check_mark: " If Field(Dropbox Link) is Empty and Field(Review Link) is Empty THEN mark the status as “ :x: Incomplete:x:” I’ve tried IF({Dropbox Link} =BLANK(), “ :x: Incomplete​:x:” ) which works, but when adding anything onto that, I hit a wall! Would really appreciate some clarity on this if possible!

Is there any way to shorten this formula?

Hi All! I’ll try to describe my situation. I’ve got 3 tables: Team Members, Project and Roles. Each role is linked to a project and a team member. Each role contains, as information, a start date, an end date and the number of hours / day. For example, Tommaso will be lead developer on project one from 01/03/2021 to 12/03/2021 with a with a commitment of 4 hours/day. Tommaso will also be Sw Architect on project Two from 1/02/2021 to 4/03/2021, with a commitment of 3 hours/day. Here’s the question: is there a way to show the daily workload on a calendar view or a gantt view? For each day I would like to see the sum of the H / days on the various projects, for each team member. Something like that (even without colors). I hope I have explained Thank you all!

Hello everyone, Unfortunately, I’ve a problem when I CONCATENATE(Montant HTVA) As you can see, I have decimals that appears from nowhere :frowning: I need to convert my prices into string to be able to generate an invoice that include all my items lines. How can I solve that problem?

Being no formula expert, I am assuming that the new REGEX_EXTRACT formula function would be a good way to achieve the following. I want to extract three digits from a name field. These three digits are always in the same place after an underscore. Name Field: DAM301_010 POWERHOUSE Extraction Formula Field: 010 Name Field: DAM204_115 DROP THE BALL Extraction Formula Field: 115 I apologize if this seems extremely simple and obvious to the pros, but for a formula layman, it’s anything but. :winking_face: Thank you so much in advance.

Hi all, I’m facing the following challenge: I have a table with a list of Product items (Catalog), to simplify the question I have 3 columns: Product name | Product ID | Sizes e.g. Red Tshirt | 14828 | S,M,L or Blue boots | 5423 | 8,9,10,11,12 I need to take a single row and duplicate it & split it per each size as the following: This original row: Red Tshirt | 14828 | S,M,L Should become this: Red Tshirt | 14828 | S Red Tshirt | 14828 | M Red Tshirt | 14828 | L (So all the other columns have the same values) Has any 1 ever encountered something like this? Thanks :slightly_smiling_face: YV

In the primary field, I’m trying to create an ID Code for my ancestors and I need some help. An example ID Code is: First three letters of surname, first three letters of name, year of birth eg Denis O’Brien born in 1836 = ObrDen1836 However, I have two issues with this example as the formula has created O’BDen1836 How do I remove the apostrophy in the surname and change the capital B to a lower case ‘b’ to change O’BDen1836 to ObrDen1836? Is this even possible? My current formula is: LEFT({Surnames}, 3)& LEFT({Name}, 3)& YOB I’ve attached a screenshot for reference also. Thank you so much, your help is very much appreciated - and may well save my sanity! :slightly_smiling_face:

Hi ! I got a beginning date : 01/01/2010 I got an ending date : 31/12/2014 In a field, I would like to show : 2010, 2011, 2012, 2013 (the dates included between beginning and end) Any clue on the formula I should use ??? I’ve investigate for hours with no answer… :frowning: A big thank you in advance Alex

Hi everyone, I’m unable to get a DATETIME_DIFF formula to work the way I want it to. Basically, I have a Status field as a single select as follows: My goal is to have a column that shows me the number of Days since a record was created. When I use the DATETIME_DIFF by itself, it works just fine as below, showing 0 days However, what I want to do is to show this only when the Status column is not equal to complete. So I’m trying to wrap my DATETIME_DIFF as the true result when the Status != “Complete”. But this doesn’t work. When I set the status to complete, I get the false result of the IF formula, which is “Complete”, so that part works fine. But when I change the status to anything other than “Complete”, the formula shows me a blank. I can’t figure out why this isn’t working. I tried swapping the IF formula for a SWITCH, but I have the same issue. The DATETIME_DIFF doesn’t show a result. Edit #1: I forgot to mention that I’ve also set a (native Airtable) automation such that whe

Hi, Sorry if this is a basic question, but I just can’t seem to figure it out. I’m trying to get a count of all the records within the same table. So for e.g. say I have two columns in a table as depicted below. I’m trying to have the “Count of all Tasks” column be dynamic so that when one more record is added, the number changes to 5 and so on. The reason is that I need to lookup this total count number into another table. However, I’m not sure how to proceed. I can’t seem to use a Rollup or a Count type for the “Count of all Tasks” column because they need me to refer to another table. In essence, I’m trying to rollup a count of all values in the same table. I’m pretty sure this should be fairly easy and I have a blind spot here. Any help is much appreciated. Thanks.