Formulas

I recently had to figure out how to get the fiscal year from a date field. Surprisingly, there wasn’t an explanation and ‘copy and paste’ formula already on here (or I just couldn’t find it). Here is my take on the challenge: To use this - Make sure you have a date field to reference labeled ‘Date’. Add 2 new fields, one labeled ‘Fiscal Year’, and the other ‘Year’. Make the one labeled ‘Year’ a formula field and copy and paste the code below into the formula area. DATETIME_FORMAT({Date}, ‘YYYY’) Make the one labeled ‘Fiscal Year’ a formula field and copy and paste the code below into the formula area. IF(IS_BEFORE({Date}, DATETIME_PARSE(CONCATENATE(‘06-30’, “-”, Year))), CONCATENATE("FY ", Year), CONCATENATE("FY ", VALUE(Year)+1)) That’s it! The end of the fiscal year in this formula is June 30th. If you have a different year end, change the ‘06-30’ in the Fiscal Year formula. I then hide the Year field and group by fiscal years. It is great for seeing historical trends.

Hi Airtable-Community A record is always automatically created in this table if the kick-off or planned completion date changes. At the top is always the data that was entered when the project was created. Below that, an entry is created every time something changes in the timeline.Now I would like to have an additional field that only shows the changes to the timeline.Example:Initial project timeline at creation: 01.09.24-31.12.24Change of the timeline to: 01.09.24- 30.01.25Displayed date field 01.01.25-30.01.25I would then like to display this in a timeline: At best somehow on top of each other so that you can easily see how the timeline changes.best regardsMirko

We have formulas to check character counts and they're returning with "over character count" for long text that is actually not over character count because they're adding characters for line breaks. Is there a way to adjust the formula to not include line breaks as a character?For example, this would return 338:Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce ac magna volutpat, pretium arcu non, ullamcorper erat. Donec dictum eros in tellus elementum sollicitudin. Morbi sit amet justo purus. Phasellus interdum eros eu quam euismod, sed efficitur lorem fermentum. Sed neque metus, dapibus at sapien at, pellentesque placerat ipsum. But the same text with line breaks would return 342 and if the character count formula were set to 340, would show as over cc:Lorem ipsum dolor sit amet, consectetur adipiscing elit.Fusce ac magna volutpat, pretium arcu non, ullamcorper erat.Donec dictum eros in tellus elementum sollicitudin. Morbi sit amet justo purus. Phasellus interdum eros eu

I am trying to write a formula to return the name of the column for each rows highest value. Attached is what my table looks like. So for example the first row should return blue, and the second row should return purple. Thank you!

Hello, I have a multi select field, with 5 different options that by person you can select 1 to all 5 items for.  I am trying to create a formula where it looks at all the people (say 20 people) and sees out of all of them how many have X as an option selected from this multi select field.  For Example:Person 1: Has XPerson 2: Has X,YPerson 3: Has YSo then If I am trying to show what the percentage is of people who know X, it would output _% Does this make sense?  Is this possible?

Hi, I'm trying to calculate the number of days until a date, however the result isn't correct.I think using the term TODAY is causing issues due to the difference of my time zone compared to Airtable's. Is there a way to add the time zone into the formula, or can I add/subtract days somehow?Here's the formula I am using;  DATETIME_DIFF( Date, TODAY(), 'days' )   

Hi guys! I was wondering if you could help me figure out why I get those texts in the progress bar field? How do I get the bar to appear at 0%?

Hi everybody,Let's say I've got 2 tables linked to one another (NUMBERS and OBJECTS)Table 1: 100 NUMBERS ranked from 1-100Table 2: OBJECTS which are randomly linked to these NUMBERS OBJECT A is linked with 6 random NUMBERS: 5, 6, 7, 11, 12, 13In OBJECT's there's a field {ranking} that says 11My question...In OBJECTS I would like to create a rollup field that shows me all the NUMBERS ranked higher then 11 according to the {ranking} field in OBJECTSIt can't be done with the "regular" condintional settings. So I tried to include a formula at the bottom of the Rollup field: IF(value > {ranking},ARRAYJOIN(values, ", ")) I was hoping to see:  12, 13 but i got #ERROR instead... Is there a way to include logical operators and field values within the formula of a Rollup field?Also... working with text fields instead of numeric fields leads to a new problem. Thats only works when the NUMBERS are linked in the right order. For instance if OBJECT A is linked like this: 7, 11

Hello! In my Inventory Tracking table, I want to name each item as: #Order Date-Product-Supplier. Everything looks good in my form when I'm entering the info, with the date format as expected, but then in my "Name" function field, timezone is included as "T00:00:00.000Z" I've tried reformatting the date field, deleting and re-adding, hiding it - nothing works to remove this extraneous info... any ideas? Formula looks like: (((("#" & {Order Date}) & " - ") & Product) & " - ") & SupplierI'm brand new to AirTable and appreciate any help. Thanks so much. https://airtable.com/appog8wBGoDN5Xiet/tblQuZFbeHjodwxpi/viwWiVK7K2wJqOX2E/fldnOc6mpaSiJv9t6

Hello community,I'd like to use a field from a different table in a formula.How can I do this ?--I have a table with a list of attending people to a specific event and how much they paid.I have a table with a list of the different events and I want to display the total payments received. Marine

I have a cell containing multiple dates.I want to have a column retrieving the most recent date from this cell.Which formula can I use? Tried with Max, or even last time modified but didn't do the trick...

Hi! I noticed the following bug in airtable. The formula that I actually paste in a formula field is not getting saved . Here is a proof of this.:https://app.screencast.com/8pYl20QKMc0fM?conversation=yInYcEQeuFQHhsuAgeFxWNIf you have a solution for this please let me know. 

Hi Airtable Fans, what I am going to ask will sound crazy, but bare with me :grinning_face_with_big_eyes: Sofar, Airtable has been a fantastic ace in my sleeve to build prototype and MVP (minimum viable product) applications faster than any of my colleagues can spin off a Java Virtual Machine. Saving, malaxing and serving data with Zapier and co. is absolutely fantastic. Some of those prototypes even landed “in production” and are still in use today because rewriting them “for real” wouldn’t have a high enough return on investment. Big THANKS to the Airtable team for that! Right now, I am facing the next challenge. The next prototype I’d need to validate a business application idea with, would require some kind of user- and user-specific-data management. And I have no idea if and how I could do with with Airtable. To make it easier to understand, let’s take the idea of a todo list. How could I create a distributed, mutli-user todo-list application with Airtable? Many users can add t

Anyone have any experience with geocodeing? I’m working with Airtable and Integromat to try to create a few integrations with location data that I’m collecting in airtable. I’m looking to be able to geocode or reverse geocode any record in a table. I have a webform that will detect and capture gps coords if available, or the user can hand enter an address. When capturing the data I would like to use integromat to to geocode or rervese geocode so that the database contains both gps and physical addresses. Another thing I’m hoping to figure out is how to do route planning, which I believe is possible through the google maps api, given a selection of addresses. Does anyone have any examples or experience here they could share.

Hi,Being European but from Nordics, I want to have our dates as d.m.yyyy. This is not a built-in format in Airtable. I have therefore used DATETIME_FORMAT(SET_TIMEZONE(date, 'Finland/Helsinki'), 'D.M.YYYY') in some places where user friendliness is more important than elsewhere.The problem is, dates formatted this way do not sort correctly in an interface. Here is an example image of that:'Is this a bug in Airtable, or am I doing something wrong?TIA,Björn 

Hi everyone,I have two bases in Airtable: "Employees" and "Projects". Previously, I managed everything in a single base, but I decided to split the data for better clarity. Now, I want to fetch data from the "Employees" table in one base and automatically add it to the "Projects" table in another base using the API.In the "Projects" table, I have an "Employee" field where multiple employees can be assigned to a single project (e.g., Project 1 has Employee 1 and Employee 2). I'd like these employees to be displayed as a list.I already have an API token set up, but I'm not sure how to fetch the data and display it in the "Employee" field so that they can be added to a project. Could someone guide me on how to do this?Thanks for your help!

Hello All,I run a real estate wholesale business and I'm looking to create a new table that will basically just tally all the calls we make every day. I would essentially like to be able to just click a button inside each record when a call attempt is made and have that data go to a new table, separated by date just so we can track daily calls.Any help would be greatly appreciated.

I'm setting up a ranking and league system for our tennis club. The ranking should work according to the following rules:1 – Players in each division will be arranged in order of the number of matches they have won in the round. Win = 3 points, Loss = 1 point, Forfeited/Defaulted player = 0 points.2 – If 2 players finish with the same amount of wins, the higher position will go to the player who won the head to head match between those 2 players (if played).3 – If 3 or more players have the same amount of wins, or if 2 players have tied but have not played each other, then the positions of these players will be decided by the most matches played. If this decides 1 or more of the players positions, but still leaves 2 players equal, the positions of those 2 players will go back to the head to head result.I'm struggling with a way to automate ranking for Rule Number 2. Has anyone done something similar?Table 1 = Players: https://airtable.com/appFy71oMkN3Qrb0G/shr95h5Ingg97O3szTable 2

Hey everyone,I need to find a way to list all the values from one column if those values meet certain conditions. Right now, I'm struggling to even find a way to summarise all entries from one column. The values in the column are email addresses, and the conditions are chosen by a single-select field.Just to clarify: I don't want to have one value/email address per line/record; I want to have all email addresses listed if they meet certain conditions.Example: I have a table with multiple records where I have listed all my customers. One of the columns in this table is an email address field with the customers' email addresses. There is another column—a single-select field—where you can select the status of that customer, for example, Status 1, 2, or 3. Now, I would like to have all email addresses from that table summarised in one field and separated with a comma, if the selected customer status is 1 or 2.I'm grateful for any hints I can get. ❤️

Is there a way to check a date constantly on airtable and if the date has passed label that record as missed?

Is there a UI to build a formula and get the resulting formula? The formula builder for creating views is useful, but I can't seem to find a way to get the formula out of it.Context: I'm trying to build a formula that filters records by 2 fields, an email field and a date field. What I have so far is: `AND({Notary 1 email (email)} = '${email}', IS_AFTER({Appt Date/Time (date)}, TODAY())` but the Airtable API request fails with a cryptic error: "The formula for filtering records is invalid: Invalid formula. Please check your formula text."

Hi there! I am trying to find a formula to bring a drop down option for KPIs I.E. IF Goals = "Gross Sales", THEN DROPDOWN, "KPI OPTION 1", "KPI OPTION 2", "KPI OPTION 3" 

I’m trying to use the Ceiling function to round a function result up to the nearest quarter, so 0.25. But my results are only rounding to the next 0.10. How do I get it to always round up to the nearest .25, .50, .75, .00? It works like this in Excel but can’t seem to get it to work in airtable.

I can't believe a very simple feature in excel/google sheets is missing here in formulas when it comes to subtracting two numbers and if it's negative - show the number in one color, and if it's positive - show the number in another color so I have a pretty simple setupField 1:  Cal+/-Formula is: 2300 - Cal   results will be a number Less than 0, 0 or greater Formula is:  If({Cal+/-} < 0, ??, ??)What do I use in the ?? to show less than 0 in red  or 0, greater than 0 in green  

Hi,I tried using != Blank() and NOT(Blank()) on several occasions for my formulars and have been getting different results. I'm not sure why.Is it because only one of the two works and the other is ignored by my formular? Or does it depend on the format that I am trying to evaluate (dates, integers, strings)? Any help on clarifying this would be greatly appreciated and prevent mistakes in the future.