Formulas

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 planni

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'),

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 w

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

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&n

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 Yea

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?

I have 3 columes in my table formatted as such: A Linked record field + 2 look up fields from the linked record. (crossed out proprietary info) this linked record field shows all clients that been accepted by this playlist. The client may show up multiple times if they have been accepted by the playlist more than once or for different projects. This is fine. The problem then comes in the artist genre and genre/moods look up fields which reference multiselect fields from the a different table. The genre and moods fields will list multiselect values multiple times because of the multipile records that have been accepted by the playlist. I am trying to show unique values for these two look up columns, but cannot seem to figure out how.

Hey guys,I am trying to build a resource tracker for one of my departments based off their allotted hour for the week (or daily) and requested tasks that people input. We are hoping to be able to better schedule them by knowing how busy each person is on a given day. We're hoping to have a interface view that shows how busy each person is either each day or week in either a calendar or other view. Tables I'm working with:Request Tracker - where people from other departments can input requests and the department coordinator enters in the "Estimated Request Hours" (number field) for how long each request will take. There is also a "Request Due by" (date field) and a "Week Number Field". Personnel - has everyone in the department and their info including "Weekly Allotted Hours" and "Daily Allotted Hours" (number field) and is linked to the Request Tracker as "Assigned to". 

I'm rounding up a field called "Total Hours" using ROUNDUP(). Total Hours is a field that adds 2 decimal numbers together. The decimals numbers have up to 3 decimal places.My understanding is that ROUNDUP({Total Hours}, 2) works like this: it will round up to 2 decimal places if any number after the 2nd decimal place is above 0.Example ROUNDUP(72.001, 2) = 72.01                ROUNDUP(72.000,2) = 72.00The screenshot below shows that this is not what is happening for me.It's rounding up 72.000 to 72.01. In the screenshot, the error fields are circled in red. The original total hours are in the right most field and the rounded hours are on the left field "(Total Hours Rounded ,2)"Any help is appreciated for this.

Basically, I have a list of assignments. I need each assignment's due date, day number, and week number (along with other fields). I already calculated formulas so that when I update the day number, it automatically updates the other two "calendar" columns.  My problem is, sometimes I need to "insert" a new assignment before many that I've already done.  So, I need to push the rest of the day numbers/due dates up 1 day to match, but I'd like not to manually change every single day for the rest of the records (this could be hundreds of records to "push" forward).I've searched so many things.  I know it doesn't work like excel where I can do a per cell formula (the one above + 1), but I can't seem to find a way to do this.Also, FYI, there is more than one record with the same day number/due date because I have a record for each subject each day (sometimes more than one per subject). I'm attaching a screen shot in case that helps.

Hi everyone,I'm having trouble migrating data from Excel to airtable I hope someone can help me solve this problem:I am creating a app to manage a betting strategy and it has two tables:Bets table has the amount i have to bet in each round and Strategy table has the minimun bet of the strategy. The objective is that when I change the minimun bet in the second table the firstone updates automatically.In the bets table the structure is:Round: (0-30) identifies in with round am IRestart round: Identifies with an X the rounds when I restart the minimum betPrevios round: Links the round with previous oneNext bet: its a formula with the expressionIF({Restart round}="X",{Minimum bet},ROUNDUP((({Minimu

Hi all, I'm not sure if my aim should be achieved with a formula or an automation but I'm hoping someone can help me. I have a various payment schedules for different companies which show me the dates each one is due to make a payment - I upload these into Airtable.These payments may fall on a weekend. Where they do each company must make the payment either on the proceeding or preceding working day according to on what is stated in their T&Cs. I have a non-working day treatment field which indicates for each company whether it's meant to pay on the proceeding or preceding working day if the original payment due date is a weekend.How can I make a formula that looks at the payment due date and if it falls on a weekend changes the due date (add or minus 1 or 2 days) according to the non-working day treatment field? Alternatively, am I better off doing this in excel before I upload the payment schedule to Ai

Hi! I have two checkbox fields, column A and column B. In the column C, a formula, I would to write: Column A checked and Column B checked. I would like the result as a checked checkbox. Do you know if it is possible to see the result as a checked checkbox? Thanks in advance!

Hello community,I want a formula who counts how many successful candidates (the ones who gets "A" and "AAA") I had today.I was thinking about: IF(AND({Date 1st Interview}, TODAY(), {1st Round Bewertung}, "A" OR "AAA", COUNTALL())But I know the "or" command is on the wrong place.Can you help me? thank youu

Hello, I am trying to automatically update the "Bill Date" based on the date of incoming claims. The bill date is based on a start and end date and all three dates are recorded in a different table for billing schedule. How do I create a if claim date on my main table is between the two date in the billing table? In excel this would be simple, but having an issue with looking up on a different table in airtable. Thanks!

Hello! How are you?I would like some help with a solution I'm having difficulty finding answers for.I have a table and I need to generate a unique code (ID), but not based on randomness, rather on a relationship of information within the table itself.The ID in question should have a format similar to this: YYMMDD + BR + NumeratorWhere the numerator should differentiate between identical records, for example:Suppose on the date 23-08-08 there were 3 records. The code should then be: 230808BR01, 230808BR02, 230808BR03.Does anyone know how to solve an issue like this?