Formulas

Hi there! :grinning_face_with_smiling_eyes: :wave: Is it possible to trim input, by using the _'s ? Or is there a more clever way? Input: John-Doe_recGQNicbCUqYIJ9s_coach-60bf663d8f264a0004816093 Output formula 1: John Doe Output formula 2: recGQNicbCUqYIJ9s Output formula 3: 60bf663d8f264a0004816093 Looking forward to some tips! Thankyou! :smiling_face_with_three_hearts:

Hi, I have a time and date stamp being created in a field (Last Modified Time) for a field that we use to say when an Estimate can be made - sort of a “Clock starts ticking Now!” timestamp. The original field that the Last Modified is watching is filled by a Zapier workflow. I then have a second field that gets time and date stamped (Last Modified Time) with when we check a box for when a user says the Estimate is done. Lets say the 1st field has a date and time stamp of 8/1/21 2:30 pm, and the second field has a time stamp of 8/2/21 3:34 pm. How do I calculate the difference between the two? The goal is 48 hrs, so if the total came out as hours I would be fine with that. I currently have tried the following formulas but it seems to be related more to the fomatting allowed by AT but that is just a guess. Here is what I tried: DATETIME_DIFF({Time Stamp on Estimate Checked}-{Time Stamp - Start Time}) also tried: {Time Stamp on Estimate Checked

I have been messing around with formulas for two days, trying to figure out how to put a checkmark in the column if the post date is today or after. So, for my social media posts (which I don’t have automated from Airtable, I post them using another platform), once the time has come and gone that the post should have gone live, I want the formula column for that record to have a check box. Thanks in advance for any help with this. Kelsey

Hi! I am trying to set up a field to automatic track the day of a meeting. This meeting is the last Thursday of each month. I found a formula to automatically populate the last day of each month but I can’t find/figure out a solution to change that date to the last Thursday. For Example Last day of the month for August is 8/31/2021 (Tuesday) but I need it to be 8/26/2021 (The last Thursday) Here is the formula I found for the last day of the month: DATETIME_FORMAT( DATEADD( DATETIME_PARSE( DATETIME_FORMAT( TODAY(), ‘YYYY’ ) & ‘-’ & (VALUE( DATETIME_FORMAT( TODAY(), ‘MM’ ) ) + 1) & ‘-01’, ‘YYYY/MM/DD’ ) , -1, ‘day’ ) , “MM/DD/YY” ) I was thinking another formula field using WEEKDAY() might work but it just gives an error when trying to change the day. Thank you in advance!

I’m working on creating a table for company accounts w/specific contacts within it. Table 1 - list of all contacts Table 2 - list of all companies The company table contains a link to the contact table to pull over the contact names & company name. What I want to do is create a field that counts how many contacts I have within each company. In my screenshots, the black lines are unique companies & contacts. The two yellow lines are unique contacts within the same company. The two green lines are also unique contacts within the same company. I want a new field that counts per company how many unique contacts per company. So the yellow company would read 2, black companies would read 1. This field would be in the company table (first screenshot).

I’m planning to use Airtable in storing price data from stocks over time. I’d like to make a formula that calculate the trend over the last time period consisting of X data points. In Google Sheets there are two functions for this “Slope” and “Trend”. Are there any equivalent for Airtable or a smart way to calculate the direction and steepness of the growth over time?

Hello, I am trying to write a formula to sum individual cells. It would look like =SUM(B4, H4, …) in excel. I cam across cell reference in the API (Airtable Scripting) but I am not sure how to reference individual cells in a formula within airtable. Thanks

I created a formula field to merge three date fields into one. No record has more than one date, so I wanted them all to be in one place. However, when I used concatenate, the dates are very long, so I’m trying to use DateTime_Format for all three, but I keep getting Errors. I’ve tried looking this up and have tried different combinations, but no luck. Here’s what it looked like originally before I tried formatting

Hi all, I am trying to combine text from two fields using Concatenate (and that’s simple enough) but when I try to add a Substitute formula to shorten one field’s text, it doesn’t work. I am new to formulas, and am sure I’m doing something wrong here. Here are the two formulas that work on their own, but not together. Thank you for any insights! SUBSTITUTE(RIGHT({2021-22 School Program & Membership},LEN({2021-22 School Program & Membership})-22) CONCATENATE({Program Booked},’ / ',{School Name})

Hi all, I am terrible with formulas, and have spent way too long looking through previous posts and reading up on Replace and Substitute and Find formulas. I have a column with various names for programs. I would like to cut the first 22 characters of the title and replace any instance of New School with “New/Comprehensive” for Salesforce upload purposes. Any idea where I’m going wrong here? REPLACE({2021-22 School Program & Membership},1,22,""), FIND(“New School”,{2021-22 School Program & Membership}),17,“New/Comprehensive”) I’ve also tried variations of combining Replace with Substitute. Both work on their own but I can’t find a way to combine them in one field. This may be impossible, but the more likely scenario is user error on my part.

I’m guessing there’s a syntax error here… I can get the formula to save if I copy a sample formula that includes 3 or 4 arguments, but as soon as I add more, I get an error when trying to save. PLEASE HELP! IF( {Ticket Total} > 0, SWITCH( {ShowDate4Formula}, “2021-09-11”, “PIT”, “2021-09-18”, “MAR”, “2021-10-02”, “PHL”, “2021-10-30”, “MD”, "2021-11-06”, “PAS”, "2021-11-20”, “SD”, "2021-12-04”, “DEN”, "2021-12-18”, “SJ”, "2022-01-01”, “AUS” ) )

From this: animals > cat > face > eyes into Animals/Cat/Face/Eyes how can i do that i know the first step: SUBSTITUTE({Category 1}, ’ > ', ‘/’) // this replaces the > into / and then how can i use the upper case method only for the first letter of every word? thanks for the help please write an example code, thanks!

Hi all, thanks in advance for reading. We have the fairly standard day formula implemented in our Airtable: IF({Live Date}=BLANK(),"", DATETIME_FORMAT({Live Date},‘ddd’)) However, for just one week, if the time is before 1AM on Live Date, it appears as the day before. I’m aware a standard fix for this is overriding time zones however that won’t work for us - we’re a global team so the localised time displays are vital, especially as we work in publishing. So, I’m kind of stumped. Any ideas?

Hello, Cell value 0 is blank Cell value 0.00 is blank Cell value empty is also blank How to treat cell with 0 value and empty cells differently? All the following forumas are returning result for both the {Buy} cells that has zero value and the {Buy} cells that are empty. I want result only for the cells that has 0 or more value not the cells that has empty values. IF(AND(IF({Buy} >= -1, TRUE(), FALSE()),{Sell}), (Sell-Buy)*Lot*Qty) IF(AND(IF(NOT({Buy} = 0), TRUE(), FALSE()),{Sell}), (Sell-Buy)*Lot*Qty) IF(AND(IF({Buy} = BLANK(), FALSE(), TRUE()),{Sell}), (Sell-Buy)*Lot*Qty)

Hello, I’m trying to generate a monthly report can someone give me an idea on how to do it please other than Filtering the date.

I’m trying to create a Text field that combines two existing fields that are separated by a dash and a space. I’m using the formula tab but keep getting this error message. Can anyone tell me what I’m doing wrong? I’m new to Air Table so I’m assuming this is a rookie mistake :grinning_face_with_smiling_eyes:

I’m probably overthinking this, but struggling to find the right formula to accomplish the following: I have a list of addresses in abbreviated format “123 Main St”, but some addresses have numbers on the end (“123 Main St #2A”). I’m trying to find a way to extract the “St” without affecting addresses with street names like “State” or “Sterling”. It doesn’t need to be perfect, so addresses with St that have a number after them can remain in that format. I’m thinking I need a formula that combines IF, RIGHT and SUBSTITUTE, but can’t quite get it. IF “ST” is contained in the last two characters, extract them. Thanks, Airtable!

Hi everyone, struggling to pin down a formula… My goal is to track investments over time (i.e. date of deposit, how much was deposited, what the total amount was in my wallet at the time of that deposit, and finishing with profit/loss since I began). The formula I need is for profit/loss since starting. I figure this value is “Total Amount After Deposit” minus the SUM of all values in the “Converted” field (which are all deposits converted from EUR to USD on the investment platform, i.e. Converted1, Converted2, etc)… So how do I get the SUM of ALL Converted values into the formula? (See screenshot for reference) Any questions/clarification required, please let me know. Thanks! Christopher

Hello Airtable community! I’ve been cobbling together a Base for managing my freelance business which includes tracking bank transactions, expense categories, clients, projects, billable hours, etc. I’m attempting to recreate the 2021 Tax Rate Schedule for the Single filing status from the 1040-ES. I believe I’ve gotten most of the income tax bracket arguments to trigger properly but am stuck for any amount over $209,425.00. Adding even $0.01 to that quantity ends up with a seemingly empty result. Where am I goofing up my formula? IF(AND(INCOME >= 0, INCOME <= 9950), (INCOME - 0) * .1 + 0, IF(AND(INCOME > 9950, INCOME <= 40525), (INCOME - 9950) * .12 + 995, IF(AND(INCOME > 40525, INCOME <= 86375), (INCOME - 40525) * .22 + 4664, IF(AND(INCOME > 86375, INCOME <= 164925), (INCOME - 86375) * .24 + 14751, IF(AND(INC

I have 2 different kinds of {App Pseud} formats, e.g. kay_obsessive goblin fic (Kiestan) Then I have a formula to spit out a URL, where the concatenate is the important bit: IF(OR({Work Submission Pseud}!={Synced Participant},{Assigned Writer(s)}=BLANK()),BLANK(),CONCATENATE("https://autoao3app.firebaseapp.com/#/mfd2021_round2/user/",SUBSTITUTE(TRIM({App Pseud})," ","%20"))) And it spits out these URLs: https://autoao3app.firebaseapp.com/#/mfd2021_round2/user/kay_obsessive https://autoao3app.firebaseapp.com/#/mfd2021_round2/user/goblin%20fic%20(Kiestan)n The extra character on URLs where the app pseud has a parenthesis always

Hi, I’ve got the same issue as this https://community.airtable.com/t/calculating-monthly-revenue-from-start-and-end-dates. But I can’t locate the answer. So I have a calculated column which shows what’s left to pay on a contract and I have a calculated column showing number of months left. I’d like to build this out to show the monthly revenue based on a equal monthly amounts. How do I then get it to populate months from there? Hope that makes sense. Shilpa

Hi there, Is it possible to activate/disactivate buttons linked to script depend on status in another column? The case is: I have buttons: run, pause. Each button has own script to do. If status is “to do”, only 'run" button is active. If else ‘in progress’ show ‘pause’. I tried to use additional column for that with IF(),but when i’m put the column with the button to TRUE/FALSE condition formula field convert it to link that is not working. Could you help me to find a way to solve it?

Hi, I want to create a url that contain the contents of a field. Ex: NAME - Google Search NAME is the field, so I can to search on google the content of the field How can I do? I try this (“https…q=NAME”) but it doesn’t work Thanks

Hi! I’m trying to create a prefilled form URL, but I believe the prefill isn’t working because I’m trying to prefill information with hyphens. Any ideas on how I can make this work? Example: Field Name: Type Name Example data: Apples - Michigan Form URL: “https://airtable.com/shrxxxxxxxx?"&“prefill_”&ENCODE_URL_COMPONENT(“Type Name”)&”="&ENCODE_URL_COMPONENT({Type Name}) How do I make sure “Apples - Michigan” doesn’t break the URL (and in turn, not prefill the form)?

Hello! I am organizing and archiving all the films shown from our local cinema. We started in Google Sheets andhave now imported into Airtable. Now we have seperate fields for the Year the film was shown, the month and the day. Is there a formula that combines the numbered fields to a date field? I can’t seem to find it anywhere. Can anyone help? I asume this isn’t the hardest formula, but I don’t know how to do it. Thanks!