Formulas

Hi Everyone,I'm new to Airtable and formulas and have a pretty simple formula that is displaying a hyphen as a result of a calculation of zero, instead of the number 0. I admit I borrowed the formula originally from one I found on here, but not sure why the number I've designated isn't being displayed.It's not a true numeric calculation, but a replacement I'm using to give a numeric value to a field that also has text for ratings, i.e. 3 - Exemplary, etc., so that I can total up all the ratings from different categories and assign a value.Here's the formula:    IF( {Field1} = '0 - Deficient' , 0, IF( {Field2} = '3 - Exemplary', 3, IF( {Field3} = '2 - Accomplished', 2, IF( {Field4} = '1 - Developing', '1', 'Out of range' ) ) ) )    I personally have no idea why the formula shared was nested and if that has something to do with it, but everything else about the formula is working. It's displaying like this in the table and the interface. 

Hello All,I need to migrate all of my records from an old base in to a new base.  The records in the old base have been assigned a number (through Auto ID plus a formula).  We are leaving the old base behind, importing all of the records in to the new one base, and resuming work in the new base.  I am trying to figure out how I can have Airtable resume number sequencing from where we left off in the old base and continue in the new base.  The new number sequence does not have to start exactly where the old one left off, and i cannot have the Auto Number feature assign a new number to existing records that I have imported.For example, we use Airtable to create and track projects.  Whenever a project is created, we have a summary field formula that takes the Auto Number (in this example 50011) and joins with with the prefix PR- (making it PR-49998) and also joins it with the project name (again in this example it is Organic Social Retainer - TEST).  The end

I have multiple values in two separate cells located within the same row. My objective is to subtract the first value from the "Stock" column from the first value in the "Quantity for Production without Units" column, and continue this process accordingly. Finally, I would like to place the result in a third column.So it would be something likeEstoque          Quantidade para produção sem unidades          Estoque-Consumo46200                              6000                                                        

Hello Community, please help to solve in an elegant way!I have record, and in a field called "FOOD" I have text separated by comma, let's say in the text I have"FOOD" = Apple, Carrot, sausageI then have another table caleld "FOOD CATEGORIES" with all the food separated by categories like (Can be a table for each category, same situation):Apple - FRUIT Banana- FRUIT Mango- FRUIT Carrot - VEGETABLEZucchini - VEGETABLEPotato - VEGETABLESausage- MEATBeef- MEATChicken- MEATIceCream - DESSERTCheescake - DESSERTCandy - DESSERTSo now I want that in the field next to the field "FOOD" will be a field called "FOOD CATEGORIES" where it will appear the food categories according to the table classification, the previous example, I want to see CATEGORIES FRUIT, VEGETABLE, MEATAt first I tough I could do with a super concatenation of IF, but seems not very elegant approach.The real challenge is that in the field food, could be a lot of different items, so I want that the match will be d

Hi Airtable community, first time posting here. Thanks for your help in advance.So, I'm trying to create something like YC Startup School weekly update forms: every new record will be named as "[Monday of the week of {created time}] - [Sunday of the week of {created time}]". Just like this:I'm very new to using formulas, but would really love to learn how that works.Tried "Weekday", "Dateadd"Got stuck right here. Thanks a lot! 

Hi !I'd like to substitute the "€" symbol only when it's alone in a field. (alone but with a space in front)For example, I've sometine a field with :1.00 €2.00 €€€€and it's very ugly in our contract.  ><I thought at the "LEN" and "FIND" formulas but I struggle to not replace the € when it is accompanied with a number... It would require a formula that only searches and replaces € symbols when it is alone with a leading space.Any ideas will be greatly appreciated :-)))) Thanks !

Hi .. Hi. How can I show 'next week day' . I know.  DATETIME_FORMAT(Date, 'dddd'). but anyway I can how 'next Monday' ?

I could not find a solution for this in the forums: From a list of dates, I need to find that date that is the last date on that list before the launch date of a given project. The idea is to find the date best suited for an event to happen before a project is launched.Full description:Records in table "Status" are each linked to one specific "Project" and one specific "Outlet". "Status" looks-up the launch date of that project. In "Outlets", multiple dates are rolled-up from a table called "Release dates".I am looking for a formula that returns the one "Release date" that is closest, but not after the Launch date of the Project. The returned date must be in the "Status" table.I cannot use conditional look-up, as the date I need to compare with is not relative to today, but referenced elsewhere. So no way to use a relative condition like "before today" or so.I have transformed the dates in to Unix timestamps already. Mathematically, the date to be returned is the minimum positive value

I need to get the price from another table and bring it to my booking table. The conditions must be based on the company and the route to bring the correct sale price registered in the Routes Price table,My airtable system is already working and I use the Company field as a lookup for the Customers table and the Routes field is just a single select.However, today, to form the sales price, I use the supplier's cost price, but this needs to be changed and the sales price needs to be added manually in another table to later appear. If customer is A and route is A show this price If the customer is B and the route is A show this price.

I have no idea how to make the title succinct and easy to understand, but here's what I want to do. I run a market garden. I am building a crop planning tool inside airtable. Throughout the year, the typical timings of events for different crops will change.For this example, we'll use my Weeks to Harvest calculation. In peak summer, lettuce will be ready to harvest 4 weeks after transplant. In the darkest part of winter, it'll take 8 weeks.Now, there is not a cut off date where suddenly it goes from taking 4 weeks to 8 weeks. So I want to be able to store the following data against a crop1) Fastest weeks to harvest (peak summer) - e.g. 42) Slowest weeks to harvest (dark winter) - e.g 83) Fastest date of the year (based on transplant date) e.g. Jan 14) Slowest date of the year  (based on transplant date) e.g. June 1And then I want to make a formula field that will adjust the weeks to harvest based on the time of year of transplant.&

I run a table that keeps track of content my team creates for a digital billboard. When we hear about new content, our project manager builds out a production schedule based on the date the content is supposed to go live on screen.To help my PM with scheduling, I used formulas to populate some milestone columns (initial concepts due, kickoff to production, multiple creative reviews, etc...) with some estimated days for each step in the process. For example: Project Kickoff should occur about a month from the start date and uses "WORKDAY({Start Date}, -33)" while the File Handoff date is only a week before launch and uses "WORKDAY({Start Date}, -5)" So far, this has worked great for providing some rough dates during the initial project planning stages, but we run into problems with our calendar when real world events force dates to change. For example: If the PM knows that a certain executive will be out of office on the formula-generated milestone date for a creative review,

Hello,I would like to create an RSVP form that can limit the number of participants. Is it possible?

Hi,I'm trying to determine how to display the difference between two dates as years, months then days.I have a start date and an end date, (the moment a worker started in the company and the moment he ended his contract with us), and I need to know how to have the result like "1y,3months,15days" or something like that, I found a formula that gives me the result in weeks and days, but a week always have 7 days, but a month can vary the days (28 days, 30 days, 31 days), so I am kind of confused

I'm using the WORKDAY function and it's skipping the weekend as it should, but it always returns the previous Friday. Is there a way to have it return Friday if the date falls on a Saturday, and return Monday if it falls on Sunday?

"I am conducting research to monitor the depression status of patients. It's crucial that I track their status on a weekly basis. Consequently, I am looking to set up a reminder calendar to facilitate this. I've utilized the provided formula to determine follow-up dates, but Airtable suggests that the outcome isn't recognized as a date format. This has hindered my efforts to create a calendar using the results. I would greatly appreciate any advice on this matter. Thank you!"   IF(    {First visit},    DATETIME_FORMAT(        DATEADD({First visit},1, 'weeks'),        'MM/DD/YYYY'    ),    "")

Hi, I'm working on a way to consolidate a base used for a diaper program. We're a nonprofit and need to track for many different reasons, but our current process is long and requires toggling from table to form to table. Here's what we're doing (all are in separate tables):Entering donation inventory via form (an individual count of diapers, wipes and formula)Receiving requests via formReviewing the requests and inventorying out the items via form (linked to step 1)Here's what I'd like to do:Inventory donations via form (needed because amount spent is required for tax purposes)Link the request and inventory out form to subtract from inventoried donationsIs it possible to deduct (2) items at once or do we have to inventory out each item to properly subtract?Essentially, all three tables would be linked together with the inventory out and request forms "combined" to avoid exiting the table when fulfilling the requests for delivery or pick-up.Is this possible? I feel like it is, but I'm n

I'm using the formula below to calculate the date difference between the day a position was opened to today (tracking number of days the position is open until filled). Is there a way to override that formula if the Position Status is listed as Filled or to make the formula only run on Positions with the status as Open. I don't want the running count of days once the job is filled, but don't want to lose the data point of the day it was opened.DATETIME_DIFF(TODAY(),({Open Date}), 'days')

Hi there,I am trying to set up an OKR table in Airtable.  I have two tables in my workspace.  One for Key Results and one for Initiatives.  Under initiatives I have a column with tasks just check off as they occur.  Please see below.  How do I create a column with a formula that will calculate the % completed based on the check marks listed in the task column.  I have tried to create the formula with creating additional columns (total task count and total task count completed to calculate off of but that didn't work).  I used the "countall tasks" in the total task column and tried to create a formula in the total task count but it isn't working.  Any guidance would be greatly appreciated.     

I created a table called Items (household items).  In Items, there is a partially filled-out field, PurchaseDate, that is some items have a PurchaseDate; other items don't have a Purchase Date (field is left blank).  I want to run a filter of those items with a blank PurchaseDate:  Is the best way to do this is to create a formula field, named noPurchDate: LEN(DATESTR({PurchaseDate))=0 such that if the PurchaseDate is not empty, noPurchDate returns FALSE, and if PurchaseDate is empty, then NoPurchDate returns TRUE? Is there an easier way to get this result? Another more "normalized" way of handling this might be to create a separate table, PurchasesWithDates linking Items to PurchasesWithDates.  How does one create a filter of Items with no link to PurchasesWithDates? 

Hey there!I'm migrating some data and processes from Google Sheets to AirTable as it is far more responsive to the frontend of a portal I am developing for my company. Right now, I'm using AirTable as the backend for the quote generator we use for our events. I'm looking to do the following:Have a set of Items that may be changed (Ex: Min/Max Price Per Person, Event Discount - Percentage Value, Weekend Markup (A True or False value set if the event happens on a Saturday or Sunday), Amount of Raw Materials that will be priced using fixed amounts based on what I mention in Section 2)The Price Per Person will go up a fixed $ amount of $40 if the Weekend Markup is set to TrueHave a set of prices that will virtually never change (Cost of Instructor, Cost of Safety Officer, Cost of Raw Materials)Generate a range from Minimum to Maximum price per person to be quoted withinGenerate an "Addon" value of additional costs (Being the total of applied prices that will never change)Create a Final "Re

For my salesperson's airtable,  He needs to change the name of a few companies. He is unable to do that since it is a formula field. How can he undo the field from being a formula field and just a normal field so he can make edits to the name of a few companies?

Quick one, I use date_add to calcuate a bunch of dates in different field. Now, I'm realising that even tho I use "Datetime_Format" airtable can't easily interpret those as dates if I want to sort by them or make timelines etc.Here is an example of a date formula I use.IF({Target 1st Harvest Date}=BLANK(),IF(  AND(    IS_AFTER({Target Sowing Date}, '10/01/2023'),     IS_BEFORE({Target Sowing Date}, '04/01/2024')    ),  (DATETIME_FORMAT((DATEADD({Target Sowing Date}, (1*{WtH}), 'weeks')),'DD MMM YY')),  (DATETIME_FORMAT((DATEADD({Target Sowing Date}, (1.5*{WtH}), 'weeks')),'DD MMM YY')))  ,(DATETIME_FORMAT({Target 1st Harvest Date}, 'DD MMM YY'))) Here's another DATETIME_FORMAT(  (DATEADD({Target Crop Out Date},&nbsp

Does anyone know how to get the GCD between two numbers in Airtable? Excel formula = GCD()

Hello,I'm a beginner when it comes to using formulas. This is my first time using a formula with conditions. Im looking to display a message when employee's reach our milestone years of service to the company. I know Im close, but its not working. Can someone tell me what am I doing wrong here? Here is my formula:IF(  OR(    {Working Years} = 1    {Working Years} = 3    {Working Years} = 5    {Working Years} = 7    {Working Years} = 10  ),  "🎉 Announce on Social Media")

How would I display the sum of this column and make it such that every time a new record was added the sum is updated with the new information so that it is usable information on softr?