Formulas

I’m trying to figure out a formula to give me the following info in our age of Corona… I want to put a column on our main table that returns either a ‘yes’ or a ‘no’ based on data from 2 other tables. I’ve got the tables set up to give me a ‘1’ for a count so all I want is for the column on the main table to read data from those other two tables and give me a yes or no… possible?

What’s wrong with this formula? It references a single field named ‘Date’. That field usually will have a valid date in it, but in some cases the field might be left empty. In a separate formula field I’m trying to use this formula but it’s not working. IF ( Date = BLANK(), “Date Missing”, DATETIME_FORMAT(Date,‘YYYYMMDD’) ) What’s wrong with that? How else do I test to see if a field is empty? William

I’m having people place orders for a sale online. I’d like to have a rollup form of their totals, since the only tidy way to submit orders is one item per submission, so there will be several lines. The buyer is submitting their e-mail with each order submitted: person1@email.com 1 shirt $10 person1@email.com 2 shoes $25 person1@email.com 10 chairs $7 person2@gmail.com 2 shirts $18 I’d like to be able to somehow summarize the total amount per e-mail address. I can figure out how to do it if I were inputting the e-mail addresses ahead of time, but not when they’re coming in from a form.

Hi everyone! Super new to Airtable (as of today actually) and I’m trying to have a “Status” column update based off a horrendous nested IF statement referencing multiple other columns, shown here: MD Signoff (Not Started, In Process, Pending adjustment, Complete) RN Signoff (Not Started, In Process, Pending adjustment, Complete) Rx Signoff (Not Started, In Process, Pending adjustment, Complete) I’m using the following formula (please don’t make fun of me :frowning: lol) but am only getting some of it to work as expected: IF( AND( {MD Signoff}=“Complete”, {RN Signoff} = “Complete”, {Rx Signoff} = “Complete” ), “Complete”, IF( AND( {MD Signoff} = “Complete”, {RN Signoff} = “Complete”, {Rx Signoff} != “Complete” ), IF( AND( {MD Signoff} = “Complete”, {RN Signoff} != “Complete”, {Rx Signoff} =“Complete” ), IF( AND( {MD Signoff} = “Complete”, {RN Signoff} != “Complete”, {Rx Signoff} != “Complete” ), IF( AND( {MD Signoff} != “Complete”, {RN Signoff} = “Complete”, {Rx Signoff} = “Complete

How can I achieve the following from Excel in Airtable? In a time table of “Events”, the StartTime of an Event begins when the EndTime of the previous Event ends: A B C Record/Row: StartTime Duration EndTime 1 14:00:00 00:30 14:30:00 2 14:30:00 00:45 15:15:00 In Formulas in Excel 1 14:00:00 00:30 =A1+B1 2 C1 00:45 =A2+B2 I would be grateful to anyone who can help me figure out how to do this. Thank you! Peter

Why would this return blank and no errors? IF(AND({Status}=“AM: Follow Up on Proposal”,{24 Hours}=“2 Days”,“yes”,“no”)) Fields: Status=Single select 24 Hours=Formula

Hello all! I have an airtable issue where I’m trying to set up a notification for a field when Training Completion Date is prior to 1/3/18. For more clarification, the indicator is being used to know when to retrain my coworkers but I’m having trouble creating the equation if one exists. I’m still fresh to airtable, but I already see the appeal. If someone can help me create a formula for a column I would be very grateful. Thank you all for your time.

Creating a table for bidding purposes and after my first field I realized my formula table is going to be crazy! (Trying to avoid another field option) As seen in the screen shot I have (SF) Costs which is a formula for the cost per SF. However, not all cost are the same. Without adding a tremendously long formula for each product, possibly by SKU as the variable, is there a way to add the difference per SF cost? Thank you.

I am looking to create a formula that enters a date when the status of another field is changed scenerio: a field called “Job State” is changed to “sent to writer” and then the formula would fill in the date that action was made I am then going to build a Zapier Zap to kick out a status update email to the client

Hi all - I’m new to Airtable, we use it at the Transportation Authority I work for. What I am looking to do is create a database of bus schedules by their shipping quantities (amount). Most schedules are distributed to multiple locations, which I have a table for each. Quantities of schedules per route vary from location to location. I am trying to get the total quantity of each schedule - across all tables - to the Total page, where I can view the grand total quantity per route. I thought I was able to use the rollup feature, but I cannot get it to link multiple columns to provide me with the outcome I am looking for. Maybe I need a formula? Any assistance is greatly appreciated. Thank you in advance!

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 wondering if there is a better way to do the junction tables. Since I plan on creating a form to continue to add to the forecast table, I want to automatically update t

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+M636+M637+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 Combo” sheet and if it does, then it use a lookup formula to retrieve the points that the combo scores. A made up example: Card 1 ha

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) Invoice late and paid (alarm clock and number of days late) So far I managed: IF({Pay cycle in days} >14, DATETIME_DIFF({Date paid},{Due date},"days")) How do I nest the next two settings,

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!