Formulas

Hello,I have a very simple formula that is taking the values calculated from other fields and giving a total.  The problem I am running into is unless I have the formatting set to 5 decimal places the results will round.  For the work we are doing we need the data to not round, but the issue is for the exports we have to do to input the data into another system we have to remove the extra decimal places. Is there a way to keep the 2 decimal place formatting while preventing the rounding?For example if a value is 2543.54546 we would like it to be 2543.54, but if I change the formatting to two places the number ends up being 2543.55 which for reconciliation is a problem.  The values change daily so the direction of rounding also changes.  My formula is super simple {value1} - {value2}I did try adding the ROUND function, but that didn't seem to help at all.  ROUND({va

Hi there,I have two tables and an interface with a form. The first form is used to add an entry to the table. I have this displayed as a nice gallery.I wanted to add a button on the gallery, so the user can click the button on the record and it will take them to another form to request a change to that record. I've got about 90% there, but getting the field to prefill with the record_ID has been miserable. I've tried every combination and it just doesn't work.On the form I added a new field "Link to another record". I select my primary table and select some columns to bring in via lookup. It looks great, the user can use the drop down to view all submissions and select the one they want. But the prefill doesn't work to auto select it. https://airtable.com/HIDDEN/HIDDEN/form?prefill_Vacancies=["rec1YlesldDRjs06V"]

I'm sure this an easy one - but I am stumped.  Here is the scenario: I want to create a view so I can send out a daily report that shows all records that are on tomorrow's service schedule, yesterday's service schedule, and 2 weeks ago service schedule.  The current airtable filter options of tomorrow and yesterday work great until it comes to the weekend - meaning on my Friday report, the Monday activity is not showing up and on my Monday report, the Friday activity is not showing up.  I have created formula fields that calculate the next work day but Im still stuck on how to filter it.  I tried to filter using "is after" "today" but then it gives me all records in the future when I just want the one next workday.  Maybe I am approaching this all wrong and making it more difficult than it is - but my brain is not computing 

i have a record table like this (simplified)idcreated at (datetime)category(text or link to category table)contentcatrgory record count (?)12025-1-2cat-1 222025-1-1cat-2 132025-1-1cat-1

Hello! I have a field in every record for zip code (number field) I am looking to have a sheet to count up how many records I have in a specific zip code. Interfaces can give me a chart on the zip count but I can not figure out how to get a table of it. I can group by zip code in the table by I need that data in it’s own table.

I am trying to track when i should expect a project back based on the amount of days given to return. The first return length is 70 days or 10 weeks. The second time it is submitted is 30 days return. Column: Submitted date: (constant)- user entered Columns: Expected Project Return (date) DATEADD({SUBMITTED Date }, 10, 'week')) This checks out good.  when I have a Resubmittal column(date) I would like the Expected Project Return Date to now show DATEADD({SUBMITTED }, 30, 'day')) So in short i want the expected return date column to update once the resubmittal date is updated. 

I have and “hours” table whose rows consist of the following relevant fields:An email address of an event participantNumber of hours participation in an eventYear of the event participationThere will be separate rows with the same email address for each event where hours are recorded.I want to create a query (filter, group by,…) that answers this question.  Which people (email address) participated in at least one event in both 2022 and 2023.Following one suggestion, IGrouped the rows by email addressCreated a new field, called “yrs active” that is defined by the formula “arrayunique(year)”.  On the email address group by line, it indicates the number of unique values found so I assumed I could apply formulas or filtering that operated on just those unique values.Created a second field, “In 2022 and 2023” defined by the formula, AND(FIND("2022", {Yrs Active}), FIN

Hi all,I have some entries in my data table for multi-year contracts. I have one field that shows the number of months the contracts span (using the formula (DATETIME_DIFF({End}, {Start}, "days"))/30). I used days instead of months because if a contract ran from 1/1/24 to 12/31/25, it would count that as 11 months.I also need to know the total of months in every year of the contract (e.g. 12 months in 2024, 12 months in 2025, 6 months in 2026). Can anyone help?Thank you!

We have a form which pulls the initial data, but there are situations where we need more information. We want to put a button in the interface that connects to a different form, but the url address is dynamic, not static. Ideas on how to accomplish our goal: Make the url address static so it goes to the form each time.Viewer/commenter could openUse an automation button that populates the form and updates the record it was triggered from. Can someone with viewer/commenter access use this button?Create a template that will link to the record, but don't think this data will be able to be pulled for reporting which is not helpful long-term. Can someone with viewer/commenter access use this button?

Hello guys,I'm struggling to achieve the desired results here. First: I have a formula that return a number between 1-156 based on the created time of a record:(VALUE(DATETIME_FORMAT(CREATED_TIME(), '0.smh'))*(156-1))+0000 Although I want this formula to return an integer and not with decimal values.What to I need to change here? Thanks in advanced.

I have a table with the following fields:Capacity Required - a number Capacity Provided - a roll up sum of a linked recordGap Remaining - the delta between Capacity Required and Capacity ProvidedI have another field called Status that looks at the Gap Remaining and provides a single select option The following are all nested, so will be triggered in order: If the gap remaining is negative, the status is "overstaffed"If the capacity required was 0 to begin with, the status is "N/A"If gap remaining is 0 the status is "fully staffed"if the gap remaining is less than the capacity required the status is "partially staffed"The status field behaves as expected for 1207 records in my table. But now I have a record where when the gap remaining is 0 the status is partially staffed...if and only if one of the values being rolled up is ".10" and 

Hi all,I'd like some help w/ a rollup field.Table A is a summary record for an order and table B has the line items of an order.  Each line item can have a different delivery date.  I'd like to create a rollup field in table A that calculates the difference between the min and max delivery dates of all line items in table B.   This rollup field presently looks like: DATETIME_DIFF(MAX(values),MIN(values),'days').  When I do this, I always get values of 0 returned.  I suspect that I cannot use a datetime_diff function within a rollup but I'm not getting an error so I wanted to confirm?  (I also just tried max(values) - min(values) but that doesn't work either, (also returns 0s).  I'd rather have a single field in my data to perform this function over 3 (one for min, one for max, one for the differen

Let me preface by stating that I am new to airtable.I have 1 table that lists every compartment of a project. This table has a priority field that changes as the project progresses. I have another table that lists the tasks of every craft/discipline in all compartments. Each compartment has numerous tasks in it. Each task has a compartment priority field. I would like this field to be automatically updated to match changes made to the priority level in the compartment table. I'm sure this is possible and have attempted to do so with a script but have been unsuccessful so far. 

My phone number field has rows with different formatting. e.g. 12824673737 and (510) 890-9699 How can I force all the phone numbers to be of one format: namely, a 10 digit number without any punctuation in between

I have a board for projects and one for tasks. Team members are pretty good about entering their project due dates on the project board, but we need to have due dates on the tasks as well. Id like the task due date to default to the project due date, with the ability for override. Ive created a lookup field on my tasks board to find the project due date (default date), but often the tasks need to be done before the project is due so i would like it if the field could be manually overwritten. I was thinking if i created another column in my task table with a formula that allowed team members to overwrite the lookup field this could work. It would say something like {lookup date} OR manual overrride.Is there a way to create something like this?

Help! I'm trying to get an estimated calorie count using workout time, weight, and intensity. My workout time field is a lookup field formatted as duration. The weight is 160, the duration is 39:00 and it is moderate intensity. The formula is returning 16,770 instead of 279.5.  What am I doing wrong?Original formula:IF({Workout Type} = "Primary", IF({Workout Intensity} = "Low-Intensity", {Primary Workout Duration (Minutes)} * (4 + (({My Current Weight} - 125) / (185 - 125)) * (6 - 4)), IF({Workout Intensity} = "Moderate-Intensity", {Primary Workout Duration (Minutes)} * (6 + (({My Current Weight} - 125) / (185 - 125)) * (8 - 6)), IF({Workout Type} = "High-Intensity", {Primary Workout Duration (Minutes)} * (8 + (({My Current Weight} - 125) / (185 - 125)) * (12 - 8)), 0))), 0)

I have a field that sometimes has a mentor's name in it, and other times (usually) is empty.I am showing that field in a timeline - which is fine when there's a name, but otherwise it shows up as "(empty)" which is annoyingI want to create a formula field that shows the value when there is a value (a name), but shows something innocuous - like an asterisk - when it's empty.I feel like this is an IF statement - but the value of the field is different all the time - Joe Schmoe, Jane Doe, etc.IF({Mentor (from Link to Forms)}, "", "*") is what I'm trying now, but it's giving me a blank where I want the name. What is formula for "the value of the field

Hi there- I have a list of around 200 e-mails that I would like to search for in my table, so the “filter” function is not going to work here. Is there a simple workaround that will allow me to create a grid view of just these 200 e-mails, from a list of thousands? Thanks!

My challenge is to do a search that looks for records where my input is part of the company name. So essentially I am looking for a "contains" function.Example searches:1.Input: "sephora"Target: "company" field in record: "SEPHORA Inc."2.Input: "GoSports"Target: "Go Sports Inc."I guess the second example won't be possible but my plan is to just get all the matches based on company name, then name of the record and description and then let AI do a matching by probability and my other form inputs.I am using the search module in Make.com.My guess was to use either "SEARCH", "FIND", or "REGEX_MATCH" but no luck so far with my tests.

Hello, I have a lot of text in a cell LONG TEXT (I extract a little bit of this), I need to create a new Formula column to extra only the telephone number, what can i do? Buonasera, Chiedevo disponibilità per parziale permuta con mia autovettura MASERATI 4200 SPYDER del 2002 ASI - TARGA ORO 37.000 Km circa colore Argento scuro capotte e interni BLU. Macchina sempre tagliandata da rete ufficiale kilometri REALI io sono il 3 proprietario. La settimana scorsa ho inserito annuncio su AUTOSCOUT 24 .. chiamano in tanti ma tutti senza soldi.. Cordiali salutiRispondi ora:MARCO PxxxxNI E-Mail: praxxxxxx@gmail.comTel: 339xxxxx00===Veicolo===========================================

Hi there, I have used the following formula based on a guide, however, I would like to use standard fiscal years/quarters and this formula is not accurate somewhere. For instance, I have a date set in my table as 11/9/20 and it displays the quarter and fiscal year s Q1 FY’21 - the formula is below: IF(OR( FIND(01,DATETIME_FORMAT({Date},‘MM’)), FIND(02,DATETIME_FORMAT({Date},‘MM’)), FIND(03,DATETIME_FORMAT({Date},‘MM’))),“Q1”, IF(OR( FIND(04,DATETIME_FORMAT({Date},‘MM’)), FIND(05,DATETIME_FORMAT({Date},‘MM’)), FIND(06,DATETIME_FORMAT({Date},‘MM’))),“Q2”, IF(OR( FIND(07,DATETIME_FORMAT({Date},‘MM’)), FIND(08,DATETIME_FORMAT({Date},‘MM’)), FIND(09,DATETIME_FORMAT({Date},‘MM’))),“Q3”, IF(OR( FIND(10,DATETIME_FORMAT({Date},‘MM’)), FIND(11,DATETIME_FORMAT({Date},‘MM’)), FIND(12,DATETIME_FORMAT({Date},‘MM’))),“Q4”)))) & IF(AND(VALUE(DATETIME_FORMAT({Date},‘M’))>=6, VALUE(DATETIME

Hi everyone,I desperately need help as this formula is driving me crazy! Basically the formula works except for Jan - Feb and March 25 and shows them as Q2 24 instead of Q$ 24. This is the fiscal breakdownQ1: april24,may24, juen24 ->  Fiscal year (e.g., for May 24, Q1 FY’24)Q2: July24, August24, September24 → Fiscal Year (e.g., for July 2024, Q2 FY'24)Q3: October24, November24, December24 → Fiscal Year (e.g., for October 2024, Q3 FY'24)Q4: January25, February25, March25 → Fiscal Year (e.g., for January 2025, Q4 FY'24) Here is the formula:"Q" & ROUNDUP(  (MOD(MONTH({Month})

Hello Airtable community! I need your precious help as I’ve been spending the last 2 days trying to figure this out and can’t seem to do what I want done here… In one table I have a field name “Country” and another field right next it that’s named “States”. I am trying to show a selected number of options in the “States” field based on what has been selected in the “Country” field. e.g: I choose “USA” between all other countries available in the (single-select!) field “Country” and I want the field “States” to return ONLY the results corresponding to States with the USA. So, depending on what’s selected in field “Country”, it needs to show only a few selected options in the field right next to it. If I choose “USA”, I want the column next it to give me the 50 states (Choosing between California, Nevada, etc) If I choose “CANADA”, I want the column next it to give me the canadian states only! etc… I have tried creating a different table with all i

Hi there, I'm trying to bring a value from a previous row into another row, matching on two conditions.For context, I am ultimately trying to compare the difference in revenue between two rows, submitted by the same company, in adjacent quarters.Does anybody know how this could be done? Thank you!

Hey Gang.I know there's probably multiple ways to do this but I'll ask.If there's an automation way of stripping/grabbing two(as there will always be two) linked records in a field and placing the first in the "first instructor) field(email type), and placing the second in the "second Intructor" field(also an email type).Obviously, I'd need to grab, each of those and place them in their respective field.Simple enough, right?Or if just placing a formula in a field to reflect those emails, that'd be fine too, I guess. thanks