Formulas

Hello everyone,I'm having trouble formatting a lookup field in Airtable. Specifically, I have a "Price (Lookup)" field that I want to separate using line breaks instead of commas (in a new "Price 2" column), as I plan to use this column for a table in Page Designer. However, the currency format is being lost in the process.Does anyone have any tips on how I can achieve this while maintaining the currency format?Thank you in advance for your help!  

Hi Community,I am looking to have a total sum of 1 column to reflect on a row that will match item number for another tableIf you look below at the snapshot images, the 1st table provides assessments for sites and I formulated a square yard number under each column (C202(52)(C) - Removal and Disposal of Existing Portland Concrete), (C202(52)(D) - Removal and Disposal of Existing Sidewalk...I am looking to have these total summed up quantities to reflect another table as shown on the second image, under the Total Assessment QTY linked accordingly to the item numberAny advice would be greatly appreciated!!!     

Hello Everybody,  I need some help with formula, so here is what I have, I have a "Multiple Select" field, lets say I have options like these there: Option A, Option B and Option C. Every option has its own price, let's say Option A is $10, Option B is $20 and Option C is $30. I have another field "Price", which is currently set up for "currency" and I manually enter the total there every time. So I want to use a formula to have a total sum of the options that I choose in Multiple Select Filed, any ideas? Thank you in advance

 Hey everyone,I am fairly new to Airtable so I hope it's not too nooby of a question.I am intending to create a fact table with invoices. Now I would simply like to aggregate the value for each client. The only way I found is the "group" feature, however this appears to me as just being a visual filter. In any case I need both the individual table as well as the grouped overview.Also I found that I can link records to each other but the actual grouping column is a formula. As soon as I select it as lookup, that is breaking the formula.How can I possibly achieve that?Kind regards,Marcel

Hello,I need to calculate a date. Here is an example:Start date of employee is 2023-05-03. The previous Monday, an email should be sent to the employee.How can I calculate the date based on the weekday.I have a formula, but it only works if employee starts on a MondayCheers

Hi, I'm looking for some help with a rollup that is rolling up start dates and returning the earliest date from a table. The aggregate function seen on the image is not taking into account the filter I'm trying to apply. I'm trying to return the earliest date on a roll up where the Session Name is not Session Zero. I do I do this in the formula ?  

I have 5 free form data fields, none of them are required. I need to display only the ones that receive data, and exclude the blank ones. How would I create that formula?

Hello all, I am trying to use the WORKDAY_DIFF Formula but it is returning #ERROR and I am struggling to determine why.  The IF,AND piece is working, the WORKDAY_DIFF is not (on its own or with the rest of the formula). IF(  AND({DateSubmitted}, {DateCompleted}),    WORKDAY_DIFF({DateSubmitted},{DateCompleted}))My fields are set to ISO dates. DateSubmitted is the Created Time auto value, and the DateCompleted is a formula - IF(Status="Done",NOW())Any help you can provide would be much appreciated. Thank you! 

Hello,  I'm current working with this formula: WORKDAY({Start Date},{SLA Test})   ---which is the formula in the 'due date' column.I would like for the Due Date to be left blank instead of #Error if there is no start date or SLA.Can someone help with an additional formula that I could add to the one above? Thank you. 

Dear community I’m a nubee here. Trying to schedule a non profit festival.Is there a formula for getting start and end time into the same column? Such as: DATETIME_FORMAT({Start Date},'dd. DD. HH:mm'), which is already pretty good. This gives me ie.: Mo. 25. 09:30I would for the sake of sharing like to include the {End time} ie (11:15) to get this appearance:Mo. 25. 09:30-11:15 Is there a formula to do this?I tryed DATETIME_FORMAT({Start Date},'dd. DD. HH:mm')DATETIME_FORMAT({End Date},'HH:mm'), but it does not work.Hoping for an answer.🙏 Uffe from KlaDa 

Hey All!I'm sure I'm overlooking a tiny detail, but here goes.My existing formula is:DATETIME_FORMAT({Date and Time},'MM-DD-YY h:mm a')&" GMT"&" - "&{Course Name}The documentation shows to add a timezone, to do this:DATETIME_FORMAT(SET_TIMEZONE("07/10/19 13:00", 'Australia/Sydney'), 'M/D/YYYY h:mm')My take, that doesn't work, is this:DATETIME_FORMAT(SET_TIMEZONE({Date and Time},‘America/New_York’),'MM-DD-YY h:mm a')& “ EST”& {Course Name}Can anyone tell me what I'm doing wrong? 

Greetings! I am pretty new to Airtable, so forgive me if this is a silly question: For reference, I am maintaining a book of business for an insurance office. This office manages multiple lines of business (home, auto, life, business, etc... which are a single select field in my table).  I am trying to create a column that displays the "next renewal", based on the field "inception date".  Here is what I have so far:IF(  DATETIME_FORMAT({Inception Date}, "MMDD") > DATETIME_FORMAT(TODAY(), "MMDD"),  DATETIME_PARSE(    DAY({Inception Date}

Hello,I'm trying to create a schedule table to track team members availability each month using the total # of budgeted hours by month.E.g. for the month of May  If Sum of {Budgeted Hours} > 160 = "Overbooked" , = 160 "Fully Booked",  < 160 "Underbooked". I would then use these values to determine the shading on the Gantt chart  

I am trying to create a field that will say "❌ Incomplete Info" with a conditional statement.We two columns for custom products (price and description). If both columns are empty, info is complete. Record has no associated custom products.If both columns are full, info is complete. Record with custom product has description and a price.If ONE column is empty, and the other is NOT, we have incomplete info. If there is a price, but no description, or there is a description but no price, then that is when I want the "INCOMPLETE INFO" to appear. I can't seem to figure it out. I've successfully nested, but it will only ever apply to some of the scenarios provided.Thanks in advance!

I have a table that includes a date field as well as a single select field with options that are times of day (e.g., 9:00am, 12:00pm, 3:00pm). This allows users to select a date and time from a limited set of time options rather than Airtable's unrestricted datetime selector.I'd like to compose these fields into a single, calculated datetime field. This composition is a fairly elementary task, but the formula keeps parsing the time string in UTC. How can I get Airtable to read the time string based on another time zone (i.e., the time zone my team works in)?Here's the formula I've written so far. I've tried using `SET_TIMEZONE()` within the various `DATETIME_FORMAT()` functions, but can't quite get the formula output right: IF(NOT({Time option}), {Date}, DATETIME_PARSE( DATETIME_FORMAT( {Date}, "YYYY-MM-DD" )&" " &DATETIME_FORMAT( SET_TIMEZONE( DATETIME_PARSE( DA

Hi I have a prefill for, formula in my base which ends with ?prefill_STWorkshopReference=PA007130&hide_STWorkshopReference=trueand it works fine when clicked in the base But when included in an automated airtable email the _ and _ are omitted and the text in between is italicised - presumably due to markdown languageIs there a way of solving this ?  

I have three columns: 1st is the Sales Last Month, 2nd is Sales This Month and 3rd one shows the ratio of the two columns. I want to add the DateTime format so Sales Field will look - Sales: 137/150 (Apr: 201/200) instead of Sales: 137/150 (201/200).   

So I have two tables, Project Reviews and Students. The Project reviews tables has a column that is linked to the student that submitted the review. Now, I am trying to retrieve records submitted by a particular student and I kept getting an empty array. Here is what I tried: .all(filter: '{Student} = "#{[student_id]}"') I am using the Airtable Ruby Gem on a rails app. Any help is appreciated. Thanks.

Hey GuysI have a dynamic datefield (updates thru automations) that begins from todays date and records 10 days ahead. Is there a way to write a formulafield that ranks the date with numbers 1-10, i.e so todays date is 1 and tomorrow 2 and so on? I could use that info for navigation in an app.Thank you in advance.Jesper

Hi, I'm wondering how to set up a search that returns only an exact match. Can anyone help? Thank you.

Currently, when I click a link in an Airtable 'button', it opens that link in a new tab. I do not want to open the link in a new tab.I want to open the link in the same tab (which will navigate away from Airtable).Any idea on how to accomplish this?

Hey all!I've got a long formula, that currently has just one IF statement, however, I need to update to add another alternate IF.Here is the existing formula, that works:IF({Course Status}='Retired',"RET-"&CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name},CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name})&" "&{Engine Version}Here is what I was attempting to add:IF({Course Status}='Retired',"RET-"&CONCATENATE(LEFT({Course Level},1),IF({Course Status}='Archived(DO NOT DELETE)', “ARC-”&CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name},CONCATENATE(LEFT({Course Level},1),{Course Type Coding (

I am using Softr and Airtable.Create a formula. If the field meets a criterion I require it to display a text "x", otherwise it must display a text "y".But the text has a separation (enter / space) between two paragraphs. When copying the text it has the separation (enter / space). But when displaying the information in Softr I am getting the next character "R" to create the hyphenation (enter / space).How can I have the separation (enter / space) in aritable, but do not show the character "\R" at softr?I appreciate your help.Translated with DeepL

I'm sharing a solution to a problem I had; hopefully this will help others. Note: you will need to be on a plan that includes Automations.When you try to reference information from a linked field in a primary field, Airtable will warn you of a circular reference, even if no dependencies will be affected by the contents of the primary field.For example: I work with cars, and I have two tables (among others) in my base: Vehicle Generations and Vehicle Years.In Vehicle Generations, I'll have a record with the primary field "Jeep Wrangler, 2007–2018"; in Vehicle Years, I'll have a record with a primary field "2007 Jeep Wrangler", or "2008 Jeep Wrangler", and so on. And each of these Vehicle Years records is linked to a Vehicle Generations record.Now, if I want to dynamically generate the primary field for Vehicle Generations (e.g. update the end year as new model years are added), I could use a formula field like{Make & Model} & ", " & {S

Have a base that looks at deal flow, including anticipated close date and value of each deal.  While we can easily view our "pipeline" at any time, struggling to figure out a way to store that snapshot of data on a routine basis.  We don't store updates to pipeline as a record itself, but update the record to the anticipated value.  At the end of a specific month in the past, what did our pipeline look like in terms of deal value, grouped by when we expect it to close?  Used to look at accuracy (anticipated vs actual) of our project pipeline projections.