Formulas

Hi everyone, I am trying to create a formula that will output what funding year a record falls within. I have a field with the dateStart of the funding and another with the dateEnd of the funding. I also have a program start date which is a static field. I am calculating the funding year manually and am sure there is a formula that could do this for me, but I haven't been able to figure it out. In the example below here is how I get the funding year: I write out the date ranges and manually tag the funding year depending on what year it falls within. The program start date is what the funding years depend on.9/1/2019-8/31/2020 = Y19/1/2020-8/31/2021 = Y29/1/2021-8/31/2022 = Y39/1/2022-8/31/2023 = Y4Any suggestions would be MUCH appreciated!

Hi all, I am looking to create an interactive leaderboard, where my students fill out a form which relays the results back into a board. I have a rough idea of how to do this, but was wondering if anyone has attempted this before or any leaderboard type board? Thanks! 

I’ve seen some complain that there is no SPLIT function in Airtable. Here’s my workaround: I’m dumping data into an existing table and I have formula fields that “pull data out” into other cells based on keywords. The formulas are just combinations of FIND, MID, and LEN. I’m doing this work once and the table will do the work for me when I import new data. Yes, a “split” would be nice, but it’s definitely possible without it. P.S. Don’t forget to escape characters with “\” in formulas.

I can't for the life of me figure out what's happening with this one transaction in the personal finances base I've been building.  Would you guys take a look?These are two of the same type of transaction for comparison.  The top transaction calculates exactly as expected.  The bottom transaction calculates out to -$0.00?!?!  I have traced and re-traced the formulas and can't find the issue, haha!  Here's the flow:The Amount field calculates by choosing the non-empty entry amount field from the following options:  an Enter Amount field that allows me to type the amount indirectly, and the Import Debit and Import Credit fields you see in this screenshot which come from my bank import files.Transactions are designated into one or more categories.The amounts from those designation records are rolled up into the Total Split field.The Left to Split formula is: IF(Type="Withdrawl",Amount-{Total Split},IF(Type="Deposit", Amount-{Total Split})) Does anyo

I’d like to use a rollup field with an array as an input for a formula in a different field, but it gives me an error. It seems to not be treating the rollup as a text string.

Hello, I need help on an airtable formula. I have a date conversion formula: but I would like if the table is empty it says "Enter your birthday date" To avoid the error message please my formula : DATETIME_FORMAT(Dateofbirth, 'DD MMMM YYYY')thank you in advance

Hi all,I recently switched my team over to Airtable for project management. There were a few things holding me back, including that we'd have to build the automation for recurring tasks manually. Now that I have that built, I'm curious if I can get a formula into the automation which adds the proper deadline to the NEW task.Issue is - there are different kinds of recurring tasks (Daily, weekly, monthly). Do I need to parse these out to create a separate automation for each of these types, and then enter a formula into the dynamic field type of the automation? (I'm also looking for the actual formula!) The other thing is if it is a daily task, we skip weekends, so we wouldn't want a new task to show a deadline of Saturday or Sunday. I'm not sure if this is possible 🙂 Here's what the automation currently is: I can add the deadline field, make it dynamic, have the previous deadline listed, and I think I'd need a formula that adds +1 day, +7 days, etc, correct? 

Hi,  I have an issue when using the formula fields in AirTable when using an IF statement.Context: I need the fields to be format as a percentage, or blank if there's an error/infinity.Here is the formula that I want to use in its simplified form, and I can easily format it to return as a percentage. (FYI both fields used in this formula are rollup fields)Sometimes, the values are 0, or blank, causing the data to reflect as such.  (or infinity in some cases)I want the data to reflect as blank. The following  is the formula used, that would have worked fine in excel/sheets.However, AirTable seems to have no idea to read this as a string or a number anymore since I cannot edit the format:Is this intended, if so, is there a way for me to workaround this?I am aware that it is possible to replace the value1 into 0 (instead of "" or ''), but that would cause a problem with another formula down the line.My result end up becoming a long string of decimal that is hard to vis

Hello, I'm trying to figure out how I might be able to extract a portion of string relative to the position of a delimiter or other character.For example, I have a table of Events that are structured as follows:##### | <Name of Event>.I want to be able to use the vertical bar "|" character as the item to extract each chunk of information:##### and <Name of Event> respectively.Any help with the formula logic here would be appreciated!

Hello everyone! I have 3 fields: 1. Date field 2. Formula field that will show a date based on the date field3. Type of issue fieldWhat I want to accomplish is this: the formula field should only show the date IF a certain type of issue in the 'Issue field' is chosen. How do I accomplish this using a formula? 

Hi everyone, I’m new here, and haven’t found the response to my problem anywhere. One of my columns contains text with several tags. I need to split the text in that column, into several columns in which each column would contain one tag. Can anyone help with that? Thanks!!

In my base I got some images saved which I want to use in page designer.I believe I used to be able to use the Image URL in the page designers Image element. It seems still to work for about 24 hours and then the URL becomes invalid (which believe has something do with Airtables new security updates.)However is there still a way to URLs of an image in different attachement fields?

Hi everyone,I suppose someone else must have dealt with this before so here my question, hope someone can point me in the right direction!We run a few bars in our property and we connected our POS to Airtable, so every day I get all sales data imported. The data includes the exact moment (date and time) of the sale, as well as total value, tips and so on.We also use AT for staff planning and payroll so I know who worked exactly when and where.Now, I want to create a table that reports  how much revenue was made within one shift, based on actual clock-in and clock-out time of the person working. I can then use that for bonus payout calculations and analytic purposes.I have, within one base, the employee and shift details, as well as all of that sales data.Does anyone have an idea on how to approach this?thanks in advance!

I am trying to create a formal that adds a specific amount of days to a date field. The amount of days varies and is pulled using a lookup field. Initially, I was trying to use the DATEADD function but it doesn't seem to work when the added value is another field instead of a numeric value.The formula I have currently is DATEADD({Submission Date},{Health Plan SLA},"days")The field Health Plan SLA is a lookup field that pulls a number from another tab. I need this number to be what the formula uses. 

Hello!I have a list of records that all have a start date and end date column for a number of employees.  I am wondering if there is a way to calculate the number of employees on a given day.  All the employees have different start and end dates.  So for example I want to know the total working on March 1 vs March 7.  I know there is a way to do this in excel using an IF/AND formula but can't figure out how to do it in Airtable. Appreciate any and all advice!  

Hi there. I'm trying to make a custom auto number. The constant is kasse 00 and then want the autonember to be kasse 001, kasse 002, kasse 003 and so on. Can anybody send me in the right direction?And off cause will there be a problem when I reach kasse 010. It needs to be kasse 010 and not kasse 0010 best regards Thomas

I'm trying replace the following text with different values within [] with values from single select columns[art medium], [main objects], [attribute], [expression], [key light], [detailing], [camera shot], [media], [engine], [artist]I thought this would be fairly easy, but somehow can't get it to work.I tried a Substitute() like this:IF(Medium = BLANK(),SUBSTITUTE(Label, "[art medium], ", "" ),SUBSTITUTE(Label, "[art medium]", Medium)) &IF({Main objects}=BLANK(),SUBSTITUTE(Label, "[main objects], ", "" ),SUBSTITUTE(Label, "[main objects]", {Main objects})) But it subs the text and then renders the remaining text. Does anyone have any pointers how I best go about this?

Hey, I am looking for a formula field to extract city, state and zip code from a full address fieldThis is how a full address looks like "New York, NY, 10031". I want to split them 3 ways using formula, how can I do thatI used this formula to get the City   LEFT({Full Address},FIND(",",{Full Address})-1)   

I'd like to count how many linked records have occurred with another linked record. I have a table where each record is a contract between a company and a governmental entity. The company's column is a linked record and the entity's name is also a linked record. Shown below is an example table.(again, company and entity's columns are supposed to be linked record) As you can see, The company AF Engineering has two contracts with the Ministry of Electricity and one with the Ministry of Health. I'd like to find a way to count those occurrences. It can be from the Companies table, the Entities table, or the Contracts table (the one on the screenshot.) It sort of like the dictionary data structure in programming: {Ministry of Electricity: 2, Ministry of Health: 1}.  How can I do that?   

Hello,I have a rollup field with the following output:FirstName1  Last Name1- XXXX Title Company , First Name2 Last Name2 - XX Title CompanyWithout going back to the original table to pull the "XXX Title Company"  I need a formula to help split and display everything after " - " for each value. Any help is appreciated!

Dear Airtable + Apple Shortcuts Mavens,N00B, here with a N00B question.Using Airtable's API Encoder, I crafted a GET URL with filterByFormula.curl "https://api.airtable.com/v0/app[Redacted]/Items_Table?filterByFormula=%22%7BItem%7D%3D'D123'%22" -H "Authorization: Bearer [redacted]"The resultant URL works fine in CURL.When I store the URL (minus the Authorization bit) as an element in a DataJar Dictionary like this:https://api.airtable.com/v0/app[Redacted]/Items_Table?filterByFormula=%22%7BItem%7D%3D'D123'%22Shortcuts fails with a TABLE NOT FOUND diagnostic message.HOWEVER, when I store the URL as an element in a DataJar Dictionary like this:https://api.airtable.com/v0/app[Redacted]/Items_TableThe query works fine. The Table is found...except, of course, it returns all entries in the Items_Table, not restricted to item 'D123'.Baffled, I am.Any ideas? Suggestions? Thank you in advance for any assistance you can provide on this matter.   

Hi allIs there a way to delete the certain matching characters that might appear in a column? For example I have a column of data (800+ cells) of company names, and 90% of them are prefixed with a 'number' (that run between 1 and 9), followed by a 'period', followed by a 'space'. For example "1. Some Company Name" or "8. Another Company Name".10% of the data in the column may not have any numbers or spaces, OR some of the company names may not have a 'space' after the 'period' for example "7.Yet Another Company Name" or "And Another Company Name".Amy advice would be greatly appreciated.Stephen 

Hello-I am trying to roll up 10 date fields that are all deadline dates to a long text field in a bullet format.Ideally I would want the field name to be visible as well as the date like this (Field name/Date):- Designer due date: 1-2-23- Submittal due date: 1-15-23- Final proofing due date: 1-30-23I saw this from kuovonneIF( COUNTALL(ARRAYCOMPACT(values)), "• " & ARRAYJOIN(ARRAYCOMPACT(values),"\n• ") ) Thank you!Scott

I have a series of fields I want to concatenate together. The final result needs to include a semi-colon in between each piece of data, but not all columns have data. So I was working to do an IF statement to check if the last of the columns (far right column of data I want) had data, if so, concatenate it with the other fields. The formula I came up with (but is not a valid formula and I can't figure out why) is:   IF({Revenue Rolodex}, CONCATENATE({Revenue Rolodex},”;”,{VIP Day Social},”;”,{4M Event},”;”,{Applied to DID},”;”,{DID Calculator},”;”,{VIP Day Bootcamp},”;”,{2022 Advent Calendar},”:”,{VIP Day Quiz},”;”,{FREEBIE: Roadmap},”;”), IF({VIP Day Social}, CONCATENATE({VIP Day Social},”;”,{4M Event},”;”,{Applied to DID},”;”,{DID Calculator},”;”,{VIP Day Bootcamp},”;”,{2022 Advent Calendar},”:”,{VIP Day Quiz},”;”,{FREEBIE: Roadmap},”;”), IF({4M Event}, CONCATENATE({4M Event},”;”,{Applied to DID},”;”,{DID Calculator},”;”,{VIP Day Bootcamp},”;”,{2022 Advent Calenda

Hello everyone, I would like to create a form where people can add their event dates (event begin at 1st January 2023 and end at 2nd January 2023) and prevent the possibility of the end date to be before the start date (event begins at 1st January 2023 and end at 31 december 2022).To sum up I would like people to not have the possibility to make mistakes with their event dates.Thanks for your answers !