Formulas

Hi Guys,I am trying to wrap my head around regex formula but can't figure out what if there are two patterns in the string. Basically I want to extract the number after the bracket but before the "+".Current value : /Date(1676537746330+0000)/Desired value : 1676537746330 Thanks

Hello Airtable Community,     Question: My main field (first left field) is the "Date", next field is "Day of the Week" (single select). Is it possible to automate "Day of the Week" field so it would automatically choose the day based on the calendar date input in the "Date" field?   Thank you in advance for your help

I'm using make.com and have a connection to Airtable and am trying to do a simple search on a table. I can't find a guide on Make or Airtable on the syntax required in Make's Formula field.I would like to do something like the following logic{Email}='1.email' AND {Date} >= CURRENTDATEWhere Email and Date are fields in the table I want to search. I can't seem to find the equivalent of CURRENTDATE in airtalble's Formula Playground or elsewhere.

Is possible to have a unique id number from autonumber function across all tables?, For example: If I create a new record on table A, that record must be 1; but if I create another new record on table B must be 2

Hello dears,I need help please to do the following formulaIf multiple cells in the "Star" column matches each other in text and the matched cells has the options "in office" or "stock" in the "Status" column then mark all cells as {show}If multiple cells in the "Star" column matches each other in text and the matched cells doesn't have the options "in office" or "stock" in the "Status" column then mark all cells as {hide}The below image should clarify the need please i need help 

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 each time a record is created in Table A, a single record representing that product is created in Table B. So it would look like this: Th

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 TrueThank you.

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.