Formulas

Hello, anyone know how to delete every characters/words after X number of words plz ?

How do I aggregate the sum of records in one field from one table to one record in another table? The key condition is that the Unique ID should match in both tables before the aggregation. So if the Unique ID is the same, only then the aggregation should happen. I have attempted multiple hacks and read all about RollUps and Conditional RollUps, but still cannot figure this out.

I am setting up a data base to track our job postings on various job sites. For a given job posting, we have a “status” field (this is the one I want to conditionally format), a “date posted” field, and a “date expires” field. The date posted field and the date expired field will be manually populated at the same time, once the posting goes up, although they obviously won’t have the same date. What I want to happen in the status field is: (1) when the date posted is blank, I want ‘need to post’ to appear. (2) when the date posted is populated and the expiration date has not passed, I want ‘posted’ to appear. (3) when the date posted is populated and the expiration date has passed, I want ‘expired’ to appear. Here is what I have… IF({Date Posted},AND(DATETIME_DIFF(TODAY(),{Date Expires})<=0,‘ :white_check_mark: ’,IF({Date Posted},AND(DATETIME_DIFF(TODAY(),{Date Expires})>=0,‘ :x: EXPIRED’,IF({Date Posted}=0, ‘ :raised_hand: Need to Post’))))) Airtable accepts this formula, but n

Hello, I am having an issue with the NUMWEEK Function that returns me week 3 instead of week 2 (according to google) for the following dates 13/1/2021 and 12/1/2021. What does it mean ? Am I doing something wrong ?

Scenario: Airtable is used to track visits to a facility. The facility sells 10-pass “punch cards” that expire after 10 uses. If the person doesn’t use the 10 punches before a specified end date, the punch card expires. I have a base set up with 3 tables: People Visits Punch Cards People is linked to Visits and Punch cards I’m pulling an array of all dates a person visited the facility via a rollup function into the People table. In the Punch Card table, I’m using a lookup to bring in the array. In the Punch Cards table, I want to return the count of all visits that took place within a specified date range (the valid range of the punch card before it expires). For example, if a person visits the facility 3 times- in April, May, and Jun, but the date range the punch card is valid is for April, the count should be 1. Example base here: https://airtable.com/shrmvP5cBpAknjW1f

i have the following colums that iam trying to combine in a formula : customer billing (with only two possibles choices: direct or indirect) indirect total sales revenue (a formula which multiplies a monthly amount by a number of months) I need a formula in a new column which combines the two following conditions: IF “indirect” is chosen, then copy the same amount IF “direct” is chosen, then apply a 12% on the “indirect total sales revenue” i hope it is clear enough. Thyank you very much in advance! fsabate@isadviser.com

We are building out an inventory control system through Airtable. I’ve found examples and been able to build out the functionality to have the inventory adjust up and down based on receiving, production runs, and shipping. However, I cannot figure out how to incorporate physical inventory counts into the current inventory number. Example of what we would like to do: THIS PART WE HAVE BUILT: May 31 Starting Physical Inventory Count of Item A = 100 Throughout June, 30 Item A units were received Throughout June, 40 Item A units were shipped June 30 calculated inventory of Item A = 90 PART WE NEED HELP ON: June 30 physical inventory count of Item A = 85 (5 were lost to waste, etc.) What is the best way to enter this new physical inventory so that items received / shipped before June 30th are not adjusted from it and ONLY items shipped / received after June 30th until the next physical inventory count are deducted from the June 30th physical inventory count? Has anyone built an inventory tr

I have a date field that is on a third Friday called “Date Started” I need a date formula that calculates the following 3rd Friday of the month for my “Due Date” field. Thanks

I would love if someone could help me work through how to set up a formula for the following situation. We have a volunteer application form for which we ask for applicants to provide 3 references. When determining whether to approve their application, we require 2 of the 3 references to be positive. If one of the three references doesn’t respond or identifies concerns, they are still approved if we receive the other 2 and they’re both positive. Basically, I need a formula that says if any 2 of these 3 fields has the value of “No concerns”, the approved column value becomes “Yes”. I’ve figured out how to use IF-AND parameters to set up a formula that says if all 3 are labeled no concerns, it’s marked yes, but can’t figure out how to incorporate an OR in there to allow for approval if any 2 of the 3 fields are labeled no concerns regardless of which 2 they are. I’m sure in plain English, my formula should probably be something like: If (reference 1 & reference 2) or (reference 2 &am

So I have done work with formulas on other projects but this one i havnt been able to figure out. I have a table where i am going to keep track of equipment hours and another where i keep track of fuel used. For the hours table i will add a new column for each day with the equipment hours to track how many hours that machine operated that day but i need to have 1 column that reports the last hours input. I know that i can put a “Ceiling” formula in but that would require modifying the formula to add a new column each day and after awhile that will get stupid. I would need one column that would take the highest number of all columns. For the Fuel table, i will be adding a new column for everyday to track fuel consumption. This would be easy using the Sum formula but just like the Ceiling formula, i would have to modify the formula everyday, adding a new column for it to include. I would need one column that could be updated with the sum of all columns related to fuel. Is there anyway to

Hey Everyone! I have a lookup column with dates like this 5/30/2020, 5/29/2020, 5/15/2020, 5/7/2020, 9/21/2020, 11/27/2020 I need to only show the last date. I also need one for only showing the first date. Thanks!

Hey all, we are trying to get unique project numbers for our airtable. (We are managing video projects). Last year, we just did an auto number and had a formula to add 20000 to that number. 20001, 20002, 20003, etc. This year, we want the unique project number to be P21001, P21002, P21003… P for our name, 21 for Year, and 001 for the unique project number. We also want 2020 projects to be P20001, etc. I found in the forum the formula for getting the YY: MOD(YEAR(CREATED_TIME()), 100) Can someone help me figure out how to make the formula that will work for any year in this way? Thanks so much in advance!! Austin

Imagine I have 2 tables: STORE and ITEMS STORES are linked to the items table so that multiple items can show up in each store. The ITEMS table has a column called “Last Updated”, which is a bunch of dates I created a lookup field on the STORE tab that looks up the linked ITEMS “Last Updated” field. This returns a list of dates in a string: 10/20/20, 10/19/20, 11/2/20, etc Question: I want to take the Max of this field but I don’t know how to do this. I tried a formula like: Max({Items Last Updated}) but that doesn’t seem to work. Sadly, Airtable doesn’t seem to have a Split() formula equivalent. Any ideas? Thanks in advance!

Hello everyone ! I have records with multiples Date fields (4 fields to be exactly). So we have {date field 1},{date field 2},{date field 3} and {date field 4}. I would like to have a formula field that shows only the year of one of these date field. Something like "If {date field 1} has no date, check {date field 2}. If not, check {date field 3}. If not, {date field 4}. If not, keep the cell empty. I tried some of formulas, without sucess. Thank you so much for your help! Quentin

Hi, I’m attempting to solve for the posted date/time of an item. Desired result: date & time of post for a new item. Available data: each item has some information about the time, but is listed in one of the following formats: x minute(s) ago -x day(s) ago -x week(s) ago Success criteria: the best approximation of an items posted date/time understanding that “a week ago” could actually mean 7-13 days ago but, for my case, I can accept this as 7 days ago. The desired results will contain a date and time. I’ve tried to use the “created time” field and find a way to subtract the time unit(s) I’m provided with, but to no avail. Is there a way to accomplish this in Airtable? Thank you in advance.

I want to make a search formula where i only want to show avaiable houses. The houses are being rent with an start date and also an end date. For example i have 3 houses. house1 is rented from 1-1-2020 untill 6-1-2020 house2 is rented from 1-2-2020 until 17-2-2020 house 3 is not rented I want to show available houses for someone who wants to rent from 1-2-2020 untill 6-2-2020. So house 2 needs to be filtered out. Can someone help? Thank you

I have a product the changes cost frequently and I want to track this cost change in one field. This original formula has been working for me but now I am trying to figure out how to calculate the cost between two dates. For example: Cost before 1/1/2020 Cost between 1/1/2020 - 1/1/2021 Cost after 1/1/2021 IF({Date}, IF(IS_BEFORE({Date}, DATETIME_PARSE( ‘1/1/2020’,’’)), VALUE({Product}) * 0.62)) + IF({Date}, IF(IS_AFTER({Date}, DATETIME_PARSE(‘12/31/2019’,’’)), VALUE({Product}) * 0.53)) + IF({Date}, IF(IS_BEFORE({Date}, DATETIME_PARSE(‘1/1/2021’,’’)), VALUE({Product}) * 0.53)) + IF({Date}, IF(IS_AFTER({Date}, DATETIME_PARSE(‘12/31/2020’,’’)), VALUE({Product}) * 0.48))

Hello! I’m new to airtable and trying to figure out what formula will take a date field and then subtract approximately 14 days to the next closest specific weekday. Details - Current Formula only calculates date 2 weeks prior: DATEADD({Event Date}, -2,‘week’) I need to back up from the “Event Date” approximately 2 weeks to pick-up product for the event, but pickups always need to be on a Wednesday. Any help is much appreciated!

Hi, I hope I’m posting in the right place. Brand new to Airtable. My goal is to set up a CRM for networking that will remind me when to reach out. My plan: Each person (record) gets a numeric rating to indicate how often I want to be in touch with them. I will have a field where I manually enter the date of most recent contact. I want another field that will calculate the next date needed, based on the rating. So, if the rating field has a 1, the field with the next contact needed will be 30 days, if the rating is 2, 60 days, etc. I’d like conditional formatting to turn the record a different color if I’m overdue. I am really not well versed with formulas or functions so I have no idea how to set this up. Can anyone help me? Thank you!

I have created a random number generator string {RNGS} using this formula: VALUE(DATETIME_FORMAT(AddDate, ‘mm’)) # Autonumber # Autonumber. (Note: I have purposefully substituted # for an asterisk/mutliply function in this post) A sample result is 238425 (33 # 85 # 85). I’d like to use just the first 4 digits for my random number field I’ve tried both RIGHT(VALUE(DATETIME_FORMAT(AddDate, ‘mm’)) # Autonumber # Autonumber,4) and RIGHT({RNGS},4) Both of these generate the #ERROR! message. Please help!

I had a functional formula formerly from a template, and then I went and copied tables between bases, and lost the actual formula in the field. What I’m trying to recreate is a conditional formula that returns a status on character count based on the social channel selected in another field. Basically: If CHANNEL is Twitter and CHARACTER COUNT is between 71 and 100, then :ok_hand: , otherwise :exclamation: :exclamation: If CHANNEL is Facebook and CHARACTER COUNT is between 40 and 80, then :ok_hand: , otherwise :exclamation: :exclamation: If CHANNEL is Instagram and CHARACTER COUNT is between 138 and 150, then :ok_hand: , otherwise :exclamation: :exclamation: If CHANNEL is Linked In and CHARACTER COUNT is between 25 and 140, then :ok_hand: , otherwise :exclamation: :exclamation: Thanks in advance!

When trying to do the very simple formula of CONCATENATE({First Name}& “ “&{Last Name}) it won’t submit, simply saying invalid formula. I have read another thread that said this is what to input to have a space between the two values, and that I also shouldn’t need to use the concatenate prefix, I’ve tried many things and nothing works. Any help?

Hi all, Can you please help me make a formula? I have records with the following (relevant) columns: Commission Processing Date (date format) Income ($ format) 1099 ($ format) After a certain date, my Income needs to be adjust by -15% and that result should be listed under my 1099 column Thus, I would like to do the following formula for my 1099 column: If Commission Processing is on or after 01/01/2020, then Income should be multiplied by 0.85. Otherwise, 1099 remains the same number as Income. Can’t figure out how to do the date part of the IF formula. Thank you!

Hi everyone, happy new year :hugs: I have a question regarding using LAST_MODIFIED_TIME() in a formula. Is there a way to use LAST_MODIFIED_TIME() on another formula field type? I have formula field “Stage”, and I want to write a formula that recognise that there is a change in “Stage”. Can you please help me achieve this? Thank you, Pjero

I’ve built a simple tracking for my recurring expenses and I want one of the fields to show the current month with the information in the field next to it. I can’t seem to find how to do that.