Formulas

I've been successful using Airtable data with quickchart.io to create charts that can be viewed in airtable or placed in Documint templates. What I'm trying to do now is "dynamically" code the chart inputs vice "hard coding"Specifically: I would like to select the fields to plot using a multi-select field and have that dynamically encode the chartURL. Example: FieldsToPlot includes 4 items (Units, Rate, Shared, Credit)- and from this I need to dynamically create this code:"&data1=" & ARRAYJOIN({Units}, ",") & "&data2=" & ARRAYJOIN({Rate}, ",") & "&data3=" & ARRAYJOIN({Shared}, ",") & "&data4=" & ARRAYJOIN({Credit}, ",")I can use substitute() to alter the data from FieldsToPlot- but I'm stuck on incrementing the number since it won't always be 4 items. I've thought of using a count function to determine how many items, but I'm not sure how to increment the number in a formula without using a loop.

Can anyone see what I am doing wrong here.Two fields, one date (as text type) one time (as text type) - The formulae field SHOULD make these into a single date/time field - This is the formulae I have, but I get errors on any dates with two characters as the day - I have tried various formatting DD/MM etc with no joy!. DATETIME_PARSE(CONCATENATE({TU Start Date} & " " & {TU Start Time}))

I managed to write a grammatically correct formula that I thought encompassed my query, but I'm getting a #ERROR! on the output.My aim is to add a conditional statement to the formula @AirOps was kind enough to format for me -SUBSTITUTE(IF(ISBN13!="", CONCATENATE(ISBN13, "5", {US Price})), ".", "")-where if {US Price} was less than 4 digits, it would add a 0 after the five, before the price. (Ex. if the price is 9.99, the output should end in 50999 instead of 5999.) This is what I came up with: SUBSTITUTE(IF(ISBN13!="", CONCATENATE(ISBN13,"5",{US Price},".","") &IF

Hello,as the subject says i'm looking to write a formula which returns a childs age at 1. august 2023 based on their birthday. i'm currently using a formula which gives me some info, but i would really like to know if there are other possibilities. This is what i use today:DATETIME_DIFF({Planlagt overgang},{Født},'months')I use months because years is not sufficient in detail - since i need more information.Is it possible to get "1 year and 2 months" or similar instead of "14"?Bonus task: how to return blanks instead of NaN.

Hello! I'm sure there is a very easy solution for this but I am having trouble figuring it out! I have an "Ad Record" field that may or may not have a date at the start (ex. "1/2/23 | Company | Product"). This formula parses out the date for me easily enough: DATETIME_PARSE({Ad Record},'MM/DD/YYYY') However, sometimes there may not be a date (ex. "Company+") so the above formula parses an ERROR. What sort of IF statement would work here to instead leave the field empty?This didn't work for me: IF({Ad Record}, DATETIME_PARSE({Ad Record},'MM/DD/YYYY'), "")   Thank you! 

Imagine I have a table of books and a linked table of status updates such as coming soon, pre-order, published, out of print, each with an associated date. Dates can be in the past or the future. I have a custom book view with all the books, but I only want to list the most recent 2 or 3 status updates. Some books may have last been updated years ago while others were updated just this month. I was unable to figure out how to do that. However I was able to display a year worth of updates leading up to the most recent one for each book. In the books table I created a Rollup field that showed that Max (latest) status date. Then in the status updates table I created two additional fields: link to the Max status date for each status entry A formula field called exclude: IF(Date < DATEADD({StatusMax},-365,‘days’),1) Finally in my custom book view I only include linked records where exclude is not equal to 1. Is there any way to a

Hello community,I'm trying to multiply two fields but I get an error message.Cell 1 : a currency field manually filled like "€100"Cell 2 : a formula field with number like "10,1"I want to multiply "10,1" by "€100"Formula : (cell1) * (cell2)Do you know why i'm getting an error message ?Kindly

Hello everyone,I'm wondering if AirTable has a formula that looks for the 2nd minor value (like MINOR on excel). I only found the MIN formula, that looks up for the minor value, without letting me search for the 2nd minor value.Any help will be appreciated!Thank you

Hi All,This is hopefully a basic and quick fix. I've got a concatenate formula that works beautifully to join a suffix to a base number, but part of the suffix is a price, and I need to have the "." removed from the string. Here's what I have so far:IF(ISBN13!="", CONCATENATE(ISBN13, "5", {US Price}))And, for example, that would spit out:97819528473244514.99How can I get it to remove that period from the price? When I did a search for similar issues, I saw suggestions of using a substitution formula, but I wasn't sure how that would work. I'm adapting someone else's code here. 

Hi all, I have a formula field that is used to output a date that is +28 days from a separate date field. Here is my current formula:   DATEADD({470 Certification Sent Date}, 28,'days')​  I need this field to always be 12:00a in the 'Americas/New_York' time zone. Is it possible to create this output without accounting for the date/time/timezone the original entry is added in the "470 Certification Sent Date" field? If so, what do I need to append to the original formula?Thanks for your help!

I was wondering how can I just show the days from any given date?

Hi all, Hoping someone can help. I'm trying to create a time sheet where the user inputs the hours & minutes they worked in 15min increments - so 1.25 (1 hour 15 mins), 1.5 (1hr 30 mins) etc.How do I get this to appear as 1hr 15 mins, 1hr 30 mins in an hh:mm format without asking them to do their time sheets in seconds and also have the ability to roll up the totals?Ideally they would not be tracking start end times using date fields. Just a box yo input a decimal. Example:  This is a platform I've used for timesheets in the past, Synergist Thank you!

Hello the community!I'm trying to do an extraction from a text but I can't get it My Initial text looks like that :   RP-34, CP-4554, IO-34, REQ-455, DK-344, REQ-44, REQ-404, FG-34   and I would like to export the list of items with REQ to have this result:    REQ-455, REQ-44, REQ-404  Does anyone have an idea of what formula to use? 🙄Thanks a lot! 

Hello all,I am new at this and english is not my first language, but i'll try to explain.I have a number of columns with numbers in it.I want to make a formula that if the number is between 2000 en 3000 in that column then in the formula colomn it states 50 bonus and if the number is between 3000 en 4000 it states 100 bonus and when not met is says no bonus.So:Colomn 1          Formula field1899               No Bonus3999              100 bonus2545               50 bonus So far i have this formula:IF({Jul} < 2000,"No bonus",IF({Jul} >= 2000

Hi there, I'm trying to get value on my main table from another table based on conditions I've defined in the main table and that will meet the parametres from a second table.I've worked with thw ways in order to tackle that issue. First is to write a formula able to do that. IF(AND(Table1.UOs = Sync.UOs,Table1.SP=Sync.SP),Sync.Name)The other way investigated is to go through script like the one below. Still working on it to include to loops for the two conditions to meet. let mainTable = base.getTable("Table1");let mainTableRecords = await mainTable.selectRecordsAsync({fields:["UOs","SP","Test Script 2"]}); let lookupTable = base.getTable("Sync");

Hey All,I know that Airtable is based off of fields and rows, so it's inherently different, but I wonder--similarly to excel, Is there any way to set up a formula in Airtable so that if 'x' condition is present in field 'a' and 'y' condition is present in field 'b', then field 'c' will display as 'z' for the corresponding row?For example, can I say, if budget is < x amount or > y amount, budget size should display "Big" "Little" or "Small?"Then, based on the info under budget and budget size, could I input logic that says, "If Budget is < 'x' amount or > 'y' and 'Budget Size" is 'x' 'y' or 'z' then What to Do should display 'a', 'b' or 'c' for a under that field?Please help! Thanks!

Hello my friends. I have a seasonal task calendar that is organized by project WEEKS. Our team works from Week 57 until Week 30. Tasks are entered into the Primary Column with an associated Start Date and End Date. Currently, the Project WEEKS are set up as tasks as well to allow the Project Managers to group by project week. When team members add a new task, they have to manually add the corresponding project week. I don't have to tell you what a chore that is. As the team Airtable person, I end up cleaning up the missing or erroneous data.In the screenshot below, you can see that I've started experimenting hoping that it would lead me to a solution. Here's what I need in prose:If the task START DATE and END DATE fall within the start and end date of a particular project week, return that project WEEK (currently in TEST Weeks). Do I need to reference the project weeks in another table? Thank you in advance for your help.

Hello guys,I am an extreme beginner,I have my data as a date in the incorrect format, I'm looking to auto-convert each figure into a specific "date" category.Can someone please explain how to do so?Thank you so much in advance

Dear Airtable Community, I have a board with records organized with date field and category list.1 - I would like to count the number of days in this record. If 4 records are posted with the same date I want it to count 1.Ex : 01/01/2022 I have 4 records registered at this date, I would like to return 1 day for those 4 entries.2 - I would like to get the number of records registered per date, I tried to use Interface to do it but could'nt extract the number of dates Step 1.Thank you for your help.Best regards,Delphine 

Hi This is a File Path from Google Drive integration.BOOK 📘/CHAPTER/PAGE/PARAGRAPH/SENTANCE/WORDI am trying to create formulas to split the File Path into separate fields for each folder. I can get the name of folder and the parent folder but want to separate it all out so I can see subfolders etc. I have been trying variations of LEFT/RIGHT/MID and LEN but can't quite work it out.Any help much appreciated.Thanks, Maria  

Hi Guys,I am trying to wrap my head around regex formula but can't figure out what if there are two patterns in the string. Basically I want to extract the number after the bracket but before the "+".Current value : /Date(1676537746330+0000)/Desired value : 1676537746330 Thanks

Hello Airtable Community,     Question: My main field (first left field) is the "Date", next field is "Day of the Week" (single select). Is it possible to automate "Day of the Week" field so it would automatically choose the day based on the calendar date input in the "Date" field?   Thank you in advance for your help

I'm using make.com and have a connection to Airtable and am trying to do a simple search on a table. I can't find a guide on Make or Airtable on the syntax required in Make's Formula field.I would like to do something like the following logic{Email}='1.email' AND {Date} >= CURRENTDATEWhere Email and Date are fields in the table I want to search. I can't seem to find the equivalent of CURRENTDATE in airtalble's Formula Playground or elsewhere.

Is possible to have a unique id number from autonumber function across all tables?, For example: If I create a new record on table A, that record must be 1; but if I create another new record on table B must be 2

Hello dears,I need help please to do the following formulaIf multiple cells in the "Star" column matches each other in text and the matched cells has the options "in office" or "stock" in the "Status" column then mark all cells as {show}If multiple cells in the "Star" column matches each other in text and the matched cells doesn't have the options "in office" or "stock" in the "Status" column then mark all cells as {hide}The below image should clarify the need please i need help