Formulas

Hi Airtable Wizards, I have the following formula which is formatted in currency and occasionally returns a -$0.00 result, where to my untrained eye, I cannot see why.  {PM: TOTAL BANK ALL EFTPOS}-{PM: TOTAL EFTPOS SALES}"PM: TOTAL BANK ALL EFTPOS" and "PM: TOTAL EFTPOS SALES" are computed fields that simply tally up totals from other columns. When both of the above columns match, it should return $0.00, which for some outputs it does, and others it computes as a negative.  Just wondering if anyone can figure out why?

I'm struggling to create the right formula and hoping someone here can help me: I have Field 1 as a single select. It's a list of brands with an "Other" option. Field 2 is a write-in field for when Other has been selected. I'm trying to create Field 3 as a formula field that merges the brand names—both the ones selected in Field 1 drop-down and ones written into Field 2. I have the second half working with ... IF(Field 1 = "Other", {Field 2}) But I can't figure out how to get the first part merged in. Whenever I try to add to the formula to make it add in the non-other names, I get an error. I tried a second IF formula, like ... IF(Field 1 != "Other", {Field 1})

I have a field called "Child_of" that links the child with the parent, both located on the same table.Since Airtable does not offer backlinking to the same table, I need a formula to know if the parent has a child. I do not need the backlink to be added to the parent record, such as through an automation. I just need to know if that parent has a child, in which case the formula could return the value 1. Counting the number of children would also work, returning the number of children in the parent record.I wonder if anyone could help me with that?Many thanks, 🙏 

So in my data column A is a created time date, column B has many lots, and column C has a birth date corresponding to each lot, so if column B is: 'LotA, LotB, LotC' then column C is, let's say, would be '03/28, 03/29, 03/30'.How can I add another column containing the Age where I get the following values '2, 1, 0' which is the result of the difference between the created date and the birth date for each different lot?

Goal: Create a list of email addresses that Accepted a Google Calendar Invite, a list of people that Declined, and a list of people that NeedAction.My Data: I have 2 text fields with my data, called "All Emails" and "Attendee Status" (grabbed via Zapier).All Emails = "email1,email2,email3,email4,email5, email6"Attendee Status = "accepted, accepted, needAction, declined, accepted, declined"In a new formula, I want to display everyone that accepted. It should print:"email1, email2, email5"In a new formula, I want to display everyone that declined. It should print:"email4, email6"In a new formula, I want to display everyone that needsAction. It should print:"email3"Any help is appreciated; i feel like i exhausted the docs and attempts. Thanks!

Can someone help me modifying this formula so that if the condition 'Trækdato (FarPay)' field is empty, the formula field is left blank? Right now it returns an #ERROR!  IF(AND(DATETIME_FORMAT({Trækdato (FarPay)}, "D")<10, DATETIME_FORMAT({Trækdato (FarPay)}, "D")<10), IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD"))=2, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD"),1, "weeks"), IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD"))<2, DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD"),2-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD")),"days"), DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD"),9-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Trækdat

I'm having a rollup field which is giving me the latest logged date from another linked table. The rollupfield is used in formular fields where it needs to show the date inserted into some text, but no matter what I do it gives me an error when I use the DATE_FORMAT() with the rollup field.  I have the following very basic script in my test field:  DATETIME_FORMAT({Confirmed Delivery Date},'DD/MM/YYYY')  And i tried to add an ARRAYJOIN(), however nothing seem to work.  DATETIME_FORMAT(ARRAYJOIN({Confirmed Delivery Date}),'DD/MM/YYYY')  Does anyone know where this can be going wrong?

I have tried multiple way, know I am missing something, but can't work out what. I have one column for Date and the next column is yes. Meaning that consent has been given and on the date in the column. I now need a formula field that will return the word 'valid' if the consent date is less than 365 days from today, and 'not valid' if the consent date is more than 365 days from today. Hope I have explained that well. I have so many good ideas, and I truly do try to work them out before I come here 🙂 Thanks in advance!

Hello amazing people! I am working on my film workspaces and trying to build a table to use for a Page Designer for a Call Sheet. I have several tabs I need to pull details from, all based on the Date Field. This Scenes tab is one of the places to pull from, and it has a Filming Date field.I am pulling IN to this Call Sheet tabWhat I need is if the Date on a record in the Scenes tab matches the Date in this Call Sheets record, I want the records to link. I will be using a number of associated lookup fields from the containing records.I assume I will need to use a Formula? I have trouble wrapping my head around those for some reason 😖 I've tried various other options and none will allow me to just say "If this date matches that date please

Hi everyone,I have a scenario one Make, sending infos from piwaa (linkedin connected inbox) to my airtableFor the module Airtable search records, I have a formula to help finding the record concernedI want it to find the right record to update thanks to my formula, -> linkedin url, OR if it’s not possible, firstname + lastname + company nameMy formula is not working and I can’t figure out why…Thanks a lot in advance for your help  

I have a column named MIN and need to add leading zeros when the value in column is less than 4 numbers:BeforeAfter Formula220022456045610000100008888888888999999999999999999This is what I have right now but is giving me an error for the largest number:IF((COUNT(MIN) <= 3), (REPT('0',4 - LEN({MIN} &''))&{MIN}), {MIN}))

If you look at my attached images below, I'm struggling with the CONCATENATE function. I want to add the COMPANY as a  prefix to each of the multiple items listed in the PRODUCTS column, but concatenate only adds it as prefix to the first item.I am doing this so that it will reference a price list. We offer the same products to each company, but each company has a different set of prices. I was going to figure out how to automate the copy/paste from PRICE CODE to PRICE, but I couldn't even figure out how to get the price code in the correct format. Thanks in advance for any help! 

Hi all,Some context: I have multiple bases with a list of tags that can be associated with records in other tables through a linked record field. If the users cannot find the tag they need, they can enter it as a new tag, but due to view permissions, any new tags created by these users will be given the status "requested" from a single select field. The single-select field can only be changed by an admin through a script triggered by a button, effectively "approving" the request. Since these admins will be handling requests from many various bases, I wish to create a common location for all of the requests, so they don't have to switch between bases to approve/deny requests. I first tried to accomplish this with an interface, but since the scripts don't work in the interfaces, this won't work. I then thought to go for a simpler solution: creating a synced table in a "master-base" with multiple source tables, listing the "requests". However:

Sorry if this is a basic question— I am just starting out learning how to use formulas with little/no prior excel experience.I have a formula that calculates the difference between today's date and the date a video was released. I have it returning the # of days: DATETIME_DIFF({Today}, {Video Date},'days') Is it possible that instead of returning the number of days, it could return # of years, #of months, #of days? So, instead of 952 days ago, it returns: 2 years 7 months 10 days? Thanks for any help.

I am trying to build a module in Make (Integromat) that will find a single Airtable record that matches an AND + OR condition:My formula is:AND( {NMI}="XXXXXXXXX", OR( {Subscription status}="Active", {Subscription status}="Pending", {Subscription status}="Extend"))If I create a dummy formula field in my Airtable  base and use this formula, it displays the value '1' (for 'True') in for the record I'm looking for, and all other records have the value '0' for False.When I use this exact same formula in my Search Airtable module in Make, it does not return any matches Any ideas on what could be happening here? I'm scratching my head...

Doing some work for an ATM Placement Company.When a client signs on, we have a set # of ATMs that we promise to place for them after they set up their bank account.Currently we are looking for a way to update a dropdown status based on a few factors (possibly a formula).We are currently tracking # of days since "Bank Account Opened" and are looking to automate a Green, Yellow, Red Status based on these factors....If we have not placed an atm within Days 60-90, they are a red statusif we have not placed an atm within Days 30-60, they are a yellow statusIf we have placed at least 1 atm between Days 0-90, they are a green statusIn one of the columns we have a formula to see if an ATM is placed or not. 1 means not placed, 0 means at least 1 atm has been placed. (Photo Below)

Hi there,I got trouble with using IF functions across Date field, currently I want my result is:IF({Date field 1} is filled, then "Completed"), IF({Date field 2} is filled, then "Extended"), IF ({Date filed 3} is filled, then "Waiting")How do I make this work? Thanks!

Hey everyone! I need help making a formula that gives me the date of the next coming Wednesday based on another date in a date field. So for example if the date field shows 3/4/23, I want the formula to return 5/4/23. Thanks in advance!

Why is array unique formula considering a single data twice? I have typed multiple topics and when I use the formula I can see topics repeated. Is there anyways to avoid repetitions?

Hello all, i'm fairly new to formulas and what not and need a bit of help.I'm trying to make a single select status field's Result show either "done" or "not done" based on a symbol in a separate formula i was thinking of trying use the IF functionThis is a rough rendition of it butIF(    {PM Time Left} contains this symbol⏳ THEN{PM Service Status} is "done" or "not done" based on the symbol) {PM Time Left}<---- this is my formulated field from other date inputs{PM Service Status} is "done" <---- This is the field i want to have formulated based on {PM Time Left} containing the ⏳ symbol  

Hello everyone,  Quick question, I am using the formula DATETIME_FORMAT(Date, 'dddd d MMMM') to add the day of the week in may table (easier to fill in for the team if they know which day i.e., Monday, Tuesday, ... it is).The issus is that the formula does not show the same value as the column date and loops between march 1st and March 6th.Any of you had the problem ?THANKS A LOT

I want to measure workload in a week. TABLE 1A) i use google calendarB) In airtable DATETIME dif give me the duration: 02:00C) The weeknumber is a formula week(Start) =  So i get Week 2022_5TABLE 2 This contains only weeknumbers: 2022_552 problems:a: When i link records, I can't link with the weeknumber 2022_5 because this is a calculated field (with a formula). If fill this manually in, the link is workingb: The duration is also calculated field DateTimeDif(end-start) but I cannot use this field in a rollup field. If i add manually activities duration, then this work...So, I come from filemaker and it feels a lack because i can't link records using Calculated Formulafields to link. Or is it wrong?  

Hello,I am looking for a custom formula to help me solve the following.Basically: If text in column A is true then add numbers in column B + 50 otherwise null.If {inspection} contains "oil change" then add {engine hours} + 50.0 The problem I have is finding the correct way to prevent all engine hours having 50 added to it so I can determine when the next oil change is due.Would this be possible?

Hi everyone, Thank you in advance for any suggestions you may have 🙂 I'm working on creating an always up-to-date Airtable that consists of records being updated in Podio. So within Podio, I have an app with a sheet consisting of people; my colleagues have access to Podio, so when someone new needs to be added to the database, they can go on Podio and add to the table. However, in Airtable, I want to be able to update the new records that are created as well as any changes being made. That way, I can ensure that any new data being added to Podio is also being reflected correctly in Airtable.At the moment, I have the simple scenario to 'watch items' in Podio and to 'create records' in Airtable. The thing that this does not take into account is the new data that has been added to Podio. Could someone suggest a scenario that will allow me to continuously update Airtable with the right records so that I can always have the most up-to-date information, even whe

Hi everyone!I'm new to Airtable so I need a bit of help.I have two bases with one main table named the same. "Students" and "Mentors"The "Mentors" base is published as a sync table in the "Students" base, and vice-versa.In the "Students" base, I have a list of students, and each row has an associated "Mentor" from the Mentors base.Now, on the "Mentors" base, I'm trying to count how many students each mentor is assigned to.I imagine I might need to use a formula, but that's beyond what I know how to do.Help? 😄