Formulas

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?

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:

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,

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

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",

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. 

Hi all! In one of my bases quotation marks are added to some of my IDs that are generated through a formula (someone explained to me that it because my titles contain a comma, but I need to keep it).I would like to remove those quotation marks. I've read and tried a lot of things, but it is not working. Here is my formula : IF({Nom pour identificateur}, {Nom pour identificateur}, Type) & " " & IF({Début}, {Début pour identificateur}) & " " & Projet I also add a screenshot of my base. Thank you very much for the help!

 I am in search of an AirTable data expert who can help me calculate percentage increases over each month from different records. I have a social media agency and use AirTable to store all our monthly analytics numbers and would like to determine percentage changes. I am in the midst of taking these numbers into my table and using the interface feature to create graphs to see it in a more user-friendly way. Please see here for a shared view of the table - https://airtable.com/appgifDfNJk8ia1a7/shrZ2KkZk6aMWcgGj Any and all help is appreciated. Please comment below if interested in helping out. Thank you! 

Hello All,I'm trying to create a formula that will create a comma-separated list of headers from checked checkboxes.As you can see below when I check one of these boxes I would like it to automatically populate the Achievement Rollup Field with the heading of that field and any others I check. 

What formula would I use to combine these two fields and get this result?Desired result - "3.69% | Increase"I tried this formula and the result is in % STATUS off…CONCATENATE({01% DIFF OFFICE}&' |'&{CHANGE TYPE})

I am trying to create a formula with this information: If a person who booked the reservation was 2 then it should apply the formula if it was anyone other number then the field should be blank or 0.- I have created a field for booked by and put number per person - I have created another field that has : IF({Booked By} =2, "Yes", "No") so this part works ok  - I have created a 3rd field that has:{Points Paid} * 0.04  This works ok So what I am unable to do is say - If booked by is 2 then apply the formula (at the moment the formula inserts for every number not just 2)..... I hope I am making sense. A

I've spent a fair bit of time looking and IF, AND, OR, SWITCH formulas but can't seem to figure this out for making three different options using two fields with the correct punctuation.I have two fields, {Full Name} and {Organization}.If there is just a name, I want 'Full Name'If there is just an organization, I want 'Organization'If there is both a name and organization, I want 'Full Name (Organization)'  This is the least complicated of all the attempts made which seems to work, but I can't figure out how to get the brackets to behave. IF({Full Name}=TRUE(),{Full Name},(IF({Full Name}=FALSE(),{Organization Name},{Full Name}&

Hi everyone,I'm working in a table in which each item has two potential "availability" dates. Each item might have an availability date "A" and an availability date "B", but they will all have an availability date "B". I'm trying to set due dates for each item based on a few variables. What I need is a formula that compares availability dates A and B, and then gives me a due date that is either 60 days out or 5 days out. If availability date A is before date B or 55 days or less after date B, I need the due date to be based on date A and to be 60 days before date A. If date A is more than 55 days after date B or is empty, I need a formula that gives me a due date based on date B and one that is 5 days before date B. In the midst of all of that, I need a formula that won't give me weekends as a due date. Does anyone know if this is possible?

Hi guys,I currently have the below in a grid view.I would like to have a formula in the "Timeframe Status" column which will do the following:1. Check whether text has been added to the "RFI Resolution" field.2. If the "RFI Resolution" field is not empty, then check the hours between the "Date Created" field and the "Due by" field. If this is less than 48 hours, then return the text "Within time". If it is equal to or greater than 48 hours, then return the text "Past due". I would like the hours to be shown decimal 0.003. If the "RFI Resolution" field is empty, then return the text "Ongoing". Again, I would like the hours to be shown decimal 0.00Thanks

Current Formula: DATETIME_DIFF({End Time},{Start Time},'hours') But if I have a "Day-Off", no date + time are listed, so on these line items they return NAN. If start + end date are blank, return 0 (Zero). Searching the community post, I have not been able to locate this formula. I would appreciate your help.