Formulas

Please could you help me to understand how to make this work? Thank you! IF(NOW(IS_AFTER({Announcement Start Date}&IS_BEFORE({Announcement End Date}, ‘ACTIVE’, ‘INACTIVE’))))

Hi there, I want to create a field that gives a different message to the user, depending on whether they have filled in a particular field ‘on time’ or not. I’ve attempted to explain it in text. If anyone could suggest the functions I need to use, I would really appreciate it. The problem for me is a. how to say ‘IF time is less than 5 minutes towards the time in the field OR IF time is more than 3 hours after time in field’ and to link that to b. ‘… AND field x has not been filled in THEN ‘show message a’’ and c. linking several options together (x3 optional messages). IF time is less than 5 minutes before end of lesson AND there is nothing in lesson reports field THEN “Please enter your lesson report, concrete targets and homework now” OR (IF time more than 3 hours after the lesson end time AND there is nothing in the lesson report field THEN “Your lesson report is late. Please enter your lesson report, concrete targets and homework now”) OR ((IF the report field is filled in BEFO

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 chart to, but I’m having a bit of difficulty writing the script. I was going down the track of: IF (

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 {itemType}}}), “ManyLinked”), Thanks, Teri

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 this date of course needs to carry the year… because

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?