Formulas

It’s pretty simple, but I’m new to this. I need a column that changes the age with the date of birth. So I know I have a columnn called AGE which should change when the child reaches a birthday in a roster. Any help so I can copy and paste a formula would be appreciated. Thank you!

Hello Guys! I need some help here, I am using javascript fetch call to get values from airtable, I want Specific field Values using filterByFormula filter… I want only those fields where field name {TaskDate} = CurrentDate and field name {FK_UserUsageID} = PK_UserUsageID… I see many topics about filterByFormula but i can’t understand them because of the high level formulas using in them. I try code: fetch(airtableURL + baseInfo + tblSchedule + apikey+"?filterByFormula=AND({TaskDate} = “+ CurrentDate +”,{FK_UserUsageID} = “+PK_UserUsageID+”)") .then(res => res.json()) .then(res => { console.log(res) }) but response is: Object { “error”: Object { “message”: “Authentication required”, “type”: “AUTHENTICATION_REQUIRED”, }, } I also use my apikey and all that needed. Please figure out my mistakes and tell me the solution. Thanks in advance…

I am brand new to Airtable. I would like to compare a product grade to a supplier grade. A Supplier with an “A” grade can make “A”, “B”, “C”, or “D” product. A Supplier with a “B” grade can make “B”, “C”, or “D” product, and so on. I am pulling from two different tables; the product grade from a product table, and supplier grade from a supplier table. This third table would simulate a Purchase Order, where a single supplier may be making multiple products. I was able to create a formula to check if the supplier is allowed to make that part, however if I add multiple parts, the formula doesn’t work. Is there a better way to approach this? IF(AND({Product Category}=“A”,{Supplier Category}=“A”),“OK”,IF(AND({Product Category}=“A”,{Supplier Category}=“B”),“Supplier is not qualified to Manufacturer this part”,IF(AND({Product Category}=“A”,{Supplier Category}=“C”),“Supplier is not qualified to Manufacturer this part”,IF(AND({Product Category}=“A”,{Supplier Category}=“D”),“Supplier is not qua

Hi! I’m trying to backtime from our due date for some reviews. Here’s the formula, which is working fine: IF({Real Time End Date},DATEADD({Real Time End Date},-3,‘days’)) But I can’t seem to figure out how to add in the WORKDAY command so I am able to skip days off to determine the resulting deadline. Help? Thank you!

Dear all, A person, completely zero at coding here. Please help! Made a database for all the company’s contacts. Have a date field “Birthday” which is in a Date format (but with an actual year of birth) (I dont have birthdays for all people by the way, so sometimes this field is empty) Then used a formula (see below) to change the year of birth to the current one so I managed to create a calendar view for birthdays. DATETIME_PARSE( DATETIME_FORMAT( Birthday, ‘MMMM D’ )& ’ '& YEAR( TODAY() ), ‘MMMM D YYYY’ ) But now I need to make an automation that would send an email to me if there is a birthday coming 2 days from now. Ideally to look it like Reminder! “Name Surname” has a birthday on “date of birthday” I tried to use a trigger when “formule to calculate birthday” (it is a formula field) is within 2 days and action “send an email” But it doesnt seem to work: the automation only sees one person instead of several and doesnt seem to be able to collect the date of birth correctly

Is there a formula where I can convert a date in a date field to a string of numbers? For example, for today (2020-11-20) I would like it to return “201120”

Hi all!! I have a list of participants in a project, but as this list of participants is a lookup from several tasks linked to the project, it has duplicated items. I want to count the number of unique participants. I’ve tried transforming this list of participants into a string, I know how to count the number of participants in that string but I need to remove the duplicates. Does anybody know how to remove duplicates from a string? I know how to remove completely a participant from the string (using SUBSTITUTE), but I don’t know how to make it appear only once. SUBSTITUTE({Participants}, Find, “”) (Find is the name of the table with the name of the participant I want to remove) Thanks in advance!!!

I’m not sure the best way to ask this question, but here goes! I have the following formula… Model & " " & VAR_Color & " " & VAR_Type & " " & VAR_Weight_Name & " " & VAR_GRIP_Circumference Some of my “Models” are missing one or several of the VAR (fields) and when that happens I’m left with a double or even triple space. I want to concatenate the formula in such a way that IF a field/VAR is empty that the VAR and the space in front of it are omitted, but if a VAR exists, then the VAR and the space in front of it are displayed. Thanks in advance for any advice on how to revise/simplify this formula!

I need a formula to round seconds to the closest 15 second mark in the hh:mm:ss time format… i.e. say 1:01:10 needs to be rounded to 1:01:15 , say 0:34:20 rounded to 0:34:30, say 1:02:55 to 1:03:00 Hope someone can help as in excel I can use the MROUND formula but this is not in Airtable.

We have been running into a snag with the simple formula of 3 columns adding together and getting off by .01. This normally is not a big deal, but in a contract this can be a very big deal. I have attached the pictures of what I’m talking about and the formula I use.

I hope that this is not a topic that has been answered already but in my searching what I found is that there are several ways to filter out empty fields but in my use case I want to keep the records that are empty in certain fields. I have had success using NOT({Field Name} = " ") and some success using IF and SEARCH in various forms but they are all getting rid of the fields that are empty rather than keeping the ones that are empty. For clarity’s sake let’s say the records have a likes icecream and the people who don’t like icecream have nothing in that field. I want to be able to filter out the ones that do have something and keep only the ones that don’t. If anyone has any suggestions it would help a lot (also I am doing this via API).

Hello - I’m looking for help creating a formula that will insert quotation marks around names in a string that are separated by commas. The number of names in the string will vary. For example the string Jane Doe, John Smith would become “Jane Doe”, “John Smith”. Is there a formula that I can use to do this?

I am using an Airtable base for Billpay synced with Calendar view. I am having trouble setting the date to recurring. For example, X bill is due on 10/1/2020, I need it to sync with the calendar so that the bill appears on the 1st of every month.

Hi I am not sure it is an IF formula or a switch formula that I need but I cannot work this one our for love nor money. So I am trying to write a complex risk assessment and within it is a simple single select column where my options are Improbable Remote Possible Probable Almost Certain What I was hoping to do is have a column next to it that converts the different options into numbers 1-5 (which I would then hide but use in calculations.) There are lots of different versions of this within the base but for the life of me I cannot work out how to take the select options and get it to return a number am I going crazy? Any help greatfully recieved xx

I’m trying to automatically name experiments based on the user that performs them. The numbering is sequential. However, I want the numbering to relative to the user. Here is the formula I’m currently using: {Initials (from Owner)}&"_"&"EXP"&REPT( '0', 4-LEN( {Autonumber}&'' ) )& {Autonumber} The issue is, I want that next experiment (MP_EXP0005) to start at MP_EXP0001, rather than 5. Is there a way to do that with a formula?

Hey all, I have a formula to generate a date and then format it, here’s what it looks like: DATETIME_FORMAT(DATEADD({Touch Point}, (SWITCH({Coin Type}, ‘Platinum’, ‘3’, ‘Gold’, ‘6’, ‘Silver’, ‘12’, ‘Copper’, ‘Null’)), ‘months’), ‘MMMM-YYYY’) My {Touch Point} field however sometimes may not have a date entered. In situations like that I want the formula to return blank (Null) as opposed to ERROR. Any ideas?

Would appreciate some help on IF statements: I have a field for “WEIGHT” and another for “WEIGHT RANGE”. I would like user to enter his/her weight and need a formula to yield the weight range. eg 42 >> 40-45 48>> 46-50 52>> 51-55 56>> 56-60 63>> 61-65 69>> 66-70 Would there be better alternatives using AND or SWITCH or anything else? Thank you.

There is not actually much to say here, but I am adding some text in order to pass the minimum for a forum post. It would be great if there was a setting to close/minimize airtable to tray or taskbar in Windows 10

Hello! I am admittedly not a formula wizard at all. Trying to create a formula that adds 3 days to a manual date field but excludes weekends. By definition, I thought the Workday formula removed weekends, but my formula is giving Sunday dates. Do you have to designate if your workday starts on Sunday vs. Monday to fix this? If yes, how? Next Expected Dispatch Date is a date field manually selected: IF({Next Expected Dispatch Date},WORKDAY({Next Expected Dispatch Date},3),BLANK())

Hi everyone, I’m really sorry for that (probably) stupid question but I can’t get a valid answer to my issue by searching alone ;( I would like to be able to use a formula to replace : “https://pbs.twimg.com/profile_images/1171057411806961666/jyWUG6oT_normal.jpg” by “https://pbs.twimg.com/profile_images/1171057411806961666/jyWUG6oT.jpg” I just want to remove the end of the URL because the formating of the begining could change. Thanks for your precious help :pray:

Hi there! I am needing help to add ONE layer to this IF formula. It’s for an invoicing tracking base. Overall, we are looking at 2 columns. I found this super epic IF Formula here on the forum and need help. The OUTCOME: *This first part I need help with - If the Paid Date exists = PAID (the rest, the formula I have works) If the Invoice Due Date is same day = DUE TODAY If the Invoice Due Date is within 5 days = PENDING If the Invoice Due Date is 5+ days = UPCOMING If the Invoice Due Date is past = PAST DUE Here is a pic of the columns I am working with Here is the formula I am working with, I am fairly sure I need to change the first layer?? IF ( {Due}="", “Not Set”, IF( AND( DATETIME_DIFF({Due},TODAY(),‘hours’) >= 12, DATETIME_DIFF({Due},TODAY(),’days’) <= 5 ), “Due Soon”, IF( IS_BEFORE({Due},TODAY()), “Past Due”, IF ( IS_SAME({Due},TODAY()), “Due Today”, “Upcoming” ) ) ) ) Thank you in advance for your GENIUS!!

Hi. New to Airtable. Looking to add a formula field that calculates a value (rating - via stars) based on data in other fields. In the attached example, I’d like the ‘Expected Rating’ field to return a star rating (via star emoji) based on the completeness of the other fields.

Hey there, trying to get an IF/THEN statement correct here. It’s a donation database. Each donation is linked to a Donor Account and a Campaign. In my Donor Accounts tab, I’m using a lookup field to determine which Campaigns each donor has donated through. In another column, I’d like it to populate “Yes” if the donor has already given/is tagged to a particular campaign, “No” if they haven’t. Thoughts?

Hi all, Could anyone help me with this please: I have 2 columns of Attachment (images) and want to do what I normally do with email addresses and dates etc; namely have a new formula field that checks whether an image exists in one field and if that’s a No, use the image from the other field (in my case as the cover in a gallery). (1 image is from a linked table field, the other a direct attachment (if exists)). So, ‘date’ might be like this: IF({Date Published},{Date Published},{Date Accepted}) But ‘attachment’ gives me the AT field content like this: untitled.png?w=640 (https://dl.airtable.com/.attachments/a0dfdghtt8876ad82f549fadb408/2164e9ef/untitled.pngw640) I can split/extract the field components eg get the actual URL via something like: RIGHT(LEFT({Journal Image}, LEN({Journal Image}) - 1), LEN(LEFT({Journal Image}, LEN({Journal Image}) - 1)) - SEARCH(“https://”, {Journal Image}) + 1) but that still doesn’t get my image usable easily (without some scripting/automation). Is ther

I have a table for keeping track on contract lengths on customers and i want to get a notification when contract is up for negotiation. So, per default the contracted time is 12,24 or 36 months. And since we have clients that signed contracts a couple of years ago, we’ve passed the original due date, but our contracts gets automatically renewed 12 months, if not terminated. I’m struggling somewhat with organising the fields, what i want to achieve is basically: Signed date Expiry date (Signed date + Contract length) Contract length (a # field) Date for negotiation (Expiry date minus 6 months) But i also want that if expiry date has passed, a new expiry date 12 months in the future should be set, which also updates the ‘Date for negotiation’. Any ideas would be awesome!