Formulas

I sometimes wonder why I still pay airtable money. For an application that claims to be good for time planning it’s mad it still doesn’t seem to be able to do time and date maths properly. I am in UK (so GMT/BST) - I want to have a date field and a separate time field, and to be able to combine these two into a calculated dateTimenothing I have tried seems to work reliably for this without jumping the date back to UTC Has anyone found a solution for what should be a trivial task?Thanks Richard

 Hi I am brand new to Airtables and have been looking around for a solution to this for a while but I have had no luck… I have two tables;One with Products which includes 2 different fields that display the royalty rates per product; “AU/NZ Rate”  and “ROW Rate”the Other table is a Sales Table and has a Region field that is either “AU/NZ” or ROW depending on where it was sold.How do I create a field that looks at Sales->Region and uses the value there to choose the value from  the corresponding field  in the Products table?  

Hi I would like to translate this formula to function in an airtable workspace, please help me.=IF(G2="Yes", SUMIFS(F:F, A:A, ">="&NOW(), A:A, "<"&A2, D:D, "Yes"), "-")

Im trying to use airtable record search in Make . . .I have a field called PostID. I want to search it for a number. I Cant find anywhere how a simple airtable syntax works for this  .. is it {PostID}=”123456” or do i put search or someting in front of it? 

how can we get data from API in Airtable? below is the sample API curl -X GET "https://apiv2.shiprocket.in/v1/external/courier/track/shipment/1559017361980" -H "accept: application/json" is it possible ?

Hi all,I am having trouble with a nested “IF” formula which I believe is correct because when I enter it into the formula field, it is accepted and saved. However, when I then click on “Edit field” to view the formula, it has “self-changed” the formula to something else. This is my original formula which the formula field is accepting:IF({Transaction Status} = "Order Filled", IF({Transaction Type} = "Sell", ({Total Cryptocurrency Value USDT}-({Value per Buy Cryptocurrency USDT (from Linked Buy Transaction (From field: Linked Sell Transactions))} * {Amount of Cryptocurrency})) * 0.35), IF({Transaction Type} = "Buy", IF({Total Sell Transaction Values USDT (from Linked Sell Transactions from Transactions)} = {Total Cryptocurrency Value USDT}, ({Total Sell Transaction Values USDT (from Linked Sell Transactions from Transactions)})-{Total Cryptocurrency Value USDT}) * 0.35, IF({Total Sell Transaction Values USDT (from Linked Sell Transactions

I am new-ish to Airtable. I am having an issue with creating a formula that give the correct output for contribution points acquired. I have a Total contribution points table, a points log table, And the master table with the contributors. I think I have the pieces in place but the category based formula is giving those with 1 point 100% and those with 2, 50% I am wondering if this is because along with a total count of all the points across all categories do I need to also collect the total points on another table of each individual category count? The current category count, counts only how many points a contributor has in that category. Secondarily, there is a weight to each category sor CAT 1 is 40%, CAT 2 is 40%, CAT 3 is 10%, CAT 4 is 10%. I hope this makes sense.

Scenario Field | Type | Value-------------------------------Amount | number | 100VAT rate | Single select | 13.5VAT amount | formula | {Amount (Net)} * VALUE(SUBSTITUTE({VAT Rate}, "%", "")) / 100 Interesting to see that the VAT Amount value is 14????It should be 13.5, well, mathematically speaking it has to be 13.5 With VAT rate as number, i

Hi,I need to pad a number to a fixed amount of digits (8), i.e. prepend with leading zeros when the significant number is less than 8.This is easy enough to do for integers with a substitute formula:RIGHT("00000000" & {Test SUM},8)However, in my scenario I have decimals and that doesn’t work, since a number 10.00 will count as 2 digits while 10.11 counts as four digits. Hence the padding will be off.I can replace the decimal character for the decimals with something else than .00, but the .00 ones still counts as two digits shorter.SUBSTITUTE( RIGHT("00000000" & {Test SUM},8), ".", "")The above substitute/right formula gives this result. The .45 decimal number is otherwise right but has one leading zero too little. The .00 number is off by two positions to the right (should be 00012300).

I currently have a table where I compare student scores to the national mean score. For example, I have 40 students and need to calculate the difference between each student's score and the national mean.I would prefer to do this without repeating the national mean 40 times in a separate column. Is there a way to achieve this with a linked, locked reference?In Excel, using the $ symbol locks the reference cell in another table (allowing you to only input it once). I know Airtable doesn't offer that feature, but I'm sure there's a workaround or method. Could someone share it with me and help?Example of what I want to accomplish in Airtable using excel logic:[Table B1: Student Score]-[Table 2: $B$2]  1550 - 1555 1549 - 1555 1540 - 1555 1536 - 1555 Where 1555 is in Table 2: B2, listed only once

I’m working with video timecodes using the DURATION field type, and am entering data in an HH.MM.SS.mmm format (H=hours, M=minutes, S=seconds, m=milliseconds). I need the data to reflect the values in the hours and minutes places even if those are zero. Right now, if I type in the data for one minute, three seconds, and 124 milliseconds, that data shows as 01:03:124 , but I need it to display as 00:01:03:124Similarly, if I type in the data as 15 seconds, 654 milliseconds, it shows as 15.654 , but I need it to display as 00:00:15.654 Anyone have a formula or workaround that will facilitate this?

Hello - I have a base with a parent table, Projects, and multiple child tables including a Milestones table. They are separated due to creating levels in a project update interface. When a teammate creates a new milestone, it does not pull into the Project table and when a new project is created, it is not pulling into the Milestone table. How do I correct this? 

So far, I haven’t had any trouble entering text and currency into Airtable bases, but I’d like to calculate simple overall percentages from my data. I’ve looked everywhere and can’t figure out how to do it in Airtable.I know how to do it at the row level, but I don’t know how to calculate it for the total data. For example, I have values in Column 1 and Column 2, and I need to sum all the data from Column 1 and all the data from Column 2, then divide them to get the overall percentage. Im trying to do this in the interface view too, but its imposible.I’m starting to think that it’s not possible to calculate simple total percentages in Airtable. Or am I missing something?Thanks very much in advance for any help with this.

A couple months back, I asked for help deleting lines of text beginning with the word ‘Highlight’ and was given this great formula: REGEX_REPLACE({Hook},".*Highlight.*\\n\n?","") It has been a huge time-saver but something I forgot to ask with my original question is if there is a way to add a blank line/line break where those lines were. I input data into the records that looks like:Highlight(blue) - Page 34 · Location 290“Look at that. That thing’s almost a book.”Highlight(yellow) - Page 34 · Location 292“You think… she’s going to print it? When it’s done?”Highlight(pink) - Page 34 · Location 294“I hope so,” I say as brightly as I can manage.  And when using the current formula, it comes out like:“Look at that. That thing’s almost a book.”“You think… she’s

Fairly new to AirTable, so this is probably really simple, but I can’t find anything helpful.When I have a Rollup field, I cannot expand the Formula field DOWN using the bottom corner handle - only to the right. This means I can only see about 1.75 lines of the formula. I’m copy-pasting in and out of Notepad to try to create/edit formulas because I can’t see the formula in AT.I can expand the Formula field down as far as 10 lines if the field type is Formula instead of Rollup. What on earth is going on here? 

I’m making an analytics dashboard for a client. They want to be able to see the top X records by viewcount, which we track. When I make a graph for this, there’s simply too many records and the bar chart looks a mess.I want to create a formula field that has a rank and I can include the top 5 records in a bar chart. What’s the right approach for this? I am not a developer and hoping to avoid an automation w script.

Is there a way for airtable to check if the single select option in my first row is different from the single select in the fourth row?The top three rows are respondent data from Pre-Surveys and the bottom three are respondent data from Post-Surveys.I want to calculate the percentage of participants who have been positively impacted by a programme.

I’ve got a database with events that I am using a list format to display -- the main reason for a list instead of a calendar is that I want people to be able to filter the type of event by tags and have a visual queue of event items. I’ve got the conditions of the list set to only display events that come after yesterday’s date, so once an event passes, it falls off the view. Additionally, this is a calendar that is populated by the community at large via a form.Most of the events are single date events, so the standard M-D-Y h:mm a format for the date is fine, but when the event spans multiple days (conferences and festivals for example), I’m having trouble figuring out the right formula to display these events with the series of dates (4/10/26-4/12/26, for example) so that they will stay on the view for the duration of their schedule. I know how I want this formula to function, but everything I’ve tried doesn’t seem to work. I’ve got a column for each event th

I could really use some help!I want my formula to return the correct Ship Deadline taking into consideration the time the order was submitted. If it came in before 4:00 pm on a weekday, it needs to ship the next day. If it came in after 4:00 pm on a weekday, it needs to ship in 2 weekdays.This screenshot shows that order 5014021 came in at 5:15 pm correctly shows it needs to ship 2 weekdays later. But for some reason, order 5014020 that came in at 7:15 pm on the same date is showing it needs to ship in 3 days instead.I can’t for the life of me figure out what I am doing wrong. Formulas are not my strong suit so I use AI to help write them. Any advice here would be very much appreciated. It will be so awesome if I can get this to work with anyone’s help!My formula:IF({Store Name} = "Retail",IF(

HelloI’m currently working on a Production Planning base, which is essentially a way for us to communicate with factories on the projected production they can expect from us for the forthcoming months. Currently there are two sets of data:Projected Units  Quantity attached to a “Launch” or a “Deal”.  Confirmed Units. Quantity of confirmed units which have Purchase Orders attached As an example, we have a deal across “P6 & P7” (period/phase) with a customer and projected around 500k units. The problem is that the factory is not going to produce all 500k units at the same time and this deal stretches over a period of time - which based on the customers sales calendar is Feb-Mar-Apr. That is their sales window, so we need to work backwards around 10 weeks for shipping and then an additional 30 days for production.I’m currently trying to figure out the best way to disperse the 500k

Today I noticed an issue with Lookup values—Airtable formulas are not recognizing them in conditions. Is anyone else experiencing the same problem?

Hi,We’re automating invoice creation, and need therefore to create payment reference numbers. I’d prefer to create these programmatically within Airtable.As a basis I wanted to use a autonumbering field we already have for creating unique IDs for the orders. Basically the base number will get an added checksum number, so that electronic banking systems can validate that the payer has entered the correct reference number.I found an Excel formula that creates the reference numbers according to our (Finland) banking standards (have tested, it works in Excel). I have been trying to convert it to Airtable, but I can’t get it to work.ORIGINAL:=IF(ISNUMBER(A2);(10*A2+RIGHT(10-RIGHT(7*MID(10000000000+A2;2;1)+1*MID(10000000000+A2;3;1)+3*MID(10000000000+A2;4;1)+(7*MID(10000000000+A2;5;1))+(1*MID(10000000000+A2;6;1))+(3*MID(10000000000+A2;7;1))+(7*MID(10000000000+A2;8;1))+(1*MID(10000000000+A2;9;1))+(3*MID(10000000000+A2;10;1))+(7*MID(100

Hi Airtable community! 👋I’m working on a base where I need status updates in one table to automatically reflect in another table — and I’d love advice on the best way to build this.BackgroundI have two tables:📌 Projects Project Name Status (e.g., “Planning”, “In Progress”, “Completed”) 📌 Tasks Task Name Linked Project Task Status I want to automatically update the Project’s Status based on the status of its linked Tasks. For example: If all Tasks are “Completed”, set Project Status to Completed If any Task is “In Progress”, set Project Status to In Progress Otherwise, Project Status should remain Planning Right now I’m using rollups and

I’m having an issue with a formula that rounds up incorrectly, i’ve browsed the forums and can’t seem to find a solution.I have a simple math equation x * y = zI suspect that the issues is stemming from a formula that contains four decimal points:I’m not able to round up this number, however, it needs to remain as is.  I’m not sure how to get a correct calculation using airtable. 

can someone help with this formula, when there are no dates in the fields it returns 12/31/1969, i’d like it to return a blank or “none”  DATETIME_PARSE(  MAX(    IF({Last Entered Patrol Start Date (from Patrol Reports Section 1)}, VALUE(DATETIME_FORMAT({Last Entered Patrol Start Date (from Patrol Reports Section 1)}, 'x')), 0),    IF({Last Entered Patrol Start Date (from Patrol Reports Section 2)}, VALUE(DATETIME_FORMAT({Last Entered Patrol Start Date (from Patrol Reports Section 2)}, 'x')), 0),    IF({Last Entered Patrol Start Date (from Patrol Reports Section 3)}, VALUE(DATETIME_FORMAT({Last Entered Patrol Start Date (from Patrol Reports Section 3)}, 'x')), 0),    IF({Last Entered Patrol Start Date (from Patrol Reports Section 4)}, VALUE(DATETIME_FORMAT({Last Entered Patrol Start Date (from Patrol Reports Section 4)}, 'x')), 0),    IF({Last Entered Patrol Start Date (from Patrol Reports Section 5)}, VALUE(DATETIME_FORMAT({La