Formulas

This is a big ask, but I just can’t figure out how to setup this formula. I’m trying to determine the Step 1 “Initial Trial Balance”. The initial trial balance is the lowest account balance for any given month in a 12 month period. The amount can change based on the following variables (other cells): First Payment date (Month) School Tax Amount School Tax Disbursement Date Property Tax Amount Summer Property Tax Disbursement Date Winter Property Tax Disbursement Date This example scenario/figures can be found here: https://www.consumerfinance.gov/rules-policy/regulations/1024/E/ I can’t figure out how to create the formula so that it calculates correctly given the variables noted above. Anyone care to take a stab at it?

Hi, I am using a formula to create a slug: SUBSTITUTE({Job Title}," “,”-") However, is it possible to add another item in the formula, such as {location}? Many Thanks, Hannah

Hi guys, I have a next due date formula that updates to the next month when it is due but i want to delay the due date to change 5 days after the due date, below is the formula i use. DATEADD({Contract starts}, DATETIME_DIFF(TODAY(), {Contract starts}, “months”) + 1, “months”) Thanks in advance

I know the way I am doing this is unnecessarily complicated and even though it works, I’d be interested in learning how to do the same thing more efficiently. I have a formula field that looks at the content of another field ({WRITER 2: SOCIETY} and is supposed to fill in a corresponding number. {WRITER 2: SOCIETY} can contain either the names ASCAP, SESAC, BMI, or any other name. ASCAP, SESAC, and BMI all have corresponding numbers (with leading zeros) that should be filled in if the statement is true. ASCAP#: 00701163494 SESAC#: 00850346936 BMI#: 00794154022 If {WRITER 2: SOCIETY} contains any other name, fill in the BMI#: 00794154022 This is what I am doing. How can I simplify this formula? Thank you in advance!!! IF( {WRITER 2: SOCIETY}=0, ‘’, IF( {WRITER 2: SOCIETY}=‘ASCAP’, ‘00701163494’, IF( {WRITER 2: SOCIETY}=‘SESAC’, ‘00850346936’, IF( {WRITER 2: SOCIETY}!=‘ASCAP’, ‘00794154022’, IF( {WRITER 2: SOCIETY}!=‘BMI’, ‘00794154022’, IF( {WRITER 2: SOCIETY}!=‘SESAC’, ‘00794154022’,

Slowly I am getting a better understanding of Airtable :slightly_smiling_face: I have a table called task: And a table for completed task: So far so good. When a user completes a task, a new record of the task is created in “completed task” along with “device.id” to keep track of which user completed the task. Then I have a table called “Stats by device” Where I get the: • The sum of act/belong/commit score (by user) • total completed task (by user) And last, a table called “stats by task” Where I get: • A global count on how many times a specific challenge has been completed. What I would like to get, but don´t know how to: • a count that shows how many times a specific challenge has been completed by a specific user (i.e. User A has completed challenge “kaste ball” 5 times) I reckon that the answer to the question above will also give me the solution to the “top 5 challenge” stat - a list where you can see which 5 challenges has been completed by you the most times. Regards, To

Hi, community! It`s my first time writing a formula and I need your help! I’d like to create a formula to represent a due date ocurring ‘today’, a due data ocurring ‘this week’ and a due date ocurring this month. I tried to create that formula combining an IF function and a DATEADD function. Airtable keeps informing the formula is invalid. What’s is the correct way to write that? I thought the following formula was ok, but Airtable says it’s wrong IF({Due Date} = TODAY (), “Today”, IF ({Due Date} = DATEADD (TODAY() , 7, ‘days’), “This week”, IF({Due Date} = DATEADD (TODAY(), 30, ‘days’), “This month” ) ) ) The formula is wrong and I don’t know how to fix it. I tried several variations and all of them are wrong as well. Could you please help me solve this? P.S: The table is written in Portuguese. I translated some fields into English to help your analysis.

I have 2 check box fields and a cost field. If one checkbox is checked and the other is unchecked I want the amount in the estimated shipping cost field to go into the Unshipped Cost column(this is the formula for the Unshipped Column cost field) I have IF({We Ship}=1 and {Shipped}=0)={Est Ship Cost} Thanks in advance.

Hi, I hope this is an easy to answer question; sorry for the density of my skull! I’m building a content scheduler for my editorial team; some stories are immediately assigned a publication date, but for others that field is still blank/TBD. As a result, when you view the list of stories sorted by PUB DATE, all the undated ones appear first – which is undesirable. To avoid that, I created a new field (PUB-DATE-Fx) with a formula that converts the PUB DATE into an IF statement, thus: IF({PUB DATE} = BLANK(), “UNSCHEDULED”, {PUB DATE}) Now, when I sort the the stories by PUB-DATE-Fx, the undated stories come at the end, under “Unscheduled.” Wonderful! But it also creates ugliness: when I sort the stories by PUB-DATE-Fx, the date now appears as an string of digits (2021-03-09T00:00:00.000Z) instead of something recognizable (March 9, 2021) Is there an easy way to tweak the formula so it renders a date that’s more user-friendly? Thank you!

I’m an Airtable noob with what I hope is an easy-to-answer questions for someone (unlike me) who knows what they’re doing. I’m building a content scheduler for my editorial team; it includes a view of stories sorted by week. To do that, I created a field (WEEK) containing a concatenation formula built around the story’s expected publication date (PUB DATE): CONCATENATE(“Week of”," ",DATETIME_FORMAT(DATEADD({PUB DATE},-DATETIME_FORMAT({PUB DATE},‘e’),‘days’),‘M/D’)) That works great for those stories with an assigned PUB DATE, sorting them under “Week of 2/14” or “Week of 3/23” or whatever. But some stories don’t have a pub date assigned yet, and those date fields are blank. Here my formula fails, filing those stories under "Week of “#ERROR” How do I get rid of the #ERROR ? Seems like I need to combine concatenate with some sort of IF statement, but that’s beyond my brainpower. Any ideas? No doubt there’s a much simpler way to do this. Thanks!

I have an address field. I would like a formula (probably using SWITCH) to say that if the address contains “London” or “Chingford” or “Romford” the Location field should say London or if it contains “Hale” or “Stockport” or “Bury” the Location field should say Manchester. Thanks in advance.

Hello :wave: everyone! Wonder if anyone can help me. Background is that we run a startup where trial users do a free session then come back and do a paid session. I want to understand: a) the average time between the free session and the first paid session b) then the time between subsequent paid sessions So I’ve got a single lookup field with a list of dates in it: (10/1/2020, 8/24/2020, 9/28/2020, 10/8/2020, 9/24/2020, 12/14/2020, 10/5/2020, 10/27/2020, 10/18/2020, 11/19/2020, 8/31/2020, 9/7/2020, 10/15/2020, 10/12/2020, 11/5/2020, 10/19/2020, 11/12/2020, 11/9/2020, 10/26/2020, 11/10/2020, 12/10/2020, 9/21/2020, 9/14/2020, 10/29/2020, 10/22/2020, 10/31/2020, 11/2/2020, 11/5/2020, 11/21/2020, 12/1/2020, 1/19/2021, 1/26/2021) I’m struggling to find a way to use DATETIME_DIFF correctly to do this as it seems to assume that dates are all in separate fields, which obviously mine are not. Does anyone have any ideas on how to solve it? Thanks so much in advance!

Hello all, I have one table with clients (1 row per client) and another table with Goals. I would like to display the number of new clients (new rows) by month in the table Goals. How should I do that ? Thank you ! Jonathan

My database has run well for 3 months but now I need to change the discount structure. I have added a Column and called it Qtr and each record will be either 1,2,3,or 4 the idea being that the discount offered for that Qtr could be different. So far I have two different discount formula but how do I add the qtr to the formula ![Screenshot 2021-03-06 at 15.11.14|700x210] Formula ![Screenshot 2021-03-06 at 15.12.22|700x165] or (upload://pdKo1JReaKkSalwdO6FRbe3o0Lb.png) (upload://hc9LcneLXO1dfccFOyXw0GxLXND.png)

Can you help me understand how I can easily calculate my task. I have the volume of the position, each position has its own value, I have a balance minus the value of the position, then I get the remainder (equal), I need to transfer this value to the original column and the next row for the correct calculation of the balance for the next position. How can I accomplish my task?

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?