Formulas

Hello! We operate in an industry dealing with perishable goods, where the timing of receiving products (INBOUND) and the customer's requested shipping date (OUTBOUND) are crucial factors. We are trying to create a base structure/formula so we can keep track of inventory.Our primary challenge is to accurately determine the current inventory of raw materials on any given day. This inventory depends on the items we currently have on hand and any anticipated future deliveries of raw materials (INBOUND) for the upcoming week, provided they arrive before the customer's designated ship-out date (OUTBOUND Ship Date). Furthermore, our system must consider existing customer orders placed for the week and determine which incoming delivery of raw materials these orders will draw from.For example, suppos

Hello,I have a problem with a formula linked to a look up. I have a table with my banking transactions (linked to my bank) and another with forecast expenses. I did a look up to get the bank balance and I made a formula (Bank balance-expenses) for the first line the calculation is correct but for the other lines it does not give me the balance calculated above. Do you have any ideas to solve this problem?

I have these two fields Formula on Contract Yield % is this:({Contract Rent PW}*52)/{Purchase Price}I need to replace the formula in the Contract Yield, to an IF formula.More likely:When the result on Contract Yield is 0%,   If Contract yield = above 1%, use Contract yield formula, but if it's 0% I need to use Appraised Yield Can you please help me what is the correct formula to make it work?I already tried (IF({Contract Yield}=0,{Appraised Yield}but it's not showing the correct results

(Similar to this post though with an additional layer)I have a table with Interactions. There's a Status single-select field and a Timestamp field (since this data is being imported from elsewhere) that shows when the data was captured in the original system.There's a second table named Opportunities. All the interactions with one individual are linked to their respective Opportunity record. I need a field in the Opportunities table that reflects the Status of the linked Interaction record with the most recent / max Timestamp.The solution to the post I linked above pulls the max date, which would work if all I needed was the most recent Timestamp date pulled into the Opportunity record. However I need to know the most recent Status based on the Timestamp.Any thoughts? I may be missing a simple

Muy buenas tardes,Estoy montando mi propio gestor de proyectos, estos proyectos quiero numerarlos con números consecutivos, pero que el primer número sea un número en especifico.Por ejemplo:N. de Pedido: P003245 (Esté sería el primer campo de la tabla)Además, al crearse un nuevo pedido debe de aparecer automaticamente el número consecutivo (en el caso del ejemplo superior, P003246).Extras de funcionamiento:Además, voy a utilizar el apartado de formulario, para introducir los pedidos de una forma más automática y sencilla (no voy a ser yo la persona encargada, y se necesita sencillez). Mi consulta sería, ¿Existe alguna forma de que en el Formulario el N. de Pedido (automatizado anteriormente) aparezca de forma automática? Sin tener que incluirlo a mano.Muchas gracias____________________________________________________Hello,I'm setting up my own project

Hi, I'm new to airtable and am trying to get a simple conditional VAT formula to work. I feel like I am circling the solution but I'm not quite there. Essentially I am trying to track various hires and purchases for work, some of which have VAT and some do not. I have a Gross cost but I need the NET cost (without VAT).I currently have a VAT Field that is a single select field with options None and 20%. I'm trying to write a conditional formula for the NET Cost that is based on the VAT field and essentially works as follows:IF {VAT="NONE", Gross AmountIF {VAT="20%", (GROSS Amount/120)*100I keep getting invalid formula and error messages and I'm not quite sure what I'm missing.Any help greatly appreciated! 

Hello all,I have a problem that I'm trying to fix, and I have not been able to fix it with the posts with related questions.I have a field with numbers that I want to round up to another field with numbers. I'll explain the situation:So it's about ordering products. I get an order recommendation let's say for product A this is 55. But the products are packed in boxes of a certain quantity (let's say 20), so I cannot order 55 pieces, I'd have to order either 40 pieces or 60 pieces (2 or 3 boxes of 20 pieces).Is it possible to use a formula to automatically round the number upward to meet the required amount (in the examples this should be 60)?

I need help creating a formula.Basically I need this to do this:If there is an extension marked yes than the move-out date changes to how many day per the extend plus the move-out date. Ex if there is an extension original move-out date was 1/1/23 and there was 30 day extend the new date would be 1/31/23.If there is no extension then move-out date is move-out date. Ex: 1/1/23 is move out no extension then 1/1/23 stays the move-out. I would think this formula is it but it is not. IF({Is there an extension} = "yes", DATEADD({Lease End Date} + {If there is an extension how many days approved}, "days"), IF

I want to add a formula where I the cell value is equal to the row number. So when I move the records around, the cell value dynamically changes. Any suggestions on how I can do this?

The aim is to use a form to book seating for film screenings, however, the issue is that the form must show how many seats are available. I was thinking of possibly using a rollup to connect fields with the maximum capacity, the number of previously reserved seats, and thus a field in the form which would show how many seats remain available. I'm not entirely sure this could work, but I would appreciate any and all ideas to solve this!

I have a field which contains product names with measurement unit in the same field."Product Name 100grams" or "Product Name 500ml"I want to create a formula to extract 100grams or 500ml (these measurement units need not to be in the last place of the field.Is there any way to grab these units?

We have a table called Documents and I want to use a formula that pulls the name of the file in the {Attachment} field in order to provide the Name of the record. I've been using the LEFT({Attachments},FIND("(",{Attachments})-1) for this purpose and it works great, but I've now found that if the name of the attached file contains a comma, then the formula name adds a quotation mark at the beginning of the name. So now I'm trying to combine the SUBSTITUTE and FIND formulas to:1. Look at the name of the attached file, and if it contains a comma then replace it with a hyphen (eg. 'Ocala, FL' becomes 'Ocala-FL' )  2. Then use the name of the attached file as the name of the record without the URL at the end (using the FIND formula). Another acceptable option would be to just remove the comma from any

Hi there,In the last few months we been happy with getting an email on each new lead being created in one of our tables. The email contains all the needed data relevant for our sales team. BUT i'd really want that email to contain an attachment which is a .eml file (email) which is populated specifically with the records data. How can i accomplish this?Goal is to minimize errors when our sales rep copy/paste the data from the email. 

Dear Community, hope someone can help me out with this!The formula below indicates based on {Check in date} and {Check out date} how many months a booking of a rental apartment is actually in the current quarter. However in this formula, when the check in date is higher then the 17th of the month in gives one month less. So lets say check in date 17th of October and check out on the last day of December, it gives 3 months. Anything above the formula gives 2 months. However, this is not correct. For any date above the 17th of the month it should also give 3 months as the booking falls into 3 months of the quarter. Obviously if a new month start then it should give 2 months so 1/11 till 31/12 is 2 months. Can someone please help me write a correct formula for this?The formula below was created by Airtable support, but incorrect and they did not want to help me further! Thank you so much!!!ROUND

Hello, I have a table where I keep my website’s customers email addresses. I would like to create a random and Unique IDs for every email that I add to the table. Also, if the same email is entered more then one time, the ID should be the same as the first email. Is it possible to do something like it on airtable? Thanks

Hello, I am having a table in Airtable with Subscriptions. Each subscription record has its subscriber data containing the following fields:First NameLast NameAddressZip CodeCityBut each subscription can be assigned to a person more than once. So persons can have several subscriptions at the same time. I want to create unique IDs based on the subscriber fields from above (first name, last name, address, zip code, city). If there are subscriptions with the same person, the Unique ID (of the subscriber) should be the same. So basically I want to avoid duplicate people having a different ID. They should have the same ID.How can I do this with a formula? Thanks a lot for your help, Michael

Hi, I currently have this formula as part of a Task Days Remaining column: IF({Due Date}, DATETIME_DIFF({Due Date}, TODAY(), 'days'))Is there a way to update the formula... so that when another AT column (i.e. a task completed "checkbox" field) is marked complete, the value in the Days Remaining cell turns to a ✅... or even turn blank?The reason I'm doing this is because even after I mark a row (task) complete, the numerical value in the Days Remaining cell becomes a "-" number (eg. -3). So instead of seeing lots of - numbers, I'd prefer to have completed task cells become blank instead (or ✅ if blank isn't possible). Thanks in advance!Christopher 

Hello! I'm new to Airtable formulas & could use some much needed guidance! I need to calculate the total with an option of a discount if a multi-select answer is chosen. For context, this is in reference to grades of elementary curriculum books purchased. I am trying to create a formula that will show me:If Rural Discount multi-select = 'Small school (one classroom per grade level)' answer, then total # of grades licensed x $250 + additional invoice amount= total invoice costIf Rural Discount multi-select does NOT equal 'Small school (one classroom per grade level)' answer, then total # of grades licensed x $500 + additional invoice amount= total invoice costHere was what my closest effort was: Additional info:

I have a team that wants to send a weekly digest that shows updates only from the previous week. I found a formula that can show if a date is this week or not but can't figure out a way to show just dates from the previous week 

Currently I have a Date & Time (duration) field.  I have combined them into  The combined date & time formula is showing a different time zone it seems?  I want it to be Los Angeles.  Is there a way to do this?"COMBINED START DATE & TIME" FORMULA:IF( ({All Day Event}), DATETIME_FORMAT({Start Date & Time}, "MM/DD/YYYY"), DATETIME_FORMAT({Start Date & Time}, "MM/DD/YYYY HH:mm") ) 

Hi all, i need help form you 🙂i have 2 tables : - VariantTable -CostTableThe key is the SKU, this field is in the 2 Tables.I would like to add colunn in VarianTable with the CostPrice.How to create field with Automaticaly put the CostPrice when SKU is same.  Thanks for you help

If I add an attachment of an Image via DropBox, is there any way to see, use a formula, or API, to pull the actual DropBox URL instead of the Airtable URL which of course expires?

I have several single select columns with "yes/no" selection answers. I want to make a sort of if/than formula to combine the information from those yes/no columns. For example, in the photo below, there are columns a, b, c ,d, e. We will call those each "foods" for the purpose of the example. If someone says "Yes" to foods a, d, e but they say No to foods b and c, can I make a column that combines the yes data? I would like a formula field to show that the person has chosen foods a, d, e, just like how a multi select field looks. I have thought about using If statements, but I'm not sure if that will allow multiple answers into 1 column. I'm not even 100% sure a formula can give me the information I need. Does anyone have any input on this? 

Hi there, I have a repetitive formula field that I need to turn into a lookup / roll-up field in another table.Here is the formula field for reference. What are the steps I need to take to allow for this formula field to be a lookup field on another table?IF(AND(WEEKDAY({Go Live Date / Event Start Date (from Program Name)})=1, {Email Week}='Week 1'),DATEADD({Go Live Date / Event Start Date (from Program Name)},-25,'Days'),IF(AND(WEEKDAY({Go Live Date / Event Start Date (from Program Name)})=1, {Email Week}='Week 2'),DATEADD({Go Live Date / Event Start Date (from Program Name)},-18,'Days'),IF(AND(WEEKDAY({Go Live Date / Event Start

Hello,how can I calculate the weighted average? Example:[-4, 0, 2, 0] -> I get this values via a roolupI want the result to be -2 i.g.. Correct me please if I am wrong.I'm open to use multiple formulas. Best regardsJohannes