Formulas

I have two date fields and would like to pull out the months and years in separate fields from one or the other. I managed to get the single IF working but not the nested function. Here’s my formula: IF({Date A}, DATETIME_FORMAT({Date A},’YYYY’), IF({Date B}, DATETIME_FORMAT({Date B},’YYYY’))) which I based on: IF([logical argument1], [value1], IF([logical argument2], [value2], [value3])) Any help would be appreciated.

EDIT: It’s not letting me upload pictures, anyone know why? I have 2 tables. 1 called Members and 1 called Appointments. Here’s the members table: Each member has a Name, Membership Type, Shared with (linked to existing table), and another column linked to ‘appointments’ table. Here’s the Appointments table: You’ll notice a the field “Total Appointments per Active Member” Anyone that has membership type ‘shared’ means that I don’t count them as an actual member. I want to link them to the original member. And since I want to track each appointment by the main member, I’d like to figure out how to add the members ‘shared’ total with the existing membership. E.g. John Smith has a total of 3 appointments (his 2 booked, and 1 from his shared member Jenny). How would I do this?

I’d like a formula field to return =today() if another field is blank, but if the other field is already populated, to use that date instead. Any help crafting this?

Does anyone know how to stop the cells I copy information into (i.e. drag cell down the column to save the same in all) from merging into one cell on the copied page?

Hi, I’m trying to keep track of how many hours I spend on different activities per day and I would like to make a pie chart using the summary bar to see the total hours I spend each day per category of work? I have 1 field for date and 1 field for, say, finance, another for, development and another for personal time and I will add one record for each day. I need to be able to chart, overall, what percentage of my time I spend in finance, what percentage in development and what percentage personal. Is this possible? thanks

Hello Everyone, I would classify myself as a mid level Airtable user, but I am having an issue and am curious if what I am trying to do is even possible. Basically I am wanting to do a LookUp If formula with a linked Field. I have two tables with a linked field. In table 1 I have a list of items, some with check marks, some with not. In Table 2, I have a column linked back to Table 1. In Table 2, I want to be able to pull a list of the items that have a check mark. I know this sounds pretty confusing. Does anyone have any ideas?

Hi all In early stages of assessing whether Airtable is a good fit. I work with a large freelance base at our creative studio, and need to allocate freelancers across several projects. For example: Laurie will be allocated 10% to project A, 20% to project B, and 70% to project C. Is there a way to do this so I can see in the Calendar setting: Laurie’s project split across the 3 projects Which these projects are, which may have different date durations At first glance at the Contractor template, I can duplicate the freelancer several times - but this can start to look messy when the calendar fills up and does not clearly show the assigned project or % allocation. Thanks in advance for any tips!

Hello, So I’m attempting to find the string “Active” as so: Yet it’s not picking up the string for a number of records (ex. should be 1 for the first record): Why is this happening? Thanks!

Hello! I am trying to build out a formula that will allow for the following fields to populate based off of a due date: “Due this week” within 7 days of due date “Due next week” 8-14 days of due date “Upcoming” due after 15 days “Due Today” “Overdue” I have reworked my formula over and over for the past 4 hours and can not seem to get it to work. Any help would be much appreciated! Thanks! Jessica

Here’s my situation. I’m using Airtable to visualize information about my direct reports on an engineering team. It’s around a dozen people, all working on various things. I’m using Airtable to give me different views: for example, how much of their learning/development budget they’ve spent, or what projects they’re working on, etc. I have one table for people, one table for projects, one table for tags about the projects (things like “infrastructure” or “public-facing” or “front-end”), etc etc etc. So here’s where I’m currently stuck. Each project has a lead, which is a field in the project’s entry in the project table. What I want to do is have a field in the people table show whether that person is a lead on a project, and what project, and have that automatically populate. I don’t want to have separate data sets - I don’t want to have a field on the person that says “lead” where I have to go in and manually select the project, because that could get out of sync if I make cha

Hello everyone :slightly_smiling_face: , I’m using airtable with graphql and i need to do a product list with ITEMS & LINKS (those are Lookups from two fields of my PRODUCTS table) Here is my table (CATEGORIES table): https ://image.noelshack.com/fichiers/2020/01/7/1578236429-capture-d-ecran-2020-01-05-a-15-44-28.png (delete space after https, links seems to be forbidden) In the formula field, i’m looking for a solution that can do something like that but i’m stucked "allCategories": [{ "name": "category name", "products": [ { "item-1", "link-1" }, { "item-2", "link-2" }, ] }, ... ] As it’s my first website with Airtable, i don’t know how to proceed. May i can sort the data directly in airtable, may i can do this in my v

I have a complex formula which generates a value with double quotes (") in it. When I try to copy the field and paste it outside, the double quote is being copied twice. For example, if the text shows: Test"Test the copied text once pasted shows: Test""Test The field I’m copying from is a roll-up field. Anyway, I can avoid the double double quotes? Appreciate any help I can get

Hi All, Trying to fix a goof in our Airtable. Backstory: We programmed a certain formula to add in the year a record was created into the record name(i.e. 2019 =19). We pre made some of these records in December, and now need to duplicate the records and have all the duplicated records keep the same name. I hope you all have realized the problem with this… If you haven’t all new records now automatically signify it’s 2020 and no longer 2019. My Idea is to add in a check box to the records we want to fix to say "If box = 1 then replace “DATETIME_FORMAT(CREATED_TIME(), ‘YY’)” With “19.” Here is the Original Formula: IF({Class} = “STR”, “ROS” & “" & {Instructor} & "” & {Class} & “" & {Strength ID} & "” & DATETIME_FORMAT(CREATED_TIME(), ‘YY’) & “" & {Playlist}) & IF(NOT({Class} = “STR”), "ROS” & {Instructor} & “" & {Class} & "” & DATETIME_FORMAT(CREATED_TIME(), ‘YY’) & “_”& {Playlist}) This would spit out something lik

I want to add a bullet or emoji to the beginning of each line. To make things more complicated I have a CSV file that I’m importing that has | as line breaks. With a quick formula I’m changing | to \n and then adding a the bullet or emoji after the \n but obviously this won’t change the first record. Is there a (simple) way to add text to the beginning of each line?

Hello! I have a Project Tracking workspace, with two tables - a list of Tasks, and a list of Projects. I’ve found it easier to manage the tasks by keeping them in a separate table. But in the Project table, I’d like to see “Incomplete” tasks, so I created a column to return only these incomplete tasks. I can’t seem to reference this column in my Project table. How can I reference a specific column? For some reason the Client table is the only one that is available under a rollup reference. Thanks!

Hello, Sorry for the novice question but i was wondering on how to take the first four numbers from a field of numbers itself. I did try the LEFT formula but it doesnt seem to work for me. Now, there is a field called Ticket with a string of about 10 numbers with the first four numbers being the year. I want to take those four numbers from that field and put them in another field.

hi hi can i trigger if a relation was set to another table and then do a calculation ( reduce and set an int field by 1) thx, gr. pit

hi, i want to have some kind of warehouse management. i have a table with products. the products have an inventory. in another table i have a relationship between them and i wanna reduce inventory with 1, if i give our chance a datefield. it’s this possible? greetings Pit

Hey all, I’m tracking scores of sports events. The main fields are “Home Score” (for how much the home team scored) and “Away Score” (you guessed it…) In soccer matches it’s common for a team to score nothing. i.e. “0”. I’m calculating: IF(AND({Home Score}, {Away Score}), IF({Home Score} = {Away Score}, “Draw”, IF({Home Score} > {Away Score}, {Home Team}, {Away Team}))) The issue is, if I input “0” Airtable ignores it. it’s a blank for calculating consideration. So, field {winning team} remains empty. The workaround is to input a “1” score in case of actual “0” and to give the other team a +1 on their actual score. i.e. if a game’s score is actually 0:1 I’d input it as 1:2 . Is there a way around it? I want to preserve the information integrity. Thanks.

Hey Guys, I’ve had this issue before, and was able to solve it. For some reason, it doesn’t work now. We’re betting on score difference, so: IF({Home Score}>{Away Score},{Home Score}-{Away Score},{Away Score}-{Home Score}) If the game hasn’t played yet, the {Home Score} & {Away Score} are empty, which results in ‘0’ So, I’ve tried checking if the records are empty or not. IF(AND({Home Score},{Away Score}), IF({Home Score}>{Away Score},{Home Score}-{Away Score},{Away Score}-{Home Score}) But I keep getting an error, that my syntax is wrong. After the above I’m running this: IF({Points Difference Bet}={Actual Points Difference},“Bingo!”,IF({Actual Points Difference}-{Points Difference Bet}<=5,“Very Close”, “Way Off”)) But the points difference can actually be higher than 5, but I’d still get “Very Close” Please help. Thank you.

I am trying to create a field that will pull from 3 other fields to generate a contract termination date. Each account has a install date (ID), contract term (CT), and renewal term (RT). For example: ID=1/1/2017 CT=36 months RT=12 months Calculating the initial end date is easy (ID + CT) but I am stuck on the renewal. I want to be able to incrementally add months to the term length depending on whether or not the end date (CED) has already passed. For example: SD=12/1/2016 TL=36 months CED=12/1/2019 (original end date) RT=12 months CED=12/1/2020 (actual end date once the original has passed) I have a formula adding the renewal term to the original contract (ID + CT + RT) but cant figure out how to add RT as many times as needed to make the CED greater than today IF(CED<=TODAY(), CED++). Any advice would be appreciated, THANKS!

Hi, I am looking for a formula to remove text between parentheses. For exemple, I would like to have : Le nord (Chili) > Le nord Le nord (Soudan) > Le nord Le sud (Soudan) > Le sud Thanks in advance for your help :slightly_smiling_face: !

I’m trying to write a formula that would find multiple parts of text (from a single cell) into a short formatted text (in a cell). Example: From “Nothing, we are a turn-key service, from site prep, permits, to completion” to “turn-key” From “The client has some responsibility like permits, site-prep, and transportation” to “door-step” From “We just send the kit of parts and the client takes care of the majority of work” to “kit-of-parts” From “We offer just the drawings / floor plan in addition or exclusively as a service” to “floorplan-only” I was able to get it to pull just one text but not multiple.

Hi, I have a table that connects pairwise my data in a kind of graph structure. Is it possible to implement a (recursive or not) depth-first search algorithm that will filter the connected records only for a given cell value?

I need to record weight data for observations that have different levels of precision because they span a wide range and must be measured on different scales. Is there a way to allow for differing levels of decimal precision across entries in a single “Numbers” field? For instance, to enter 4 sample measurements of: 3, 3.0, 3.02, and 3.020, Airtable makes me select a single level of decimal precision for all of the entries, so it ends up showing as something like: 3.000, 3.000, 3.020, 3.020 or 3, 3, 3, 3 or etc etc… You get the idea. In any case, the resulting entries do not accurately reflect the actual data. As far as I can tell, if I want to use this field for formula-based calculations, I must designate it a ‘numbers field’ and therefore must choose a single level of decimal precision for all of the entries. Is there a way to get around this without misrepresenting my data precision and without creating separate weight fields for each level of decimal precision? Thanks for the adv