Formulas

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).

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

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.

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

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

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 ?

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

Have a form that a record picker is used to select a client and am trying to get a encoded URL into a button that can be selected from within an Interface page and have the client pre-selected.Have made plenty of Forms pre-filled encoded URLs (Button linked to formula building the URL), but cannot get this one to work - only seems to be for the Record Picker elementHave other URLs for this exact form that work fine, eg:https://airtable.com/appgAgIX5zNGRiox4/pagqzLgwghd7xBloc/form?prefill_Action+Type=Maturing+Term+Deposit&prefill_Task+Name=Maturing+Term+Deposit+-+(Bank)(Amount)(Date)

Disregard...no way to delete

// change these names to pick a view:let table = base.getTable('Invoices');let view = table.getView('Grid view'); let result = await view.selectRecordsAsync({fields: ['Amount']});let runningTotal = 0;for (let record of result.records) {    // change the field names here to adapt this script to your base    runningTotal += record.getCellValue('Amount');    await table.updateRecordAsync(record, {        'Running total': runningTotal,    });}

I'm creating an athlete database. I want to create a column that shows where an athlete ranks, for various metrics, relative to everyone else. For example, let's say someone is 6'9" tall and that puts the athlete in the top 2% of all the athletes in the database. How can I calculate that for that athlete (and every other athlete)? Is that possible?

Hello - We are creating a way to track social media posts for our clients. Clients will ask us to make a certain number of social media posts on their behalf, we then want to keep track of how many social media posts have been made and how many are still required for each client contract. Here are the two tables we have created Social Media Posts Created - Airtable - Content overview Social Media Contracts (requests) - Airtable - All content and requests I’m new to Airtable, so this may be a simple formula, but any help would be appreciated.