Formulas

In my Data I have some of the columns have Zero’s that are not relevant so dividing by any column with a Zero in it would not tell the truth at the end. Does this exist yet or is there a workaround? Excel has this and it works so seems doable in Airtable unless I missing something. =AVERAGEIF(A1:C10, “>0”)

I’d like to create a new record everyday automatically, with the a date column prepopulated, so that I can populate the other columns. Is this possible? Thank you in advance

Hi! We are using this for property management. Have a table with each action/task. some are one-time but other occur monthly, quarterly, annually… etc. I want to add the completion date to the May Landscaping Record [which is quarterly] and have this action automatically create a new record with exactly the same information, only with a new date that is 4 months from now? Is there a formula or Zapier way to do this? Please excuse me in advance if this thread/topic has already been explored. Also, if there is no formula solution, what are your best user-driven methods for updating such records without letting them slip through the cracks?

Hello! I’ve been struggling with a formula and am hoping for some help. I have an $Invoice field, a $Paid field, and I’d like to have a Difference field (i.e., show $0 if a client paid their invoice in full, or show the balance due if an invoice is short-paid. I would also like the difference field to be blank if there is nothing in the $Paid field… that’s where I’m struggling. Any suggestions?

For a given row I have like 8 date columns. Any of the 8 date columns could be the “latest date” and i’m trying to compare the latest date from that collection to today’s date. Any way to do this other than a crazy 64+ conditional IF formula on Airtable? lol. Like returning a max date in a given row then just calculating that against today’s date?

I’m organizing a list of companies and trying to calculate how many years they’ve been in business. Some companies are still open/in existence, and some businesses have closed/shut down. Ideally I’d like to have 3 fields: Start Date: The year the company opened for business End Date: The year the company closed for business (if the business actually closed down) Age: How many years the company has existed either up to today OR to the “End Date” if there is a value placed in that field. I’m trying to figure out how to write a formula that basically states the following: Age = IF End Date is null (TODAY - Start Date), ELSE (End Date - Start Date). I’d also like to figure out how to make all 3 of those fields on show “years” as appose to the whole date field (month/day/year). Any guidance is much appropriated!

I’m brand new to Airtable and am loving it so far. I have a product which has tiered pricing based on the volume of licenses that are purchased. Example 1-4 = $75, 5-10= $50, 11-24 = $55. There are 14 tiers. I want to enter the exact number of licenses that are being purchased, identify the category that belongs to, display the per price cost and then calculate the total license fees for # Licenses * Cost per License. Any help is appreciated. I also have other types of fees which I can manually enter as they are simple, but I’d also like to add the license fees plus the other fees to give me the total amount to be billed. Possible?

Hello! I hope an Airtable wizard out there can help. I have 12 tables - one named “Topics”, with a list of topics, and 11 tables of “Articles” (one per country I write about, which isn’t very handy but would be solved if Airtable finally did nested tables!), with a list of articles, which all have a field linked to “Topics”. Here’s my problem. All topics have not been written out as articles in all countries. I need to see that from the “Articles” tables. At this moment, the only solution I’ve found is to create an article with a blank title ("-") and assign it to that topic in the linked field. But I have hundreds of topics, and thousands of non-written articles, so it’s extremely time consuming to do it that way (and I am expanding to 40 countries, so it will soon be an impossible task). I was wondering if there was a formula I could use in the title field (which is the primary field in “Articles”) that would automatically show an empty title (or

Hello, anyone experiences missing notification settings on macOS Mojave? I’m using 10.14.3 version. In System Preferences -> Notifications, I can’t see Airtable options. Notifications from all other aps are there. Thanks

Is there a way to do a SUM formula that doesn’t just count the one record it’s attached to but includes previous ones? So like: A B 10 =SUM(A1) 15 =SUM(A1+A2) 10 =SUM(A1:A3) So I can add a number into Column A and have a running total in Column B. In the example above it would Column B would read 10, 25, 35

I would like to create a formula that adds a certain number per day. For example: Lets say I have a field that starts with 8000 and I want the field to increment by 44 each day. I dont want to add a new row each day, I simply want the same field to go: 8000 today then 8044 the next day, then 8088 on day 3. Is this possible? How would I go about this? Is there a simple way to simply add 44 each day? Or do I need to create a 2nd table that has pre-populated dates and numbers that go up by 44 (88, 132, etc) and the first table references the 2nd table somehow? I’m really terrible with excel/airtable formulas and logic :frowning: Any help would be greatly appreciated!

Does anyone know how to make a word count formula?

Hello, I’m looking for a way to track whether projects completed by team members were done so early, on time or late. I have a column that uses a single select drop down option for them to change their status when they proceed to the next portion of their project (On Deck, In progress, etc). Is there a way that I can set up a formula to track when a task goes from one status to the other, compare the date it was changed to the due date (also set up in a column using the date field) and return a value of either early, on time or late?

See if someone can help out with a couple formulas here. Thanks. See video below. Loom Loom | Send a video. Not a thousand words. Get your message across with instantly shareable videos for work. Capture your screen, record your front-facing camera, and narrate it all at once, then instantly share with a simple lin

Hello everyone! I have a need for a field that determines next “share time” for an event based on specific criteria. I have the following columns: {Start date} = date field {End date} = date field {Select dates} = checkbox field that indicates when an event concists of only select dates between the {Start date} and {End date} and not the whole span. I also have a date field for {Last shared} = date field What I want to achieve is a new field, using formula, that gives me the date the event should be shared next called {Next share}. If the difference between {Start date} and {End date} is less than 14 days AND the {Select dates} is unchecked AND if today is before {End date} I want it to add 7 days to the {Last shared} date. If the difference between {Start date} and {End date} is greater than 14 days AND the {Select dates} is unchecked AND if toda

Hi, I’ve searched around and haven’t found an answer to this, so here’s hoping someone can offer help. I’m using AirTable to record and invoice hourly work. Among my columns I have client (organized in single select dropdown) employee (organized in single select dropdown) employee hours (organized in a # column) date I need to create a view or a tab in my base that adds all the total hours by client in a way that allows me to add them to a Block so I can create an invoice. For example: a) On (date) (Steve) worked (3 hours) on (client 1) b) On (date) (Steve) worked (2 hours) on (client 2) c) On (date) (Bob) worked (4 hours) on (client 1) I need to have this formula calculate Client 1 - 7 hours (sum of records A & C) Client 2 - 2 hours (record B) … ideally while filtering by employee and date, but I can figure that part out. Obviously, organizing a view into “Group By

Is it possible to display results from 2 different IF Formula fields in one field-I tried using an ARRAYJOIN formula…

Hello, I’m trying to solve a problem that I think may not be all that complicated. I want to automatically add the date to a column if the column is blank (ideally so that dates answered previously don’t change but new entries have the date added). Anyone have any formula suggestions?

Noob here - so bear with me. I have an incoming date I want to convert into an Airtable Date - is there a formula that can do this? Here is the type of incoming information I want to convert: Thu, 14 Feb 2019 19:17:23 +0000 (UTC)

I’m trying to extend a function that I previously got some help with on this forum. My use case is a base for storing details on art exhibitions, with fields for a start and end date for each exhibition. With help, I got the formula working when it was only checking 3 conditions - Has it started? Is it currently running? Has it finished? I’ve now tried to nest another IF to test whether the exhibition will end soon (a certain number of days to the End_date), but I can’t get it to work. I’m wondering whether it’s even possible to do what I want! The closest I’ve got are these two. The following will flag all currently open exhibitions as “Ending soon” even if they’re not. IF(IS_AFTER({Start_date},TODAY()), “Not yet started”, IF(DATETIME_DIFF(TODAY(),{End_date},‘days’)<=3, “Ending soon”, IF(IS_AFTER({End_date},TODAY()), “Currently running”, “Already finished”))) The following will flag all open exhibitions a

When pasting the created formula field from Airtable to another application (Chrome or TextEdit), the result adds open and closing quotations "". Formula: ‘’ & Name & '’ & “\n” & {FIELD 1} & “\n” & "FIELD 2: " & FIELD 2 & “\n” & "FIELD 3 " & {FIELD 3} & “\n” & "FIELD 4 " & IF({FIELD 4}=“1”,“Yes”,“N/A”) & “\n” & "FIELD 5 " & {FIELD 5} & “\n” & "FIELD 6 " & IF({FIELD 6}=“1”,“Yes”,“N/A”) & “\n” & "FIELD 7 " & IF({FIELD 7}=“1”,“Yes”,“N/A”) & “\n” & "FIELD 8 " & IF({FIELD 8}=“1”,“Yes”,“N/A”) & “\n” & "FIELD 9 " & IF({FIELD 9}=“1”,“Yes”,“N/A”) & “\n” & "FIELD 10 " & {FIELD 10} & “\n” & "FIELD 11 " & “\n” & {FIELD 12} Result: “Sample Name FIELD 1 FIELD 2: FIELD 2 FIELD 3 FIELD 3 FIELD 4 Yes

I’ve just imported my library database from librarything. Copy and paste worked as a charm for my 711 books, I was impressed! The only thing is that on the Author field I got “surname, name” records: “Kafka, Franz” When I try to link them to another record in a new base, I get one record for “Franz” and another for “Kafka”. Is there a formula to join text separated by comma within a record?

I’m trying to create a formula that will list multiple fields that are empty, but can only get it to list a single field. It is meant to be a quick reference field to see what info is not filled out in a row. The goal: IF {Field 1}, {Field 2}, {Field 3} are all empty, but {Field 4} is filled out, then list “Field 1, Field 2, Field 3” Below is my attempt. It only ever lists a single field, seemingly overwriting other values that would be returned. If I introduce OR IF it breaks the formula. IF({Film Year}=BLANK(), “Film Year”, IF({Print Source}=BLANK(), “Print Source”,IF({RT (min)}=BLANK(), “RT (min)”)))

I have a roll-up field that rolls up a bunch of warning signs, which I want to display neatly on top of each other instead of separated with commas. I got a formula with the following: SUBSTITUTE({Roll up warning},’,’,’\n’) I’ll expect it to replace all the commas with line breaks - however it just gives me an #ERROR!. What am I doing wrong?

I am in the process of changing everything from excel/sheets over to Airtable and I have run into a strange problem that is quite trivial in Excel/Sheets but seemingly difficult in Airtable. Essentially all I want to do is count how many times an email address has come up in the last 30 days from a DATETIME. Each record has a unique datetime and an email address, all I’m trying to do is count how many times that is happening in Airtable. The function in sheets is =COUNTIFS(A:A,A2919,B:B,">="B2919-30,B:B,"<="B2919)) Where A is the email column, and B is the date of entry. Any help would be appreciated.