Formulas

hey community, I’m working on this formula to return a simple “Y” or “N” based on whether or not a date is past. The items in the grid could either be on a single date, or could have both a start date and an end date. I’ve tried a couple different ways of writing this formula but getting a lot of errors. What I’d like to do is: “if the end date field is empty, determine whether or not the event is in the past based on the start date field; otherwise [if the end date field has a date], determine whether or not the event is in the past based on the end date field.” Here’s what I’ve got: IF({End Date} = "", IF(IS_AFTER(TODAY(), {Start Date}), "Y", "N")), IF(IS_AFTER(TODAY(), {End Date}, "Y", "N"))) Could somebody help me figure out what I’m doing wrong? Thank you!

Hi All! I have a nested IF statement that is set up to find if the value from Column A, B, C, D, or E matches Column F. If one matches, it produces the name of the column. What I’m looking for is a way to produce more than one name if more than one column matches. Currently it looks like this: IF( OR( ColumnA, ColumnB, ColumnC, ColumnD, ColumnE ), IF( ColumnA=ColumnF,“ColumnA”, IF( ColumnB=ColumnF,“ColumnB”, IF( ColumnC=ColumnF,“ColumnC”, IF( ColumnD=ColumnF,“ColumnD”, IF( ColumnE=ColumnF,“ColumnE”, ) ) ) ) ) ) This only gives me one result. If more than one column matches, I’d like it to somehow give me both or all results Thanks!

I have a Fx field and for some reason, it’s showing " at the beginning and end of the words. Is there a way to remove it? Formula: {Contact} & “—” & {Type} How it’s showing: “John Smith, Acme Inc”—Email How I’d like it to show: John Smith, Acme Inc — Email

I am building a simple recruiting database in AirTable. Here are the tables that I have Candidates - (names, all information, name of recruiter linked to Recruiter table) Recruiters - Name, Email Position - Position, company, salary, etc. Submission - This is where a candidate is linked to a position. Link to Candidate Name, Link to Position. I also have the Recruiter Name here though Lookup. How can I display recruiter’s email so it can be included in email automation?

Hi all, I’m using Airtable to create a mood tracker. I have a form question which asks which of the following 20 emotions I’m feeling, multiple answers = ok. :green_square: Balanced :green_square: Calm :green_square: Cozy :green_square: Loving :green_square: Fulfilled :yellow_square: Energised :yellow_square: Focused :yellow_square: Happy :yellow_square: Inspired :yellow_square: Motivated :blue_square: Bored :blue_square: Drained :blue_square: Lonely :blue_square: Sad :blue_square: Tired :red_square: Angry :red_square: Anxious :red_square: Hungover :red_square: Restless :red_square: Stressed :red_square: Tense What’d I’d like to achieve then is to have a pie chart look at these emotions and display an ‘average’ with each of the 4 colours. My plan was to use IF(FIND) to create a kind of mood average column in the database to direct the pie cha

I’m trying to figure out the difference (in percentage) between two numbers. I’d like to set up a formula that gives the percentage (positive or negative) difference between two numbers. So in the screenshot above Row 1: Percentage decrease from 100 to 50 Row 2: Percentage increase from 25 to 50 Row 3: No change between 50 to 50 My formula so far: -(({Starting #} - {End #})/{Starting #})

The piece I am really struggling with is the comma delimited split. This is the order as done in power query. Select entire tab/Table Source = Excel.CurrentWorkbook(){[Name=“Table1”]}[Content], #“Changed Type” = Table.TransformColumnTypes(Source,{{Many Columns/Fields}}), Select only the Columns/Fields #“Removed Other Columns” = Table.SelectColumns(#“Changed Type”,{“Key”, “ManyLinked”}), Remove empty cells/rows #“Filtered Rows” = Table.SelectRows(#“Removed Other Columns”, each ([ManyLinked] <> null)), Split the ManyLinked into one row for each comma delimitate value - each row gets the value from Key and a single value from ManyLinked #“Split Column by Delimiter” = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#“Filtered Rows”, {{“ManyLinked”, type text}}, “en-US”), {{“ManyLinked”, Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {

Hi everyone! I’m adding a date field {Fecha de inicio} and a duration field {Tiempo de impresión} to get an end date with hour. But I need to know if the result can skip/exclude non working hours and days so the results are not for example, Sunday at 4 am but Monday at 8am This is what I have now: DATEADD({Fecha de inicio},{Tiempo de impresión}, ‘seconds’) I’d appreciate if someone can help! :slightly_smiling_face:

Hi! I’m tracking loan payments (for a 0% loan support-community). One column should show the monthly payment made. The other column should aggregate total payments made thus-far. Suggestions? In the screenshot I manually entered what I’m aspiring to have auto-calculated. Thank you!!

For a casting agency db I need to set a personalized filter column I have a field with the age, now I need a formula column that gives me the segment :point_down:t2: AGE CALCULATED COLUMN < 15 kids => 15 & < 18. teenagers => 18 & < 31 young => 31 & < 45 mature => 45 older I’ve been trying to make an if then concatenated formula but I’m always getting errors. Any help? :pray:

I’m trying to add a column to a view that calculates - if X date (date a specific record was updated) is older than 2 weeks, show text “2 week” in the corresponding cell. And so on… for all the weeks - 2, 4, 6, and 8. Is this something that can be done? I’ve tried playing around with IF statements, but I’m not sure how to have it detect today’s date minus 14 for 2 weeks, today’s date minus 28 for 4 weeks, etc.

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?