Formulas

I have a little brain exercise and I’m running in a hamster wheel and cannot see to find the logic to use or if there’s a function out there that would solve my problem. I’ll take any help I can get :))) How can I create a formula doing the following behavior ? Variable : paymentDate - formatted in YYYY-MM-DD (ISO) I want to make each line appear if it fits the criteria. A paymentDate can belong to Quarter 1, 2, 3 or 4. Q1 is from Jan 1st to March 30th Q2 is from Avril 1st to June 30th etc… We have one month to prepare our reports after a quarter ends. What I want is to hide all the records from a current quarter until the deadline, i.e. all the payments made during Q1 2022 for instance will not be visible by my partners before April 30th (March 30th + 1 month). What I need is to be able to calculate a date (quarterDeadline, for instance) that will be April 30th, July 31st, October 31st, and then January 31st (of the next year). But

Hi, just wondering if someone can help me, I have an issue with a linked field. The problem I am working on a skills matrix and adding skill for a user is fine, however I need to create an additional field to show all the unselected items, ie. what goals after left to achieve. So what I need is the linked files that shows all the selected items and another that show the unselected items. Hope some one can help and thanks in advance.

Hi ! I have a calculated field that returns for me a value that looks like 2021 | Q3 or 2022 | Q1, determining the year and the quarter from a payment date. Now, in a linked table where this field (“Quarter”) is a lookup, I’m calling the RIGHT() function as follows : RIGHT(Quarter,2) in order to have as a result Q3 or Q1 as in the examples above. However, I get an ERROR! I believe it has to do with the fact that my “String” is a lookup but even if I create a field in the table, a function, that is basically “Quarter” (i.e. take the value that’s in the lookup) and call RIGHT() on that new field, I get an ERROR! too. I am deducting that RIGHT() cannot be called on a cell that’s a function ? True ? Any workaround ? Thank you !

Hi everyone, Can someone help me with formula Regex_extract. In Airtable I have a field with text in quotes: " “msg”: “2. Have a second version of that same photo with no background”" . I can’t get rid of the quotes with formula REGEX_EXTRACT.

Dear all, i am looking for a solution for the following problem: “Column A” is a multiple select field where I have data on company presence in a variety of countries (e.g. “Poland”, “China”, “Germany”) “Column B” should have a formula that checks whether “Column A” contains an entry (among others) that belongs to a specific list (e.g. “Poland” or "Germany). In this case the formula should return “Europe” I am not sure, whether this problem should be solved with IF, nested IF, SWITCH formulas or whether I need a completely different approach Thanks for any input, Cheers

I’m trying to add a column into my financial forecast that shows whether Column A + Column B = Column C. If the value in Column C doesn’t match I’d like it to show me that it’s incorrect. The formula I have so far is below but I can’t get around the error. Can anyone tell me what I’m doing wrong? IF({Revenue Recognised in FY22}+{Year Recognised To Date}={Contract Value},“MATCH”,IF({Revenue Recognised in FY22}+{Year Recognised To Date}>{Contract Value},“INCORRECT”,IF({Revenue Recognised in FY22}+{Year Recognised To Date}<{Contract Value},"INCORRECT”))) Any advice is appreciated! Thanks

I’m really struggling on working out how to create a formula for the above. For context, I have an AirBNB type operation whereby there is a check in + check out date for guests who stay which can sometimes be as long as a few weeks. I’m trying to figure out the best way to work out if a specific day falls between 2 dates so I can group data based on events & also calculate occupancy in a given month or event based on a property (e.g. if there’s a major event in a city on the 10th of February & there’s a checkin on 8th February & checkout on the 11th February, how to have that filter in as an “event booking”). Alternatively, with the same logic, if I want to separate “weekend bookings” from “midweek booking”, I’m happy to have a separate table where I manually input every weekend/event. Would greatly appreciate any ideas anyone might have on how to solve it

I would like to achieve the following: Multiply all the quantities inside Part List Output (2x, 8x, etc) by the Quantity value. So in the first row in the example provided I want to multiply all of the integers in the comma-separated strings by 6. I’ve mocked up a demo output in Calculated Part List. One complication to note - the strings can also contain integers that I do not want to target for calculations, eg. 1x Shelf 812mm. I can easily manipulate the Part List Output if necessary. For example, wrap in special characters: _2_ Large T Side with Holes, _2_ Large A Side, _1_ Large Base, _8_ Spring Clips Perhaps @ScottWorld has managed this?

I am getting an export that expresses a start and stop time as text in 24 hour format. I am trying to make this into AM/PM. Thoughts? Input: 730 1130 1330 1700 Desired Output: 7:30 AM 11:30 AM 1:30 PM 5:00 PM

I have read the solutions for extracting text but still can’t seem to get it right. Need extract text between the dashes (-). The text between the dashes could be any number of characters, if there is a limited it is unknown. - ###### - ##### - ##### -

Hello, I have a Jotform survey flowing into my base per each response. In the survey, I have multiple “Check all the apply questions” that subsequently flow into multiple-select fields in Airtable. I am trying to connect a data visualization program to Airtable to visualize these responses, but the connect combines the row into a single string (i.e. “Option 1, Option 3”), which makes it difficult to visualize. Is there a way to have a field that puts a 1 if a selection is made, or a zero if not. I am trying to avoid making separate tables with available options, since my base already has a lot of tables (this is a big project). What I initially have: What I’m trying to achieve:

I am trying to convert TODAY() to the WEEKDAY() and then to the single letter for that day of the week. I need to match that to a data feed that provides the day of the week as a letter. Here is my formula: SWITCH(WEEKDAY(SET_TIMEZONE(TODAY(),'America/Los_Angeles')),0,"U",1,"M",2,"T",3,"W",4,"R",5,"F",6,"S") The problem is that it is returning the wrong WEEKDAY() numeral. If I do this: SET_TIMEZONE(TODAY(),'America/Los_Angeles') I get the correct day. And if I do this: WEEKDAY(TODAY()) I get the correct weekday. But as soon as I combine these ideas it’s returning the value for yesterday. So, assuming we are using today as 2/2/2022 I’d expect to get WEEKDAY() of 3 and my SWITCH() would change it to a W. But what I am getting returned is “2” which is turning into a T. Thoughts?

Hello! Is there a way to calculate the value of a mathematical expression which is written on a long text field? For example (long text field: 340+850 and formula field: 520)

Hi all, • I’m asking Harvest to populate a field in table jobs called {client id by Harvest} via Zapier - this is working fine • I then want AT to lookup the corresponding client {Name} in my client table that relates to the client ID {client ID - manual} in the calient table and populate the field {client name - lookup} in the table jobs i cant make it happen for some reason… quite frustrating. any thoughts?

Hi there, I have a rollup field - Field A - which displays items in an array using a comma separator. I retrieve the items using ARRAYUNIQUE(values). When I try and call this field in another field - Field B, this formula keeps the comma separator: {Field A} Produces: 1, 2, 3 When I try and add text to the formula, the commas are lost and the values aren’t separated but bunch into one long block of text: {Field A) & " Text" Produces: 123 Text Would anyone be able to help? How can I add the commas/separators back in between these values?

Hi all! We have an employee paid in part on commission. I’m attempting a formula to calculate the pay. Here are the factors: If the appointment shows (pays $25) If purchase is made (pays based on source of purchase) Source 1: $50 Sources 2&3 : $100 So I’m looking for a formula that can display the amount if an appoint shows and no purchase is made and also if an appointment shows and a purchase is made via any source. Thanks in advance for the help!

Hi, I have created (what is essentially) a spreadsheet that tracks my products for shipping and calculates the costs associated with shipping and duties. I want to create a Single select column where I can select a percentage to add to the Net Value of my items and have it give me the new total including the added percentage - for example, $100 Net cost x 10% = $110. I have multiple percentages I need to choose from - 10%, 15%, 20%, 22% and so on. I’m very new to this, so apologies if this has been asked before. I tried to create a separate table and link them but it says there is an error in the configuration. Any help would be much appreciated!

Hi there, Let’s say I have a formula field - Field A - that produces a decimal value (e.g., 0.5). I use the formatting option within the formula field to display this value as a percentage. However when I call that field in another formula - Field B - it is displaying using the original decimal value. Is there a way to format my the ouput in Field B, so that it displays as a percentage value? Currently Field B is outputting data like: 0.5 0.8333334 0.2 I want Field B to output: 50% 83% 20% If anyone could help that would be much appreciated!

I track consignment items in Airtable. This is my consignment pricing structure: Day 0-29: Initial List Price Day 30-45: Initial List Price minus 15% Day 45-90: Initial List Price minus 25% Day 91: Expired What formula will auto-discount the initial price by the above amounts based on the date when the item sells? Can the entire cycle of auto-discounts be calculated in one column? What formula can be used to mark an item “expired” on day 91? - could this formula also be included in the same column as the auto-discount column? :slightly_smiling_face: Thanks in advance for any help or insight.

I use Zapier Parser to bring over a PDF attachment and update a record in AirTable. The issue is it takes the email attachment and gives it a super long filename in AirTable with letters and numbers. Is there any way to have it be a set filename or custom filename instead? I tried using Format from Zapier but not having any luck.

Hi, having issues combining two different IF statements for contracts: IF(Phase=“ :inbox_tray: Lead”,“ :bell: ”) IF(DATETIME_DIFF(TODAY(),{Last Contact},‘days’) >= 7, “ :bell: ”) They both work separately but want to be able to combine them so that IF a contract is in Lead phase AND the last contact date was 7or more days ago, then follow up ( :bell: ). Every time I use the (AND) function, following the example here, it doesn’t work. Is it because the Phase field is a function?

I want to know (and then trigger an automation) when there are 10,000 records added to my table. How can I do this?

We want to know how to fetch a field from another table and make it multiselect depending on the record it relates to. Example in record one, I bring a field from another table (This one has several data in the same column) and I want to be able to select one, several or all; for record two, I bring the same field from the other table and I only want to select some that are not the same as the one in record two. In the first record, choose a data from the generic task package, in the second record, choose other data

I am trying to create a table that calculates equipment depreciation by straight line, sum of years, and double-declining, because we have a number of different equipment types that depreciate in very different ways, but want to monitor all of them in one place. I am struggling to find a way to calculate the accumulated depreciation in each given period. Straight line was no problem, but the sum of years is reminding me a lot too much of high school calculus and I’m ready to take it all back into an Excel sheet no one will ever look at again. I haven’t tried the double-declining yet, but I’m hoping it’ll be easy after sum of years! Is this even possible in Airtable? I suspect I will need helper fields and don’t really want to go there in case some future equipment has a much longer lifespan than I’ve built in. Excel has an SYD function to get the period Depreciation Expenses, but nothing (that I’ve found) to give you the Accumulated Depreciation without refere

Hello beautiful community! I hope you are doing well. I’m looking to optimize timesheets on airtable. I have almost everything set up, but, I am missing a feature: “cumulative time” / overtime. In fact I would like to have in the time bank who is the sum of the overtimes per employee over all periods to know how many hours the employee or the company owes (time balance) I was thinking of adding the SUM/Overtime by period and have it appear in the employee’s profile (Banque d’heure) Do you have any idea of the formula / process to do this? After that, From this bank of hours, the employee could with a form say: I take x hours for recovery. it makes sense? Thank you for your help! Template sour