Formulas

Relatively new to Airtable. I’d like to be able to calculate the total number of workdays in a given month for employees who work six days/week in a museum, so Tuesday through Saturday. My approach has been to take the total number of days in a given month using DATETIME_DIFF and then try to figure out a way to count just the Sundays in a given month and subtract these from that total. Ideally, I could just define WORKDAY_DIFF parameters (in fact, it seems obvious to me that I should be able to, but I don’t see any reference to this/any way to do it . . . just the outdated(?) assumption that a workweek comprises one set of values).

Calculate lookup values against a calculated column and then sum. Example: Lookup results * calculated_column 14,15,17 * 9 SUM((14 * 9)+(15 * 9)+(17 * 9)) How can I do this?

I need some help with creating a formula that can calculate the price, depending on which week the purchase was made. We’re selling weekly courses from February till July (23 weeks in total, €15,- each) The full program is €345,- When customers book a year in advance they get a €20 discount. I got up to the point where I made a formula that works and kinda goes like this: IF weeknumber < 5, the price is €345 IF weeknumber > 28 the price is €325 Since people can join our courses at any giving time. I would like to add a formula that calculates the price depending on the week they joined. I’m looking for a formula that kinda goes like this: IF weeknumber is between 6 and 27, the price is €420 - (weeknumber x €15) Unfortunatly my coding isn’t that good yet… If anyone could help me out combining all of this in one formula, I’d feel blessed!

Hi there I’m building out a detailed budgeting base, which includes a section for documenting when we expect our income to hit our accounts. My husband gets paid every 14 days (fortnightly), for example. Is there a formula that would let me document this without doing every bit of income manually? If I was using Google Sheets, I’d be able to have a ‘date +14’ formula, but I can’t figure out how to do that in AirTable. Thanks for your help in advance!

Hey, So I have three tables, Sales, Purchases & Inventory. I have 3 items A, B, C. In each sale, a customer can buy all three, just two or one of the items in whatever quantity they wish. On each day I make purchases of varying quantities of A, B & C. I cannot figure out how to use rollup to give me a total of purchases, sales and purchases - sales in my inventory. Images of my three tables below.

So I have a “Members” table with “Name” and “Email” as fields I also have an Activity form for activity checks as “New Members” table. They put in their email in, and submit the form. This is then placed in the “Email” field. I want to display their name in the “Name” field of the “New Members” table using lookup. I know I can use the link to another record field, but that isn’t automatic. Is there a way to take the table’s “Email” and set a value to the “Name” field based on the email that was provided (executing a lookup to the “Members” table)

We have two columns that we need added to a third sum column. Our first column is our Admit Date for people using our program and the second is Exit Date for when they leave the program. We need our Admit Date and Exit Date columns to populate to a total days in care. Right now we have the formula as: SUM({Exit Date}-{Admit Date}) but it is giving us a message of NaN when an admit and exit date are plugged in.

I have a formula for calculating when I can plant seeds, according to the LFD (last frost date), which is and will remain 3/29/2020. Weeks is how many weeks before the LFD I can plant the seeds. The formula: DATEADD({LFD},-{Weeks Start},‘weeks’) Currently I have a whole column for LFD that is set to autofill 3/29/2020 into every row, of which I have 350+. Instead of this whole column for LFD, is there a way I can somehow reference this number one time within a formula, or within one cell somewhere instead of a column of 350 rows with the same number?

Hello Everyone and forgive if this question has been answered in a previous Post. Quick background. I created a StartDate using the Date field. Then I created a second column to enter the number of days that the project is expected to be completed. This field is a ‘Number’ field called Duration. Then I created a third field called EndDate which is a formula field where it calculates the number of days assigned to the project with the Start Date to acquire the End Date value. The formula is DATEADD(StartDate, Number, ‘day’) First question, is there a way to exclude ‘weekends’ so number of actual days would be business days? Second challenge - Using the EndDate information, if the project goes past the EndDate by more than 2 business days, I would like that to be populated in a PastDue field where it would state: ‘Past Due

I have a formula to calculate how many plants I can fit in a 10x12" square. My formula goes: 10/Spacing * 12/Spacing. (Spacing is a column where I record the number of inches of distance a plant needs). The formula works, except for in two instances- 1. When I have not filled out the spacing column yet, the formula produces ‘INFINITY’ as the result. I’d like it to return a blank field instead of returning ‘INFINITY’. 2. When the plant requires two 10x12" squares (i.e. when the Spacing is 24"), the formula produces 0. I would like that changed to 2, or even better, an actual calculation with a decimal, like 1.5, 2.5, etc. Can anyone help? I’ve searched similar issues but can’t find anything that helps me.

Hi All I’ve been working on a simple time off/vacation tracker. My company has a service they use, but it is really difficult to interpret the data displayed. My company allows our time off to be fully available at your anniversary date of hire. We do not use accrual of time as some companies do. I’ve almost hit what I’m looking for, but need one more piece to button it up. I basically have two tables. 1 PTO Data holds the critical criteria - PTO types, alloted, planned, balance etc. There is a column which is linked to the 2nd table pulling in the time off dates requested Linked Calendar - is a table, but I display in a calendar view and I simply select a date in the calendar for the time off. I want the ability to change the time period of how balance is calculated off of the dates requested. Not sure of the best way to do this. Right now I have two columns…PTO Begins and PTO Ends…this is my range of when my PTO is

Hello everyone, I order to properly store geographic coordinates in Airtable, I need to create a formula to transform a field like “Address: Province: Country: Longitude: -1.4934866Latitude: 12.3614676” into “12.3614676, -1.4934866” I have tried a few formulas but I systematically failed. Anyone could give an hand on this please? I would really appreaciate. Warm regards from Burkina Faso Franck

I’d like to use the cumulative sum of one of my columns in a formula. Is that possible? Essentially, I want to use the sum of the column “material amount” divided by the column “goal” which is fixed at 3,500,000 to determine in the column “% of my goal” the ongoing percentage of my goal I am at in any given time period.

So I have an existing table where I am trying to compile a list of different business chains and their locations (both cities AND counties) I have 2 separate data lists that I am working with - a list of cities where the businesses operate, and a list of counties and each city within them. One of the columns is a multiple-select where I can choose different cities where “Business A, Business B…” have existing chains. “San Francisco, Oakland, etc.” Is there any way, either in the same cell or in an adjacent column that once I add a city “San Francisco” for example I can automatically get “San Francisco County” or “Oakland” and “Alameda County”. The maybe tricky part is that if I have multiple cities from the same county, I don’t want duplicate county names. Thanks!

Is it possible to convert integer values into ASCII characters in Formula columns? Pseudo code example: convertToChar(71) returns the char ‘G’.

I need to filter the output of a table to only include records that contain the word “attractions” in the Categories field. I know it’s not correct but here’s what I tried: If({Categories} = attractions) Can someone tell me where I’m going wrong in the code? Thank you in advance for any help.

Hi All, I’m trying to create a base for managing and tracking my time off requests. The service we use is clunky and difficult to interpret, so I want to keep track of this on my own in the event the service we use does not line up to what I requested. I’m having some difficulty on how to do a formula or at least achieve a particular end result. I have a table called Time Off Benefit I next have created a table for my time off request to include the reason, a column for Type (vacation, sick, remote in and bonus hours), Hours requested and # days (which is hours requested divided by 8 hours), start date, finish date, approved (yes no) and approval date. What I now want to do is figure out a way in another table to calculate time left for each time off type based on the benefit table for the given work year My work year is equal to date of hire…

I’m using this current Formula and it works fine: IF( {Total Pages} < 12, 1.67, IF( {Total Pages} < 43, 2.00, IF( {Total Pages} < 60, 3.12 ))) I’m hoping to add an element that IF( {NO CHARGE} is “yes”, then $0 What is the best way to add this…

I’m lost! I’m trying to write a formula something like IF({Duration}>=5, “1”,{Duration}>=10, “2”), so when a contestant reaches a certain number of hours, a reward automatically appears in the column. and so on… Where duration is hours. I know AT defaults to seconds and have tried versions of this on my view. Just thought I’d throw out hours if it would work maybe. I’ve tried: IF({Total Seconds Watched}>=18000, “1”,{Total Seconds Watched}>=36000, “2”) with the current config. It seems to save the formula but then 1. only displays the number “1” even if seconds are greater than 36000, and 2. does not save the formula with the , “2” - it disappears each time I save it. Help and thanks for any insights and more elegance to my clumsy formulating!

I have a multiline text field and I would like to use a formula field to take that multiline text and use the existing content but add a bullet point character in front of each new line. Is this possible? I’d like it to happen automatically without manually having to run a script Part 2 - If that works, I’d like to take the bulleted list and split it after 10 lines into a second field. Is this possible?

Hi All, I currently manage a budget on Airtable. I have a list of all of my live expenses (In a table called live expenses) which I upload daily. E.g. supplies expense which the matching GL code. I have another table called actual which I compare to the budget. I’d like to have all transaction from the live table added in to this table. For example all of the Sales income be added up and added into the actual Sales income line. Any insights would be highly appreciated. Thanks

I’m using integromat to automate some data from google sheets to airtable. In this automation flow, I’m using ({record ID} = ‘string’) formula to match the g sheet cell with the airtable record id. For the most part this worked just fine until it came across a record that also had ’ in the record. This, I presume caused an error in the formula to not match the string as it stopped after the ’ character thinking that was the end of the string. Is there an escape character I can use in this formula to solve this issue? Or is this something I have to solve in the google sheet for the matching strings?

I have an age formula that returns an “error” if no birthday is available. How do I remove the “error”

Hello all, I have a range of random numbers. For example: 7, 13, 24, 28, 32, 35, 37, 40, 42. I need a filter that filter numbers into separate section. For example, “1st section” includes 1, 3, 5, 7, 9 then the “7” above will be filtered into that “1st section” and so on. Is there anyway to do this with formula? Like if the value is “7”, it will have the “1st section” text or something similar

I have a table that is being filled via Zapier from a form. I want to be able to go into that table, manually add a name into a column, and based on the name, use an IF statement to send to one of a number of other tables. Any ideas how to do this?