Formulas

Building a budget and I’ve searched for 2 days on this formula and now realize, its time for help. I have my paycheck weeks listed but I want to group by month - I’ve added a new table labeled Month to convert (using a formula) the dates to the corresponding month. For instance… 1/4/19 would be January 2/15/19 would be February 4/5/19 April and so forth. Thank you in advance for your help.

Hi Folks, I’m creating a research repository and want the primary field to be a concatenation of the value of a particular field in a linked table. Right now, it seems, that you can only access the primary field when making these links. Is there a way the value in another one of the linked records fields to show up and be accessible? Thanks!

I am new to AirTable and am not sure how to create a formula for a specific function I need. I am using a base to track involvement of triathlon team members in our club. Each event they participate in (team workouts, races, volunteer events, etc) awards them a particular number of points… referred to as DOTS. At the end of the year, their total dot count determines the monetary reward they get to use toward new team racing gear. I created a table that includes each MEMBERS of the team. I created a table that lists each EVENT. I created a table that lists each category of event and how many DOTS are earned for that event. To use the base… first I entered all the team members’ info. Each week I enter the events that earn dots. One column allows me to chose the type of event from the EVENTS table. I also have a column where I manually enter the number of dots earned for that event. Then is the column where it connects to the MEMBERS and I choo

Hello! Could you help me with a formula? We’d like to archive everything with a date in the past. Right now, we’re checking a box that then causes the entry to filter out. Is there a way to automate this? I was thinking an If statement (If the publication column’s date is prior to the current date, check the archive column), but I can’t figure out the exact language. Thanks!

Hello! I have a table with 4 sheets. 2-4 sheets collecting data 1-shets columns with lookup from 2-3 sheets, BUT i need latest entered data from 2-3 sheets and then calculate from columns Here is tables Airtable Customer credit limits - Airtable Explore the "Customer credit limits" base on Airtable.

Hello, for context I am creating a form that will be shared publicly, that will be shared widely, where the names of people will also be able to be seen in a public view (it is for a petition). I am afraid of people writing inappropriate/joke names and would like to catch as many as I can with an if statement that would turn them blank in this public view. I am not sure how to go about this exactly especially since I know the case matters. For instance IF(OR(Name=“Jesus christ”,Name=“Jesus”),Blank(),Name) is there a function i can use that disregards case and/or just searches the string for certain inappropriate word? Thanks

I’m trying to work out some hire prices using a formula, where the weekly cost of item is used to calculate the daily cost of it. I have an item that cost me £1000, and I have decided that the weekly hire price is this figure divided by 10, so £100 a week. The daily price is calculated by dividing that by 3, so £33.33. However, I want the spreadsheet to round this up to £33.50. How do I use the ROUND function to do this? At the moment I have: Weekly cost = {Purchase Cost}/10 Then, daily cost = {Weekly Cost}/3 For both, I want it to round to the next 50p. Thanks!

I’m doing a survey asking users to rate features on a scale of 1 - 5 using the single select data type. However, the responses are strings like Very happy, Happy, Neutral, Unhappy, and Very Unhappy. I need those values to correspond to numerical values like “Very happy = 5”.

What causes this error to happen? See first comment for the table. Formula here: IF({Premiere - HKT}="",“N/A”,DATETIME_FORMAT({Premiere - HKT}, ‘dddd’))

I recently discovered that when I multiply 6.1 * 3, and convert the product to a string using product & “” the result is a string with repeating decimals, even though the numeric result is simply 18.3. This is actually causing a serious problem in my table when formatting prices for invoices. Any insight is appreciated, thanks!

I made this formula that tells me when it has been 5 days since my last contact with a client IF(DATETIME_DIFF(TODAY(),{Last Contact},‘days’)>5,“Overdue”,“Ok for now”) and it works perfectly. There are some clients that I want to follow up with weekly, monthly, quarterly etc ! Now what I want it to do is look at my contact frequency column and if someone is tagged as weekly then say overdue after 8 days, if they are bi-biweekly say overdue in 15 days, if they monthly say overdue in 35 days etc etc. Any ideas on how I would do that.

Hi There I was hoping I could get help on linking 2-tables that are within the same base. I have a table called “List” and this table has fields which consist of PO #, Accounts & Shop Order #. On another table “Invoicing” which is populated with a variety of fields including PO #, Accounts & Shop Order #. Is there a way to link the PO #, Accounts & Shop Order # fields from the “List” table to the PO #, Accounts & Shop Order # fields on the “Invoicing” table? Basically if I populate the PO #, Accounts & Shop Order # fields in the “List” table then the PO #, Accounts & Shop Order # fields from in the “Invoicing” table will automatically populate with the same information. Is this possible? If so can someone give me directions on how to do this? Thanks so much.

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.

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.

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.