Formulas

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?

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". Project Tracker - all projects the requests are for live here. Is connected to Request

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((({Minimum bet}*2)+{Acumulated Loss})/2,0))Bet Amount: Brings the value of the next bet from previous round linked recordAcumulated Loss: This one I have the problem with. It should be a formula that adds all the bet ammounts of previous rounds and restart at the restart round but i ca

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 Airtable?

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?

I'm getting an invalid Switch formula, even though it's super simple.I have several columns of singular date fields. Each date field is a milestone. I'm writing a simple Switch formula to check if Today() = one of the date fields, then if so say the column's name.I've simplified this formula to it's most basic to evaluate against itself and I'm still getting an invalid formula. What am I overlooking?   SWITCH( TODAY(), TODAY(),"yes", "no" ) 

Hello Airtable Community, I hope all is well! I am having trouble with creating a running total formula/script. In the first table called "General Monthly KPIs" there is a field linking to the "Transactions" table with the dates, month and year, and the amounts of each transaction. There is a rollup field in the General Monthly KPIs table that calculates the monthly burn of each month. How can I create a running total field? There are thousands of transactions, so the 1-to-1 and 1-to-many method will not be applicable.  I tried scripting, here is my script: Which resulted in an incorrect running total:Does anyone know how to create a running total?I hope you all have a great day!- Ella

Hello, I'm trying to return the date in a field based on the urgency selected in another field. For example, if the urgency is "As soon as possible" the date should return today's date which I'm able to get with TODAY() formula. However, I couldn't manage to get next week's date with TODAY() + 7 as I would do in Excel. What is the correct formula would be for that? Thank you! 

Hi everyone,This is my first post to the airtable community forum so please excuse any rookie mistakes 🙂I'm having an issue with comparing two dates, I'm using the following formula...IF(IS_AFTER({Today's date},{Estimated Programme End Date}), "Yes", "No")Strangely, it seems for some records but not others.  Any ideas?Thanks, in advance, for your help.Kind regardsBenDATE COLUMN 1 FORMULA (Estimated Programme End Date)DATETIME_FORMAT(IF({Programme}="NQPC",DATEADD({Created Date},70,'days'),IF({Programme}="BTH/MIT",DATEADD({Created Date},42,'days'),{IM - programme end data (from Initiation Weekend)})),'DD/MM/YYYY') DATE COLUMN 2 FORMULA (Today’s date)DATETIME_FORMAT(TODAY(), 'DD/MM/YYYY')The comparison formula only works on occasion (row 1 doesnt work, row 2 and 3 do work, etc).  See screenshot below for sample output which shows the problem.  Take row 1 as an example of the is

I'm trying to find the average of a Ratings field for a product using a rollup.Based on the docs this should work: ROUND(AVERAGE(values), 2) However, I still only get whole numbers back. How can I see the result to two decimal places using a rollup?

I'm trying to do some math and get some totals of columns, but I have div/0 errors. The following formula calculates work days and prints the result from a "StartDate" and a "FinishDate" column.  DATETIME_DIFF(EstFinish,EstStart,'days')-(INT(DATETIME_DIFF(EstFinish,EstStart,'days')/7)+IF(DATETIME_FORMAT(EstStart,'E')>DATETIME_FORMAT(EstFinish,'E'),1,0))*2+1This formula works great, however when I do not yet have dates selected I cannot sum the values and having "ERROR" all over the view is kind of ugly.   I know I could probably create a view that hides them then they wouldn't mess up the calculations but I'm wondering if there is a cleaner way to do this that will not throw an error if dates aren't selected? I know if this was Sheets I would simply do an IFERROR and I know that AT has ISERROR I just haven't been successful in figuring out how to use it. Any help would be greatly appreciated. Thank you!😁

I created a master program plan in hopes to combine all of the project plans on my team, but I couldn't get the start date to auto-adjust based on the end date. My team used to use a spreadsheet in which we used this formula: =WORKDAY(H3,1,Holidays!$A$2:$A$10002) and this would auto-project how long the project would take based on the duration and previous task end date. When I tried to replicate a similar formula, it's an error due to circular reference. Is there a way to do this without having to enter the start date every time? (example: when a previous tasks took longer than expected the new end date should trigger the future start date to autoadjust based on the duration). 

Hi,I have seemingily a simple questions, I'm using airtable for the backend in my flutter app. I know that I can retrieve a specific result from my table, by adding the record id at the end of the url. Like this:/rec0PjvzbdWLP1sxMBut, how do I retrieve two or more results. Meaning, I have an array of records eg, [rec0Pj, recvzbd, recWLP. rec1sxM]. How can I make an api call to return just these ids?Do I use findByFormula, or some other way?Thanks

Hi, currently my team wants to have a form to submit warning for staff. A warning will last for 30 days, if staff violate within 30 days, it will automatically be their 2nd warning just by submitting formPlease help, i don't know how to set up the field automatically change from 1st warning to 2nd warning, or if they violate after 30 days, the old warning will be expired. 

Morning! I'm having trouble getting Airtable to recognise the output of this formula as a date. It is basically saying "if it's in this city/country, you need to begin the project x,y or z number of months before the required by date". The last "otherwise" was "" but I've changed it to -999 so the none of the formula would spit out a string value, but that still doesn't seem to have worked. The {Client Required By Date} field is a date field in the same table and is date only (no time), so that shouldn't be an issue, I don't think. Formula below:  DATETIME_FORMAT( IF( OR( {Town/City} = "Glasgow", {Town/City} = "Edinburgh", {Town/City} = "London", {Town/City} = "Liverpool" ), DATEADD({Client Required By Date}, -7, "months"), IF( FIND({Country}, "United Kingdom,Spain,Portugal,Republic of Ireland,Poland"), IF( AND( {Country} = "United Kingdom", {Bed Spaces / Units (all)}> 500 )

Hi I got help with this a little while ago and it was perfect but now I want to add to it and anything I try does not work.This is relating to an commissions payable per situation.IF(  {Booked By} = 2,  {Points Paid} * 0.04)The above works however I want to add more situations -IF booked by 0 points paid is 0.00 IF booked by 1 points paid is 0.03 ORIF booked by 2  points paid is 0.04 ORThanks in advance.