Formulas

Hi, I am using Airtable in combination with Zapier Zapier generates a record in my master table with a start and end date. Those dates can be 1) within the same month, 2) across two to many months, 3) over a new year. I would like to log for each record, how many dates are within each month within the given date range. E.g. 1) 3/5/19 - 3/10/19 should return 5 days in month 3 2) 3/5/19 - 5/2/19 should return 26 says in month 3, 30 days in month 4, 2 days in month 5 3) 12/29/18 - 1/3/19 should return 2 days in month 12 2018, 3 days in month 1 2019 The reason is that I want to calculate occupancy per year/month across multiple master table line items. Any thoughts?

Hi There Looking for help with a formula. I have a column (Inv Date) that is populated with Invoice Dates. I have a 2nd column (Payment Received) which has a multiple select list consisting of Paid, Courtesy, Consignment, Credit or it is blank meaning no payment has been received yet. In a new column (Past Due) I would like to create a formula whereby if the (Payment Received) column is populated with Paid it will then populate the (Past Due) column with Paid. If the (Payment Received) column is blank then it will populate the (Past Due) column with however many days it is past the invoice date. Please can you help. Thanks so much.

I have an Autonumber Column, I’d like to create a Formula that combines each Autonumber + a string “M” Formula “M” + {Autonumber} Intended Result “M1” Current Result NaN Thank you

I am trying to put a formula in Airtable that would calculate the difference between two times and give me the result in quarter hour increments. I would import the data as follows from a spreadsheet: Name Start Date End Date Start Time End Time Calculated Difference between two Doe, John 3/1/2019 3/2/2019 8:45 pm 7:00 am 10.25 Hours Smith, Jane 3/1/2019 3/2/2018 8:35 pm 7:00 am 10.25 Hours (Rounded to 8:45 pm) What would the formula be for the calculated duration because my payroll company is set up the enter into quarter of hour increments. Your help is greatly appreciated.

Hi There I have a column (Net Sales USD/CDN) that is populated with various mounts. In another column (Currency) I have indicated which of those amounts are USD currency and CDN currency. In a new column (Total Net CDN Sales) I would like to come up with a formula where it converts the USD currency amounts to CDN currency using a currency rate of 1.3 and leaves the CDN currency amounts as they are. Your help will be greatly appreciated. Thanks so much.

Hi! I’m trying to create a formula that extracts a keynumber from a string of text to add to a field in Airtable. The input is in some form like this: NB - 1720ENTHXXXXXAR - TTQ 1720 NEWSBREAK THURSDAY - 0:15 And the output should be: 1720ENTHXXXXXAR The problem I have is that the source doesn’t use consistent formatting, and the original string has an inconsistent amount of blank space characters all throughout, which I’m getting rid of using SUBSTITUTE(), and then using the MID() formula to grab the key number from the text with the spaces removed. This is my formula: MID(SUBSTITUTE({Input}," “,”"),4,15) However, there appears to be some inputs that aren’t fixed with the SUBSTITUTE() function, and as a result am getting an incomplete key. This, weirdly, is fixed by adding, removing or modifying a character anywhere in the original string. Here’s a sample base if you want to check the data. There seems to be some carriage returns in the original string, but Airtable should be conver

I have a column that is multiple select, in which I enter dates in the format mm/dd/yy, to keep track of when I bought things. So let’s say for Cinnamon, the History column might have dates 02/20/19 and 03/01/19 as multiple select entries. I am trying to: Have another column displaying the most recent date from that History column Have another column displaying a number value that is the difference between that most recent date, and today (example for Cinnamon above, most recent date is 03/01/19, so today being 03/11/19, that would be 10 days). Thank you very much for any help!!

Hello, Im trying this method to link users with “passwords”. It is a way to add a little of security inside an airtable database. Please let me know if you think of ways to make it better. I have a User table, where users can register using a form and they will fill in a field named Password, (type: single select) containing a list of uniques words. Let’s consider another table, Content table. Using a new form, users can select their name from the list of users (linked from User table), and they will fill a new Password field containing the same exact list of words. If you compare the Password field in User table with the Password field in Content table, for each user, for example in a boolean field named Matched, you will be able to filter all records that are not matching the password. If your password list contain more than 1000 words, it will be harder for people to impersonate a user, as they would to post the same thing over an over before it matches. I can now use the Matched f

Newbie here. I have a school table with a list of schools, a School district table, and a Contacts table. Schools are linked to school districts. Contacts can be linked to a school OR a school district. What I am trying to do is enter a new contact in the contacts table:. If the contact is related to a school, I choose the school in the school field and the district field is auto-populated If the contact is a district contact (no school) I want to be able to type in or choose the district in the same District field. I don’t know how to accomplish both in the same district field.

Hi all, I’m not too familiar with Airtable formulas yet. I have one long text field which has the following single record: This is a sentence This is also a sentence This is the final sentence I would like to take the whole sentence from each line minus the 3 characters from the left (so taking away the “1”, the “.” and the blank space “”). These would be pasted into separate formula fields. Formula field 1: This is a sentence Formula field 2: This is also a sentence Formula field 3: This is the final sentence I hope that makes sense, any help much appreciated!

Im not sure if a nested IF/AND formula is the way to go or if to round it. But when I try to write the formula I get an error message. Im trying to convert time into 1/10th increments of an hour for billing and account for rounding up or down. For example: Less than 9 minutes = 0.1 of an hour (6 minutes) 9 minutes to 15 minutes = 0.2 of an hour (12 minutes) 15 minutes to 21 minutes = 0.3 of an hour (18 minutes) And so on… I need to write a formula that will read the duration (hh:mm) and round up or down to the nearest 1/10th increment in minutes. Any help would be greatly appreciated!

I have three table : Table one 【member data】 which have {start date} {end date} {single linked to weekly summary} table two【activities】 {date of activities} {multiple linked to weekly summary per person} table three 【weekly summary per person】 {week number} {single linked to member data} {multiple linked to activities} If I want each activity to have the accurate number of member during {date of activity} , to get the real attendance rate. How could I do to set up the formula…??

Hi everyone! I have a simple DATETIME_DIFF field that returns the amount of difference in dates just fine. I however use it to count the days of an event and would like it to return the amount of days an event takes place, which then means I would need a DATEADD +1. For some reason I just can’t get it to work properly. It’s probably as simple as a , but still. This is what I’m trying to build from IF( {End Date}=BLANK(), '1 day', IF( {End Date},DATETIME_DIFF({End Date}, {Start Date}, 'days') & ' dagar ')) I am beyond greatful for any and all help I can get.

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 “-”, whatever!) if there is no articl

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?