Formulas

Hi, I have used ARRAYJOIN in rollups many times, but never tried it in a regular formula field, and I’m having some trouble. I was trying to use ARRAYJOIN to create a string I can parse with DATETIME_PARSE. I’ll simplify the formula I wrote here to get rid of some complicating factors. The formula I tried was: ARRAYJOIN([“2019”,“01”,“01”],"-") Airtable didn’t seem to understand square braces, curly braces or brackets, but I also couldn’t figure out any other wrapper for the array that would get it to work. All of the help articles I found are intended for rollups, where Airtable generates the array automatically. Anyone know how to do this? Thanks!

Question: I have a collaborator field with numerous collaborators per record, and I was hoping to add a field that would add up how many collaborators there are listed in that record. How would I do this? Thank you!

This formula is not working to pull data from the two formula columns (Days Actually Out and Backdata days out) I want. It seems so simple. If the DATE OUT is before or is today, pull this number. If the date is AFTER today, pull another. IF({Date Out} <= TODAY(), {Days Actually Out}, IF({Date Out} > TODAY(), {Backdata days out})) It currently only pulls the ‘Backdata days out’ data for this column. . . Can anyone tell me if this is a no-no in AirTable or if I’m doing something wrong?

Hello, I would like to send daily recurring reminder emails to my team members. I would like the email to be formatted to include the task due and also include specific fields that I have created including multiple select fields and due date. For example, within a table, “tasks”, I have a view which is filtered to include a single team members tasks grouped by due date and a multiple select field identifying the “importance” of a task, either “low, moderate, and high”. I have a formula column which prints the text “send reminder” if the due date field is today’s date: “IF({Due Date}=TODAY(), “send reminder”, “”)” I have set up a Zapier trigger using the “scheduler” zap, which is scheduled to trigger every morning at 5am. Next, I set up a Airtable action, “find record”, to search for the value of “send reminder” in the table. I then set up an action for gmail to send an email to the team member. I want to format the contents of the e-mail to contain: The name of the tasks due today, alo

Hello, my first post to this community. This formula was entered and accepted with no errors. The issue is the second IF (in this case {Tax Type}='Sales Tax) does not produce a result. Here is the formula: IF({Tax Type}=‘Bank Rec’,IF({Qtr}=1,‘1/31’,IF({Tax Type}=‘Sales Tax’,IF({Qtr}=2,‘3/20’)))) My statement is: Statement 1: if the tax type is ‘Bank Rec’ and the Qtr=1 then the date 1/31 should display. Statement 2: if the tax type is ‘Sales Tax’ and the Qtr=2 then the date 3/20 should display. Here is what I have tried: If I change the tax types for e.g. put sales tax first and bank rec second then bank rec will be blank I have hand typed the formula in the field, I did not copy and paste I have tried changing the single apostrophe’s to double For some reason the second IF does not produce a result even though the formula does NOT error out.I have uploaded the image of the result. Thank you in advance. I hope i posted this correctly.

I can’t find a similar question so I’m reaching out. My placeholder shows Project - “our tracking number” but I need to have “project” changed to Recall ONLY IF a dropdown menu has the status of Recall. Suggestions on the OR formula to use?

I have a download column with 1 number each day. I want to have the total number of download from the beginning from each day that is incremented. Anyone can help ?

I’m looking to estimate a person’s retirement date based on their career start date, this would be using the assumption that they would work 35 years following their first job.

I have two worksheets where I need to compare numerous data fields. Each table has the same common denominator in column A (invoice number) I want to compare numerous fields in the primary worksheet pulling from worksheet 2. For example: I want to pull the record data from worksheet 2 into worksheet 1 to compare them. Tax amount, total invoice, ship-to address, ship to city, ship-to state. I then need to see via a formula if one doesn’t match the other. So if the ship-to city doesn’t match it returns an “X” I also need to pull in data (ship via) from worksheet 1 into worksheet 2 (where it doesn’t exist I currently do all of this in Excel with vlookup. Is this possible to do in Airtable?

I’d like to show N/A when the field is blank, at the moment I get #Error. Can anyone help with where would I add it into my formula? Here’s what we have so far - IF( (IS_AFTER({Hose Inspection Expiry Date}, DATEADD(TODAY(),14,‘days’))=1), “ :thumbs_up: ”, IF( AND({Hose Inspection Expiry Date}>TODAY(),{Hose Inspection Expiry Date}<(DATEADD(TODAY(),14,‘days’))), “ :rotating_light: ”, IF( {Hose Inspection Expiry Date}<TODAY(), “ :skull_and_crossbones: ”, IF( {Hose Inspection Expiry Date}=BLANK(), “NA”, “NA” ) ) ) )

I am trying to write a function that allows me to add a set of numbers if a specific criteria has been met. EX: SUM (IF {Category 1} = “widget”, value {Cost 1})+(IF{Category 2} = “widget”, value {Cost 2})+(IF{Category 3} = “widget”, value {Cost 3})+(IF{Category 4} = “widget”, value {Cost 4}) (I’m not even sure if an IF function can be used this way.) I also don’t know if I should be using a rollup table or something else, given that this data would be on a separate sheet. Any help is appreciated. Thank you.

Hi, rather a basic question but escaping me at the moment. If I have a table that has columns Date & Amount, how could I get another table that summarises this data into Week number, Total for Week Thanks

Here’s an example of what I’m after. Example Input = “1-4,8,10,15b” Desired Output = “1,2,3a,3b,3c,4,8,10,15b” The idea is there’ll be a range between 1-100. Some of these may have children values (example 3a, 3b, 3c). If “3” is inputed it would capture all children. If “3a” is inputed, only that child would be selected. Ideally the ability to indicate a range (1-4). Any recommendations with this?

Hi Guys, I am working on a formula/solution that gives me the result from a specific field. Maybe you can help me. So my first field is a single select field with the following possible selections: -“Lookup 1 selector” -“Lookup 2 selector” -“Lookup 3 selector” -etc. This is then followed by multiple lookup fields that look up data from different tables: Lookup 1, Lookup 2, Lookup 3, etc. I now want to create an additional final result field that contains the value of the Lookup field that has been selected with the selector. And to make it more complicated: I am trying to avoid a long IF() formula because I might be adding more columns and selectors at a later stage. The goal is that I do not have to touch the formula of the final result field ever again. If I do have to touch it again, it should be a simple one like a sum or multiplication without any nestling. Do you have any good ideas how to do it? Thank you in advance!

Another one for the masses. I have 2 columns that calculate days in arrears and rent arrears based on the days in arrears and taking into account a third column of a part payment. A fourth column is a vacate date. these are the formulas for each column: Days in arrears: (IF({Vacate Date}, DATETIME_DIFF({Vacate Date}, {Paid To Date In Property Me}, ‘days’), DATETIME_DIFF(TODAY(), {Paid To Date In Property Me}, ‘days’)))-(IF({Part Payment},0,0))-(IF({Part Payment},ROUNDUP({Part Payment}/({Weekly Rent Amount}/7),0))) Rent arrears: ({Weekly Rent Amount}/7)*IF({Vacate Date}, DATETIME_DIFF({Vacate Date}, {Paid To Date In Property Me}, ‘days’), DATETIME_DIFF(TODAY(), {Paid To Date In Property Me}, ‘days’)) -{Part Payment} These work fine until you put a vacate date in which stops the days in arrears and arrears amount changing. which is what I want… but… If a tenant pays money and the paid to date is changed, this is not reflected in the new arrears amount. so my 2 questions are: Is there a

I have a Date Field and a Checkbox Field I would like Records with Dates in the Past to be unchecked and Dates that are equal to today or in the future to be checked. Date Checkbox 3/16 True 3/15 True 3/14 True 3/13 False 3/12 False 3/11 False 3/10 False Thank you

New to airtable and formula’s so this should be easy for you but Im having brain fart moment! I an trying to get a column to populate the following monday date once a date field is entered into the Listed column. using this formula: IF(1-WEEKDAY({Listed})<=0,DATEADD({Listed},1-(WEEKDAY({Listed}))+7,‘days’)) I get the desired monday result but all the other cells contain #error! ( assuming its because the listed column cell for that row is blank) Tried using BLANK but I just cant get my head around it to get rid of the #error! Thanks all!

I’m looking at matching a number of sorority committee chairmanships with a number of women. Each will have a list of preferences. For example, those elected that will be overseeing the committee chair will rank their preference of the women who expressed interest in a chairmanship. The women will also have a list of preferences to which committee chair they would like.

Hi! I have a photo gear base with all my gear, I have one table with all the gear and i have one table with shoots where I pick out the gear for each shoot. I would like to add weight (as in kg) field to my gear table in the Shoot table i can have a summarized weight of all the gear for each shoot. Will this be possible? Thank you!

HI, I have a guest list for an event and want only the last /recent entered values to remain. Unique identifier is guest name guest code as F1,F2,F3,F4,F5. Suppose guest F2 again enters the form after 10 days with new arrival date and departure date - then he previous entry one should be hidden from my sheet and only this be considered in all rollups/lookups/linking.formula etc. I shall calculate rooms required, catering etc when guests enter arrival/departure/no.of persons etc. data using a form When there are two entries in the form for same guest, say F2 - I want the Last modified time to be picked up /maybe in a different view and the first one should be hidden and out of my calculations. Effectively- Duplicates in the same column (F2, F2) need to be removed and only the value (F2) with last modified time should remain. With this feature- I allow guests to change their plan/date/no.of persons requirement in future by entering their guest code.

We are looking to gather feedback on creative images. Usually we print and write feedback directly on the paper, but we are looking for a way to digitize this process. Is there a way to add comments directly to images on airtable and can we gather feedback and approvals from multiple people?

We’ve built a table to track and support our Corporate Events (webinars, seminars, etc.) I’m pulling in a data from a form, and using a Date Field titled “Event Date,” to calculate four dates on which we’d like to send out an e-mail sequence to get people to register for the event. Is there ever a way to change those calculated dates? At all? I know this flies in the face of why you’d do the calc in the first place, but the dates are often fluid and change. If I were to do the calcs in a separate table, and then link to the results in another table, would they be editable? Thanks for any help.

I have a budgeting table with earnings and outcomes, so for the monthly payments (like subscription etc.) I’d live to have that at a certain date a cell duplicate itself. I was thinking to use a checkbox so if it’s checked than it duplicate periodically, but I can’t figure out the right formula. Can you help me?

Hello, I’m rather new to Airtable and need help with a Formula. I have [worksheet 1] & [worksheet 2] Worksheet 2 has two columns, {A} & {B}. {A} has words or short phrases. {B} has a point value associated with the text in {A}. In worksheet 1, I have a column that those words will populate in. I’d like to use the column right next to it to show the point value of text that appears in that column. How can I make this happen. I’m thinking using a formula with IF. But I can’t figure out how to type it up. Thanks!

Hello everyone, I use Airtable to manage projects and elements (Balcony, Terrace, Stairs, Separator Screen) in projects. I look for a method to automatically create drawings numbers. Indeed in elements table I have a link to projects table and for each element I create a drawing. I want drawings numbers drawing like this: ProjectNumber-ElementCode-Number the numbers is reset for each different element in same project. Example: Project1-Balcony A-01 Project1-Balcony A-02 Project1-Terrace X-01 Project1-Terrace X-02 Project2-Balcony A-01 Project2-Balcony A-02 Project1-Terrace X-01 So, as you see, the numbers at the end are reset for each element in same project, and reset between project. Can someone help me? Thanks SDionne