Formulas

Hello,I'm wondering if anyone has any recommendations on how to achieve the use case below. I've seen VLOOKUP scripts and I've also seen automations, but not sure which would be easier given the criteria.Table A has each Product and State pair listed as its own row (as there are other columns of data applicable to that pair). The Product will have as many rows as necessary, depending on how many states it's sold in. The Status column says if it's approved to be sold in that state.Table B needs to be a summary of all of the states that each Product is sold in and the status of the approval, where each Product has one row and the States are now listed as columns.Table B needs to lookup the Product name in Table A, find the State name in each row and bring the Status in that row over to the matching State column in Table B.It would be great if ea

hi, asking for help,I made a database of my office documents and i need a formula for due dates that is due on 16 months, however the due dates will correspond to two （2） date fields, ex. date A and date B, the condition is this; if date B is not empty, due date will correspond to date B + 16 months, but if date B is empty, due date will correspond to date A + 16 months.I hope someone help me, thank you!

I have created a Rollup field in my Contact table which searches within the Touch Points linked table and rolls up the 'Start Date/Time' field using the 'MAX(Values)' aggregation formula. My challenge is that I am unable to get a date which I can then filter to appear inside my Rollup field. The Formatting tab does not have the option for date as per screenshot:https://share.getcloudapp.com/X6uKqr7Qhttps://share.getcloudapp.com/9ZuKv2yyAny input would be appreciated. Video included below for more context if necessary:https://share.getcloudapp.com/rRugpPzAThank you!

Hi,  In my database I have several fields that are calculated automatically with a formula, but I want some of these important date in a calendar.  But the Calendar only allows me to use a Date Field, how can I "force" the calendar to take the formula date ? This is my formula fieldDATETIME_FORMAT(DATEADD({Date de départ}, -60,'day'),'DD-MMMM-YYYY') Thanks for your help !

Hello Airtable Community! 🙂So... I am rather new. I have a formula that works fine, except that occassionally the result is a negative number.How can I display the result as "ZERO" if the result is less than zero (i.e. a negative number)?I read somewhere that MAX should do the trick... but not working.   IF({Total Sold}> 0, MAX({Total Sold}-({Products Completed}+{Total qty on hand})), 0)   P.S. As mentioned, I am new, so if you do write out a quick formula, could you please use MY actual field names? 🙂please advise, and thank you in advance!DJ

Hi, I'm working on a task list with two date fields - Action Date (the date to focus on something) and Due Date. Not every task has a due date. and not every task has an action date. Is there a formula I can use to pull either the earliest of those two dates (if there are two) or whichever date there is (if only one)? Thank you!

Hi! Is there a formula (or other solution) to remove extra whitespace within a string? My formula right now looks like:  SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM({name})," "," ")," :",":"),” .”,”.”),” ?”,”?”),” ,”,”,”) This works great to remove whitespace before/after the string using Trim and then remove double spaces and spaces before punctuation using Substitute. I'm wondering if there's a way to also remove instances of 3 or more spaces within a string without separately writing each possibility (3 spaces, 4 spaces, etc) as substitutes.Thanks for any help!

Hi!I'm having such a basic issue and can't figure out what I'm missing. I believe this should be a very basic IF statement: IF {field} is not empty, show this stuff. However, I've tried using {field}, {field}!="" and {field}!=BLANK() and even where there is nothing in {field}, text is showing. Here's the simplified formula I'm using to figure out what is going on (substituting "the text" for what will really be there):IF({abr formula- all},"the text")Here you can see that even if {abr formula- all} is blank, "the text" is showing up.Any help appreciated!

Hello everyone! So first off I have this formula field:   DATETIME_FORMAT( IF( DAY({Tilføjet d.}) <= 10, DATETIME_PARSE(CONCATENATE(YEAR({Tilføjet d.}), '-', MONTH({Tilføjet d.}), '-11'), 'YYYY-MM-DD'), DATEADD({Tilføjet d.}, IF(WEEKDAY({Tilføjet d.}) <= 3, 3 - WEEKDAY({Tilføjet d.}), 10 - WEEKDAY({Tilføjet d.}) + 3), 'day') ), 'DD/MM/YYYY' )  The name of that field is 'Kan laves d.' and shows me the date of the next coming wednesay based off of the date the record was created. So for example if the Record was created today, it will show me 25/2/23. So what I want to make now is a new formula field, that will add a green circle for all the records that can be made the next coming wednesay, and only for those.  Can someone help me with this?

I have a column 'Original' with the value: Breakfast-NO|Lunch-YES|Dinner-YESI want to the next three columns that are set to formula to automatically be set like this:OriginalBreakfastLunchDinnerBreakfast-NO|Lunch-YES|Dinner-YESFALSETRUETRUE Breakfast would be the string between the first '-' and '|'Lunch would be the string between the second '-' and '|'Dinner would be the string between the third '-' and '|'also want to convert the value from No to FALSE, YES to True

I have a survey set up with 5 of outcomes depending on the total amounts of points. To assign a certain score to a certain outcome I used the IF statement. See attached files. It does not work properly for at 60 points it should show 'Extreme angst'.Any thoughts?Thanks.   

Good day Airtable Team,I would like to ask for your assistance in a formula to compute for the due date based on the following scenarios:1. Due Date if Rush Tag is Yes: Variables to be used would be "Extracted for Ticketing" and "Rush TAT".2. Due Date if Rush Tag is No: Variables to be used would be "Extracted for Ticketing" and "Rush TAT".Thank you   

Hey I would like to know if there is a way to create a  summary per record instead of per filed? like, how may empty per record? Many thanks!! 

Inside this URL, I need to create a formula if PHYS is 100 or above and ASTR is 200 and above then divide the column of quality points by the column of credits. I also need to not include P, NE and add 4 credits if there is an F. Any ideas?

Airtable novice here, need help crafting a search formula that will find the record that contains both a First Name and Last Name.  So in the attached screenshot, I will provide "Harley" and "Comber" and need Airtable to flag record 3 as satisfying the search.  I can't search based on an exact match because sometimes the name as it exists in the table includes a middle initial, full middle name, jr., sr., etc. that the document I'm pulling the first name and last name off of to do the search does not include.  But I will always have the first name and last name available to search.  This is going in a Zapier routine that will provide the first name and last name for the search and then return the correct record so I can use the corresponding Matter ID in future steps.  So, basically I need a formula that instructs Airtable to find the record that includes both the [First Name] and [Last Name] variables.  Thanks for your help.

I am looking to score my leads in my Airtable which I use as a CRM. I have a multi select field called 'Journey' which has a number of events in the lead life cycle - for example an IOI (indicator of interest) of the prospect asking about the price, or the timelines - what I would like to do is rollup those Journey steps which are in the 'Touchpoints' table and have a lookup field for the Contact into a Contact rollup field and calculate the lead score which would involve assigning unique values to the Journey steps. For example an IOI - Pricing might be 5 points, an IOI - Timelines - might be 7 points (as an example) - adding all those points together and having a lead score. Hope that makes sense. Any input of how to make a formula like this would be much appreciated. Video below for reference also:https://share.getcloudapp.com/yAuAwJdW

Hello community!I have a column with 3 simple choices "number of trucks" and I wants to convert these choices into figures in a "quantity" column. IF({number of trucks} = "3 trucks of more", "0", )IF({number of trucks} = "1 truck (24 tons)", "24",)IF({number of trucks} = "2 trucks (48 tons)", "48",)I can't close my formula, do you have an idea?

Currently, the yyyy-mm that were paid are stored in a lookup field named Month Paid For.For instance, we only paid the vendor until 2022-11 even though we were supposed to pay them as of 2023-2. How do I obtain the months that have not yet been paid? ( 2022-12 , 2023-1 , 2023-1). Thanks !

Hello!Here is what I am trying to create:A formula that results in telling me if the same collaborator's scheduled leave is going to conjunct with another collaborator in the same team.This is what I am working with:leave start dateleave end dateTeam Slack ID which is a lookup field directly connected to the collaborator selection eg. if Mary JONES selects her name, the Slack ID for her teams HR and Marketing will show.I am trying to build a formula that will:be able to identify conjunction between leave periods.search if this conjunction is happening between collaborators who work in the same team.Has anyone some ideas to share?

Hello all, I have linked a SignUp Genius signup to populate a student table in my Airtable database automatically.  I have 3 custom questions, and Signup Genius exports those ALL in one custom questions field separated by commas.  I need to separate those responses into 3 separate fields in my base and cannot figure out how to set that up. One of the fields is a linked field to another table in my base. I'm assuming that I have to import that into one field in my table, then set up a formula in a different field that separates the comma-separated values.    I have no clue how to do this. 

Hope you guys can help! I have a list of projects ranked 1-28. If a new project is entered and the rank is set at #8, I would like all the following numbers to be adjusted. (i.e. original #8 becomes #9, original #9 becomes #10...).  Is there a clean way to accomplish this? Field type is "number" currently. Hoping to find a formula but that might not work.  

Hi all, Semi-new user to the tool here, and still learning the formulas. I have the following issue: I am using the switch function to categorize certain single select options into different categories (in this case, which statuses in the HR systems relate to an overall designation of accepted, dispositioned, interviewing, etc)My full formula is below. The issue is the very last line item: 'Doesn't Meet Min Qual', 'Dispositioned'This line has the apostrophe in the word "Doesn't", and that seems to be invalidating the formula. Everything worked great until I added that line, and then it breaks. I unfortunately can't change the wording of the option, since it comes directly from our system. Is there any way to get the formula to work, and ignore that specific apostrophe? SWITCH({Current HR Status}

I have three training sessions, and participants need to have completed all three in order to proceed. I'd like to have a formula that shows 'completed' when all three = completed and 'incomplete' when none/not all are complete.Thank you!

I want to link my URL column with my title column so that the Title is linked to the URL. Instead of having the URL, I have the Title but it is link to the URL, creating an hypertext link. 

I'm looking to auto-populate some date fields. Right now, you add the date and the day of the week auto populates but I'd like to be able to enter 2/14/23 in the "EST Release Date" field and have the rest of the dates in the column auto-populate. Is this possible.I'd also be copying and pasting multiple sections into one grid so each chunk would have to be independent of another chunk...if that makes any sense. I know how to figure this out in excel but I'm hoping this is possible in airtable, too! Thanks!