Formulas

Good evening everyone. Hope to find some help calculating a tennis player rankings based on their best 6 results out of 15 tournaments played. What would be the formula if tournament values are: T1, T2, T3…T15? Thank you!!

HI Community I’m relatively new to Airtable. I’m working to develop a prototype for a client. I need some advice. The project is creating a system to automatically adjust the time requirements for staffing based on forecast occupancy of a hotel. Here is the flow for data: The “Setup Table” is the initial setup for the staff for the hotel: minimum hours, hours req per room occupied, etc. The “Forecast Table” gives the hotel management to outline the occupancy for the hotel given a 2 week look ahead. I have 5 different Calc tables, because the hours are calculated differently. Think of these as junction tables. Right now I’m working to create the “Time Req Table” that aggregates the junction table. Next I’m going to build the Time tracking Actual table and then the Dashboard to compare the actual to forecast metrics. I’m

I have a base with over 1200 number fields and a few text fields. Is there a better way to sum all number fields that start with “M”? At the moment i am using the below formula which i regularly need to update every time i add a new field. SUM(M001+M002+M003+M013+M018+M019+M031+M032+M036+M039+M040+M042+M043+M045+M047+M048+M049+M050+M051+M052+M053+M055+M056+M057+M058+M059+M060+M062+M063+M064+M065+M066+M067+M068+M071+M072+M073+M074+M075+M083+M084+M087+M095+M096+M097+M099+M100+M101+M103+M104+M105+M112+M113+M116+M117+M123+M125+M126+M127+M128+M129+M135+M136+M141+M163+M172+M186+M187+M202+M206+M207+M208+M209+M210+M211+M212+M213+M214+M215+M216+M217+M218+M219+M220+M221+M222+M223+M224+M258+M280+M290+M294+M317+M327+M330+M331+M332+M333+M336+M363+M364+M373+M579+M580+M581+M583+M584+M585+M587+M589+M590+M592+M593+M594+M595+M597+M598+M599+M600+M601+M602+M603+M604+M605+M606+M608+M611+M612+M613+M614+M615+M617+M618+M619+M620+M622+M623+M624+M625+M626+M627+M628+M629+M630+M631+M632+M633+M634+M635+M

One of my workflow views is filtered to show jobs that have received a quote, telling me to process. However, some jobs have multiple quotes coming in from different vendors. If I filter to show the status “waiting on quote” and “received quote” I end up multiple waiting on statuses bogging down the view needed in order to process. Each project has a job number so one job number could have been assigned to multiple vendors, all required to submit a quote. If the quotes I am waiting on do not show up somehow in the received quote filtered view they could be submitted without receiving all the data. Is there a formula I can use to tell us “2 of 5 have been submitted” for instance?

Hi all, Ok I’m fairly new to Airtable so this could be an easy topic to solve for many, but not for me yet… I’ve got a Duration type field in a table called “Travel time”, which contains the time it took an operator to get to his assigned work location. The format of the field is “h:mm”. I’d like to create another field called “Paid minutes”, which basically would contain the total minutes of the Travel time minus 60 minutes, because that would be the actual duration of time he can claim for any overtime pay. If the Travel time is less than 60 minutes, the value in the Paid minutes should be zero. So the idea for the formula would be something like: IF(MINUTE({Travel time})>60, MINUTE({Travel time})-60, 0) …but as you might know, the MINUTE function is for datetime field and does not work for duration. Can anyone help me solve the issue? Thanks in advance!

Hey guys, I’d Like to use formula’s to create custom tags (multiple field type) from data entered in long text field. For example from this long text bellow, I’d like to be able to create tags “beef” & “lamb” in the products offered column (multiple field type). I wouldn’t want “eggs” tag be created, because the text doesn’t contain “eggs” for example Grassland farm raises natural grass-fed beef and lamb because it is good for the land and good for you. We realize everything comes from the sun. We harvest grass, the sun’s bounty, in the form of beef and lamb. Low fat, grass-fed meat tastes better, too. You can almost taste the sunshine. I’m thinking the formula would look something like this: IF (product description) Contains: “beef” THEN Create (Product) = “beef”

Hi Guys, We’ve freshly setup awesome initiative for Tech companies that want to help NGOs, we are getting many responses and submitions but not all of them are Tech Companies that we are look for. TechToTheRescue[dot]org there you can see our We have Live view for Grid on embeed on website + form under this. I want to have option to approve form submission before it goes live, is there such a option? Best, Tom

I’m currently migrating from using google sheets to Airtable, I have a majority of my formulas working, however, I need assistance with this: =IFS(A2>1000001,A254%,A2>=400001,A251%,A2>=200001,A247%,A2>=100001,A242%,A2>=80001,A237%,A2>=60001,A234%,A2>=40001,A231%,A2>=20001,A227%,A2>=10001,A224%,A2>=1,A221%) The formula looks up a value and depending on the amount it then calculates a value based upon a percentage. I look forward to any assistance I can get

I’m trying to understand how the Lookup function works but before I continue with that, I need to know how to create a formula field that gives me my current weight, date of the recording as well as the time? How can I do this? My fields are pretty simple. They’re called ‘Weight’ and ‘Time’ I want the formula field to look up both.

Hi guys, I just discovered Airtable and I am trying to use it as a database and a points calculator for a card-deck building game I am working on and now I am struggling with the scoring of ability combinations in a specific hand. I have the following tables: Characters - with all relevant character info Abilities - all abilities with desc and each ability is linked to each character with that ability Ability combos - a table with the scoring combos of abilities, for instance ability X and Y = 5 pts Current deck - simulation of a hand with 7 cards Cheat sheet - a table with all pairs of characters in “Current Deck”, i.e. Card 1 & Card 2, Card 1 and Card 3 all the way up to Card 6 and Card 7. What I need help with is a formula that in the “Cheat Sheet” looks up i.e. Card 1’s abilities and Card 2’s abilities and see if they match a combo in the “Ability Com

Hello, I have month end dates and product values. I’m trying to use the previous value in a formula. I started by trying to get the previous month-end date which I was able to get using a DATETIME_PARSE formula. My attempt to get the Previous Value hasn’t worked yet. In laymen’s language, it would be something like: Find the Value across the table where Date(in searched records)=Previous Date(in current record) & ProductID(in searched records)=ProductID(in current record) ProductID is linked to a Products base. I’m very new to Airtable and appreciative of any help. I tried to include a screenshot but my guess is as this is my first post it won’t let me. If I’m able to I will. Thanks

Hello I like to have a table with, let say 200 rows for 2000 rows. The 1st row will have a date that I put in manually and all the remaining row increase by one days for each row. The increase need to accommodate the change in number of date relative to the month. So after Feb 29 should be 1st of March. I guess if we have the 1st column as auto-number, there should be a formula for this, but still not find a simple way to do this in AirTable yet.

Dear Airtablers, I fully accept that I might be working non-intuitively, and am looking for help and suggestions. I‘m attempting to keep my accounts under control and wish to flag up the 4 possible statuses I can conceive - either an invoice is „Paid on time“, „Paid late“, „Open“ (unpaid but not yet late) or the worst kind „Unpaid and late“. As a beginner I‘m having terrible trouble but not much fun finding out how to do it. I have the following fields: Date invoice sent Terms in days Due date (automatically generated from formula) Date paid (blank if open) Pay cycle (in how many days did my client pay?) Days late Punctual/late/unpaid? What I (think I) want: In „days late“, I want to show 3 things. Invoice on time (thumbs up) Invoice late and unpaid (exclamation mark and number of late days)

Hello, I am trying to keep track of different screens that are active during a set amount of time. There is a “Start Date” column and an “End Date” column. I need a column that will automatically put a yes or a check when today’s date falls between the Start and End. I’ve tried this but it’s not working: IF(AND({Start Date} <= TODAY() , {End Date} >= TODAY()) , “ :white_check_mark: ” ) It’s saying that “Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.” Any ideas on how to get around that? Thanks!!

Hi, In one table (Table A) I’m using a Datetime_Diff function to calculate the difference (duration) between two date columns. Each record of Table A is linked to a date in Table B (there can be multiple records in Table A with the same date in Table B). In Table B, I’m trying to use the Rollup function to “sum” the durations calculated in Table A so that I can see a “daily duration total”. This returns a NaN value no matter how I try and calculate/add midsteps. Only work around is manually entering Table A’s duration as a duration field. Example here is Equipment downtime. Table A has the “time the equipment went down” and the “time the equipment returned to service”. This table also calculates the duration of time for each piece of equipment. Table B is a daily summary, and I’d like to be able to Sum the downtime for all equipment. Thanks in advance!

Hey there, In order to better manage my emailing campaigns and some of the auto replies I’m receiving I integrated my sending box with Airtable. To ease the cleaning I would like to add within a column a specific code based on the corpus of the auto reply. Here is an example of the formula I came up with: IF(SEARCH(“yyyy”,Corpus),“Click to validate”,IF(SEARCH(“XXXX”,Corpus),“Nothing”,IF(SEARCH(“aaaa”,Corpus),“Nothing”,IF(SEARCH(“bbb”,Corpus),“Nothing”,IF(SEARCH(“ccc”,Corpus),“Nothing”,IF(SEARCH(“ddd”,Corpus),“Unsubscribe & Check for address”,IF(SEARCH(“eeee”,Corpus),“Unsubscribe & Check for address”,IF(SEARCH(“fff”,Corpus),“Unsubscribe”,IF(SEARCH(“gggg”,Corpus),“Unsubscribe”,IF(SEARCH(“hhhh”,Corpus),“Unsubscribe”,IF(SEARCH(“iii”,Corpus),“Unsubscribe”,IF(SEARCH(“jjj”,Corpus),“Unsubscribe”,IF(SEARCH(“kkkk”,Corpus),“Click to validate”,))))))))))))) but it doesn’t seem to be working. Do you guys have any clue why? I may have to add new criteria moving

I feel like what I’m trying to do is simple, but I just can’t make it work. I want to remove the text in the brackets. Example below: [SUPV PROD - STAFF] Doe, John [SrDesign/Ani - FL] Doe, Alice [PRO/EDITOR - Ani] Smith, David I want to remove the all the text in the bracket and the brackets themselves. Desired results below: Doe, John Doe, Alice Smith, David The Bracketed text is always in the beginning of the string. Thanks!

I can’t seem to figure out this formula. Here is what I would like to do. Compares today’s date with a specific field date. If today’s date is after field date I want to multiple field A by 10. If today’s date is before field date, then I want to multiple field A by 50. Regardless of which calculation is done above, if the sum of the calculation is 1000 or above, then I want 1000 returned instead. For example, let’s say field A has a value of 50. If today’s date is after the field date, then the value returned would be 500. If however, today’s date was before the field date, then the value returned would be 1000 (even though the calculation would by 50 x 50 = 2500, the 1000 limit would override it). For the life of me, I can’t figure this out. Can anyone help?

I have a table and there in that table 3 fields are there, which contains related id. I tried ND(groupid='recafQ3BugoXXXXX') and AND(FIND('recafQ3BugoXXXXX', ARRAYJOIN(groupid, ','))) still no luck.

Hello all, I’ve created a simple form that our staff can use to log when an employee is absent. This works great as every morning our managers fill out the form for the staff absent/on leave/working remotely etc. I then have a sheet which filters by the date column “today” to display the staff. However if an employee is on leave for let’s say a week, we have put in two date picker fields example: Date From Date To Is there a formula which will filter the employees name as long as the date appears on/between the two date fields. Thanks!

I have a tab with a list of hospitals. Some of them have the tag “COE”. On an other tab, I’m listing the feedback gathered from hospitals. Some feedback came from many different hospitals. So I add the name of the hospitals on the column “requesters” Now, I want to sort my list of feedback, and put on the top the feedback coming from “COE” hospitals. How can I do that? I thought to use a formula to have “COE” on the name of my hospital, and do something like “IF on the requester list there is an hospital with “COE” on it, then check the box” (for example). But I have NO IDEA how do do this… and there is maybe an even easier way to do it… Thank you very much for your help!

Hello, I’m trying to create a bill spreadsheet for myself. I have a column for “Next Due”. I want a field to be labeled what pay period that bill falls into. I get paid bi weekly, so how would be the best way to do this? Basically I want to be able to group my bills by pay period to see how much is owed during that time frame, what is outstanding etc Thank you!

Hi all, Is there a way to sum the number of years passed (as a whole number, rounded down) from a listed date to present? I’m using this for an asset register. There are 4 important columns: A) date of purchase B) original purchase value C) annual depreciation (as a %) D) current value I’m looking to write a formula for current value (D) that will multiply the annual deprectiation % © by the number of years that has passed since the date of purchase (years since A), then multiply that by the original purchase value (B). For example, if 3 years have passed since purchase date, the original price purchase value was $1,000, and the depreciation is 16.67%, it would be ((3*.1667)*1,000), returning a current value of $500.10. How do I get that 3 to caclucate automatically in a formula?

Hi, I am currently trying to use an if formula to display emojis to highlight the status of a license (date field). I currently have:- IF(IS_AFTER(TODAY(), {END DATE}), “ :stop_sign: EXPIRED”, IF(IS_SAME(TODAY(), {END DATE}), “ :calendar: EXPIRES TODAY”, IF(IS_AFTER(TODAY(), DATEADD({END DATE}, -1, ‘week’)), “ :warning: EXPIRES SOON”))) The formula is working fine but I wanted to add ELSE display “ :heavy_check_mark: ACTIVE” but can’t seem to figure it out. Thanks

So I know this shouldn’t be all that difficult, and I get the results for my first two outcomes (Blank and 1 Year), but no years past Year 1 provide an answer. IF({Last Service Date}=BLANK(),BLANK(),IF({Service Frequency}=“1 Year”,DATEADD({Last Service Date}, 365, ‘days’,IF({Service Frequency}=“1.5 Years”,DATEADD({Last Service Date}, 547, ‘days’,IF({Service Frequency}=“2 Years”,DATEADD({Last Service Date}, 730, ‘days’, IF({Service Frequency}=“3 Years”,DATEADD({Last Service Date}, 1095, ‘days’, IF({Service Frequency}=“4 Years”,DATEADD({Last Service Date}, 11460, ‘days’))))))))))) “Last Service Date” is a Calendar Type and “Service Frequency” is a Single Select Type (Single Line Text had same result). In Excel I can use a different formula that works just fine, but is not recognized here