Formulas

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. 

Hello Airtable Community,I'm new to writing code and am struggling to combine two different formulas into one. I have 4 columns I'm working with - Due Date, Days Until Due, Date Posted, and Priority. I want the Priority Column to output "Due Today", "Due This Week", "Due Next Week", "Upcoming", "Overdue" or "Complete" based on the number of Days Until Due and the Date Posted columns. I have a formula working that will output "Due Today", "Due This Week", "Due Next Week", "Upcoming", and "Overdue":IF({Days Until Due} < 0,'Overdue',IF({Days Until Due} = 0,'Due Today',IF({Days Until Due} <= 7,

Hey all, Looking for a custom script/formula, happy to pay if required. i have a table full of records that are daily jobs. Each job has a value and I want to be able to show a total of all records that match 2 fields (customer and week number). The result should land in 'Total This Week' and the record 'Total in Matching Week (Expected Answer)' gives the expected answer.Hope it is clear but ping me if not. Have tried using various scenarios of roll ups into other tables but cant get it.https://airtable.com/appqWVJT26DBHjzDO/shrXA4B7gcO7XWTiCAny help would be massively appreciated, really get me out of a pickle.  R   

Hey there.I'm trying to get the duration of an activity by calculating the difference between 2 timestamps.These timestamps are the results of a formula that shows the time as a box is checked at the start of the activity and another, at the end. The formula in Start Time and Finish Time fields are:IF({Start}=1,DATETIME_FORMAT(SET_TIMEZONE((LAST_MODIFIED_TIME({Start})), 'America/Sao_Paulo'),'H:mm'),0)IF(Finish=1,DATETIME_FORMAT(SET_TIMEZONE((LAST_MODIFIED_TIME(Finish

Hello! getting lost in the sauce of nested IF formulas and looking for help.I have a CRM in airtable that I'd like for a formula to return one of two formulas based on a single select status for that deal.If the deal is open (any one of 7/10 statuses in the single select), I'd like for it to show the deals age based on today's date  DATETIME_DIFF( TODAY(),{Deal Created}, 'weeks')  If the deal is closed (any of 3/10 statuses in the single select) I'd like for it to show the age of the deal when it was closed:  DATETIME_DIFF({Date Closed or Lost},{Deal Created}, 'weeks')  Thanks for your help!

I am creating a record sheet with computing the monthly accrual of 1.1667?