Formulas

I have a formula which calculates the # of days between two dates. I want to add if the resulting value is <0, then the value should be set to 0, otherwise, keep the value. This is the calculate of two dates. How do i add the second part? I tried nesting, but that didnt work, F(  OR({🚩Workflow Status} = "Complete",{🚩Workflow Status}= "Released to Customer"),  DATETIME_DIFF({🚩Actual Eng. Delivery Date},{🚩Original Planned P50 Date},'days')  ,"")

so  i have a table to thats shows a field with top 10 finishes which is a formula field that uses the below formulaIF({Amain Finish}<='10','1','0') what i want to do is create view that just shows how many top 10 finishes we have for each driver. so for example in this table data i have a total of 3 top 10 finishesI also have a field called drivers 2 only 2 options (Jeremy and Jaycie)can i create a view to show just the total of the top 10 finishes then sort by driver so Jaycie wouldshow 2 top 10 finishes (blue color on date) and Jeremy would have 1 (red color on date). Thank you for any help or assistance

I'll try to keep this simple. TLDR: How can I use a field in parent table to bound the results of a linked table rollup?I have three tables as follows:People- containing personal infoTasks-Task date and other pertinent dataGraphs-Start Date, End dateUsing integration with chart.io I want to graph various aspects that are pulled from the tasks table, BUT I want that data to be bound by the start/end date of the graph. I'm successfully using an automation to list all tasks (linked field) that fall in the date range, and then from that list another linked field to find all people that did work during that time. Now, what I'm trying to do (unsuccessfully) is use a rollup to count items by person based on DateStart/DateEnd (which have been converted to strings). The *problem* is that when I use "Only include records" it does not allow me to dynamically define that inclusion- I hav

I work in a digital archive and I'm using Concatenate to create a unique naming formula based on certain fields in a spreadsheet. However, some items have different naming requirements. What I would like to do is be able to tell this formula to skip over any empty fields and only pull the ones with data in them for the naming. I would additionally like a way to tie the dash "-" to the numbers so that only the correct number of dashes appear for however many fields there are in the name. Here is the formula I am currently using. CONCATENATE({Collection ID}, "-" , RIGHT("00"&{Box No.},2), "-", RIGHT("00"&{Folder No.},2), "-" , RIGHT("0000"&{Item No.},4))This will produce a name like "collectionID-box-folder-item". Ideally I would like it to be able to produce names like "collectionID-box-item" or "collectionID-folder-item".Apologies if this is a bit unclear, I am not very familiar with this kind of work. 

Can someone smarter than me help me creating this formula 🙂I'm trying to create a body fat calculator formula based on waist (cm) , hip (cm) and neck (cm)The calculation I'm trying to make as a formula (for women) is this one:And this is what I came up with in Airtable, but it says: Invalid formula:   495/(1.29579−0.35004*LOG({Waist (cm)}+{Hips (cm)}-{Neck (cm)})+0.22100*LOG(180))-450   For now I just want to test it and use it for myself, so I've plug in my height directly in the formula which is 180cm.In my table I have a field for each measurements.What am I doing wrong and how can I make it work?

Hello,I have a table with fields like this : The "First char" field is a formula that return the first character UPPER of the "First name" field.  Then, the field "check" is used to filter a view from A to E. So i need a formula that show "OK" if the "First char" field contain A or B or C,,,,,,,,,E or else it will show "KO"Any ideas how to do it ? Thanks.

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?