Formulas

Hey All,I know that Airtable is based off of fields and rows, so it's inherently different, but I wonder--similarly to excel, Is there any way to set up a formula in Airtable so that if 'x' condition is present in field 'a' and 'y' condition is present in field 'b', then field 'c' will display as 'z' for the corresponding row?For example, can I say, if budget is < x amount or > y amount, budget size should display "Big" "Little" or "Small?"Then, based on the info under budget and budget size, could I input logic that says, "If Budget is < 'x' amount or > 'y' and 'Budget Size" is 'x' 'y' or 'z' then What to Do should display 'a', 'b' or 'c' for a under that field?Please help! Thanks!

Hello my friends. I have a seasonal task calendar that is organized by project WEEKS. Our team works from Week 57 until Week 30. Tasks are entered into the Primary Column with an associated Start Date and End Date. Currently, the Project WEEKS are set up as tasks as well to allow the Project Managers to group by project week. When team members add a new task, they have to manually add the corresponding project week. I don't have to tell you what a chore that is. As the team Airtable person, I end up cleaning up the missing or erroneous data.In the screenshot below, you can see that I've started experimenting hoping that it would lead me to a solution. Here's what I need in prose:If the task START DATE and END DATE fall within the start and end date of a particular project week, return that project WEEK (currently in TEST Weeks). Do I need to reference the project weeks in another table? Thank you in advance for your help.

Hello guys,I am an extreme beginner,I have my data as a date in the incorrect format, I'm looking to auto-convert each figure into a specific "date" category.Can someone please explain how to do so?Thank you so much in advance

Dear Airtable Community, I have a board with records organized with date field and category list.1 - I would like to count the number of days in this record. If 4 records are posted with the same date I want it to count 1.Ex : 01/01/2022 I have 4 records registered at this date, I would like to return 1 day for those 4 entries.2 - I would like to get the number of records registered per date, I tried to use Interface to do it but could'nt extract the number of dates Step 1.Thank you for your help.Best regards,Delphine 

Hi This is a File Path from Google Drive integration.BOOK 📘/CHAPTER/PAGE/PARAGRAPH/SENTANCE/WORDI am trying to create formulas to split the File Path into separate fields for each folder. I can get the name of folder and the parent folder but want to separate it all out so I can see subfolders etc. I have been trying variations of LEFT/RIGHT/MID and LEN but can't quite work it out.Any help much appreciated.Thanks, Maria  

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.