Formulas

Hey all, I’d love a hand with a formula if anyone knows this? I’d like to find what % a number is of another number, if that makes sense… So for example… If I have $500 in an investment account, and that investment is currently sitting at $750, what’s the formula to show the % increase (which equals to 50%)? The formula would of course show the opposite of -50% if the investment fell to $250. Any help is greatly appreciated. Thanks! Christopher

Hi All, I am trying to figure out if a particular task has an upcming due date by seeing if a particular date field falls within 4 weeks from today. I have written the following formula: IF(AND({Milestone_Current_Due_Date} <= DATEADD(TODAY(), 1, ‘month’)),{Milestone Status}!=‘Completed’, “Upcoming Due Date”) But as you can tell from this screenshot it does not seem to operating correctly, saying some tasks have an upcming due date that is not within 4 weeks.

Hi everyone! I’m working in a pretty big AirTable right now and I have a question regarding searching an array. So if you see the first column I have a Lookup field with a large amount of items, some repeated, and I would like to search for each item and count how many times it was in the record. So for example, we would like to see how many “Mechanic” strings were in the array pulled from “Users (from User Categories)”. I thought the first step would be to convert the lookup field to an array so I have “Archetype Array”. However, I can not figure out how to search “Archetype Array” for specific string insides of it, like “Mechanic”. If you have any ideas to help, please let me know! Thanks!! :slightly_smiling_face:

Hi everybody, I need your help, please! I have a base with an action date, you have sixty days to complete the action. This is the deadline date. The deadline formula looks like this DATEADD({Action Date},60,‘days’) Now my problem is - If the deadline date is on a weekend, I want the reminder date to show as the next working day ie the Monday. Is there a formula that will accomplish this? Thanks in advance, Sean

I am trying to check to see if the Date column is empty, then if not, display the date’s weekday in the Day of Week column. Here’s my formula in the Day of Week column: IF({Date}!=BLANK(),DATETIME_FORMAT({Date},'dddd'),"") I do not understand why I am getting errors for all the blanks. I’ve tried several different versions of the formula above. Any help appreciated.

Hi there, having a difficult time writing a formula, for an employee timesheet. Currently, I am the using DATETIME_DIFF formula to calculate the total amount of hours worked within a 14-day period. I’m using two date/time fields for each day (ex: Day 1 - Start, Day 1 - End), so my formula is embarrassingly long: DATETIME_DIFF({Day 1 - End}, {Day 1 - Start}, ‘hours’)+DATETIME_DIFF({Day 2 - End}, {DAY 2 - Start},‘hours’)+DATETIME_DIFF({Day 3 - End}, {Day 3 - Start},‘hours’)… and so on 11 more times. My issue is, we have some employees that don’t work the full 14 days, so I need a formula that will calculate their total hours, and if some START/END fields are left blank, it will result as a “0” in the overall calculation. I am a novice when it comes to formula writing, and have just started using Airtable this Summer. Any help or guidance is appreciated. Thanks!

Hello, can someone help me figure out how to add ONLY the Start Date to a field? Currently, it is adding both the Start Date and Start Time. See images for my current formula. Thank you!

I’m trying to just double the value of a field in another field. I have one field called “costs” and another field called “goal” that I want to double to let me know how much profit I should try to make from an event. I’ve tried: {Total Costs} * 2 Also putting the 2 in parenthesis, leaving out the spaces, etc. Nothing seems to work.

Hey there, looking to create a simple slug and for some reason the apostrophe isn’t being removed. Example Title: Zoom Fatigue is real, so let’s deal with it. So make lowercase, remove apostrophes remove commas, remove full stops and replace spaces with dashes (etc) is what I’m after. LOWER( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( {Name}, "'","" ), ",", "" ), ".", "" ), " ", "-" ) ) I cannot fathom why the apostrophe is not being removed. I’m missing something easy… ideas?

Hello all, I see a lot of answers for a formula to calculate the age with the DOB. but on my forms, I only ask people their Year of birth. Tried different formulas but can’t get one that works to calculate the age when I have available only the year of birth. Any advice? Thank you!

This formula is being accepted in a field, and is working: IF({NOTES}=“to June 2022”,“Yes”,“No”) but both of these more elaborate versions are being rejected: IF(OR({NOTES} = “to Dec 2021”, {NOTES} = “Life”, {NOTES} = “Prepaid 2022-2023”), ”Yes", “No”) IF( OR( {NOTES} = “to June 2022”, {NOTES} = “to Dec 2021” ), ”Yes”, “No” ) Can anyone enlighten me on what I am doing wrong please?

Hello, Currently, this is the formula in the column to make it display the date of the upcoming Wednesday, but the times are random. I want to make the times in this column to all be 7:00am. Is there anyway to do this? IF(3-WEEKDAY({Submitted at})<=0, DATEADD({Submitted at},3-(WEEKDAY({Submitted at}))+7,'days'), DATEADD({Submitted at},3-(WEEKDAY({Submitted at})),'days'))

Hi all, I’ve got Field ID and would like to get its Field Name in a formula field. Is it possible at all?

Hi - i used search but only found different things asked … and the only similar question was closed and not aswered. … so i´ll try my luck here :slightly_smiling_face: It basically is something like an inventory roundup or item count on an invoice. I want to create a rollup field that gives me the counts of duplicates in a group in another table. Table 1 contains “roles” I can have multiple lines with the same role. eg: Field1 - Field2 Day1 - Role1 Day1 - Role1 Day1 - Role1 Day1 - Role2 Day1 - Role2 Day2 - Role1 Day2 - Role2 In table two i want to roll them up to something like: Day1: 3x Role1, 2x Role2 Day2: 1X Role1, 1x Role2 i found a solution where all options are hard coded … that is not usable as there are over 500 “Roles” already and we need to add more over time. any way of doing this? probably very easy and im just to blind to see the light … thanks!

Kind of new to using Airtable. Let’s say I have two tables. The first table is a master table with a whole bunch of information. For purposes of this ask, I am condensing to exactly what I need to know, but ideally this table will have a lot more info based on a client. The second table is a list of people involved and their role and what clients they have. A client can have 1 manager. But a manager can have multiple clients. So, what I want to do, is have a formula (or some other function) set up so that the Table1:Manager field auto-populates the Table2:ManagerName based on if the Table1:ClientCode value exists in Table2:Clients. I want it this way so that if clients get moved around managers, then it can update easily on my master table without as much manual intervention. Basically: Grab Table2:ManagerName where Table1:Clients is in Table2:Clients and insert it into Table1:Manager for Table1:ClientCode. Table1 Name | ClientCode | Manager G

hi i have a column with codes like this “0010o00002Fs719AAB” What will the formula to extract the 8 of the code? it should become Fs719AAB. Thanks in advance! Chris

Hello, I need help please, consult, I need to know how to use in a formula only one term within a record. example: within the field price m2 printed + cut I have two values, how do I select only one if I have two or more? Is it possible? Thank you!

Hi, I am having trouble with my IF(Find) formula. It is working for the first half of the formula but not catching the other "IF(Find). Here is what I have so far: IF(FIND(‘TR’,{Name}),‘Trade’,IF(FIND(‘PR’,{Name}),‘Private’)) It is making them all “Trade” and not picking up on the “Private”. Screenshot below for reference

I have three fields like this: | Start Date | Billing | Next Billing | In the Start Date field, there’s a past date In the Billing field, there’s a dropdown list with the values “Monthly”, “Quarterly”, “Semi-annually” and “Annually” In the Next Billing field, I want a formula that calculates next billing date based on the values in the two previous fields. Next Billing should always be a upcoming date (or today). Examples: If Start Date is “2018-01-01” and Billing is “Monthly”, I want Next Billing to say “2021-09-01” If Start Date is “2020-06-18” and Billing is “Semi-annually”, I want Next Billing to say “2021-12-18” If Start Date is “2016-10-24” and Billing is “Annually”, I want Next Billing to say “2021-10-24” I have tried to do this m

Hello, I am using this formula to display a red circle if a character count is over 310, or a green check if its not. I would like to add another condition but cannot get the syntax right, any help? I would like to add a 3rd condition, so if it is over 260 but under 310 it has a third symbol… LEN({Description}) & " " & IF(LEN({Description})>310, “ :o: ”, “ :white_check_mark: ”) Thanks!

Trying to do the following in my base: if field a = X, field b = X and field c = X or blank. Result will be pass, fail. For more detail X = approved. What is the current function?

Hello, I am trying to write an equation for total pallets used. If the total cartons is over 4it gets palletized. My equation is IF(Cartons > 4, ROUNDUP(Cartons/{Cartons per Pallet}), 0). but Airtable wont accept it. Any advice?

Trying to create a formula that adds x number of business days if priority is x. This is what I have so far: IF(Priority=“High”,WORKDAY({Writer Assignment Date},3, IF(Priority=“Normal”,WORKDAY({Writer Assignment Date},5, IF(Priority=“Low”,WORKDAY({Writer Assignment Date},10)))))) It accepts this formula, but populates with an “#ERROR” in some fields and blank in others. All fields have the required selections. Trying to create a due date field that is customized by priority. If it is high priority for instance, the person would need to complete the assignment within 3 business days by that date.

So I have a table with a field Client that references a record from another table. Client column as such can contain a value such as ['rec1234abc']. Now I want to do a filter to return all records that reference a specific record. I tried this (using the Python client): my_table.get_all(formula="ARRAYJOIN(Client, '')='rec1234abc'") But that returns me no results. How can I accomplish this?

(I’m using a very pared-down example here in hopes that I can convey what I’m trying to do, and then apply a solution to my actual base where I’m having this issue.) I paste data into my base from an internal report weekly. When I paste records from that report, some of the records already have numerical data in Column 2, and those records will be blank in Column 1. If a pasted record does NOT have numerical data in Column 2, that means it will have existing numerical data in Column 1 (and it’s data that is essentially related to Column 2). It is more important to me that every record has data in Column 2. For records with data in Column 1, I can calculate what belongs in Column 2 by using a simple formula - for the sake of example, let’s say the formula is {Column 1} * 2 . However, if there is existing data in Column 2 (i.e. Column 1 is blank), I