Formulas

Hi there! I am needing help to add ONE layer to this IF formula. It’s for an invoicing tracking base. Overall, we are looking at 2 columns. I found this super epic IF Formula here on the forum and need help. The OUTCOME: *This first part I need help with - If the Paid Date exists = PAID (the rest, the formula I have works) If the Invoice Due Date is same day = DUE TODAY If the Invoice Due Date is within 5 days = PENDING If the Invoice Due Date is 5+ days = UPCOMING If the Invoice Due Date is past = PAST DUE Here is a pic of the columns I am working with Here is the formula I am working with, I am fairly sure I need to change the first layer?? IF ( {Due}="", “Not Set”, IF( AND( DATETIME_DIFF({Due},TODAY(),‘hours’) >= 12, DATETIME_DIFF

Hi. New to Airtable. Looking to add a formula field that calculates a value (rating - via stars) based on data in other fields. In the attached example, I’d like the ‘Expected Rating’ field to return a star rating (via star emoji) based on the completeness of the other fields.

Hey there, trying to get an IF/THEN statement correct here. It’s a donation database. Each donation is linked to a Donor Account and a Campaign. In my Donor Accounts tab, I’m using a lookup field to determine which Campaigns each donor has donated through. In another column, I’d like it to populate “Yes” if the donor has already given/is tagged to a particular campaign, “No” if they haven’t. Thoughts?

Hi all, Could anyone help me with this please: I have 2 columns of Attachment (images) and want to do what I normally do with email addresses and dates etc; namely have a new formula field that checks whether an image exists in one field and if that’s a No, use the image from the other field (in my case as the cover in a gallery). (1 image is from a linked table field, the other a direct attachment (if exists)). So, ‘date’ might be like this: IF({Date Published},{Date Published},{Date Accepted}) But ‘attachment’ gives me the AT field content like this: untitled.png?w=640 (https://dl.airtable.com/.attachments/a0dfdghtt8876ad82f549fadb408/2164e9ef/untitled.pngw640) I can split/extract the field components eg get the actual URL via something like: RIGHT(LEFT({Journal Image}, LEN({Journal Image}) - 1), LEN(LEFT({Journal Image}, L

I have a table for keeping track on contract lengths on customers and i want to get a notification when contract is up for negotiation. So, per default the contracted time is 12,24 or 36 months. And since we have clients that signed contracts a couple of years ago, we’ve passed the original due date, but our contracts gets automatically renewed 12 months, if not terminated. I’m struggling somewhat with organising the fields, what i want to achieve is basically: Signed date Expiry date (Signed date + Contract length) Contract length (a # field) Date for negotiation (Expiry date minus 6 months) But i also want that if expiry date has passed, a new expiry date 12 months in the future should be set, which also updates the ‘Date for negotiation’. Any ideas would be awesome!

Helo, I’m sorry but i don’t speak english very well… I would like to invert the result of the ARRAYJOIN function and get all the results that are not selected Thx

Hello! I’m trying to simply show the aggregation of two lookup fields into a third column while keeping the linked values. Concatenate will de-link the data as it only works with text values- how do I carry the link through concatenate? Is there a formula or AirTable workaround that will join data without losing the links?

Hello Dear Community! I am having not much fun concatenating minutes into hours. In the image, where it says (for example) 75.16 minutes in line 4, I would like it to say 01:15 (or even 01h:15m. My formula is below. I know it’s something to do with DATETIME_FORMAT, but what do I put in and where? When I tried inserting: & DATETIME_FORMAT({TOTAL Bearbeitungszeit}, 'h:mm') Airtable got upset. How do I do it, guys?

I have a table with two of the fields called msgId and ID. msgId is generated by my app, whereas the ID is the auto number assigned to the record by Airtable. In my request (via the JS client library), I’d like to request data the following way: Lookup a record with a given msgId and find the ID of that record, let’s call this required ID. I’d then like to sort the table by IDs desc and retrieve five records just before the matched record, i.e having the ID less than the required ID I found initially. I’m thinking of two separate requests here, first get the ID related to the msgId by doing a filter by formula search function. Then send a second request, retrieve five records less than the ID. Is there any way to do this in a single request?

I would like to set up a resource planner base that allows me to track the following: project start date, project end date resources allocated to the project for specific periods of time during the lifespan of the project (start date end dates) resources could be allocated to one or more projects for various time periods within the lifespan of the project. (their role is the task they will be undertaking e.g project manager, contracts manager, design manager) during a planning period, e.g defined by start date and end date I would like to be able to see if I have resource availability during this planned period so i can reallocate them to another project if so required). I have looked at the resource planning base Resource Planning - Airtable Universe. However resource is allocated to project by % time and availability then i

I would like to set up a resource planner that allows me to track the following: project start date, project end date resources allocated to the project for specific periods of time during the lifespan of the project (start date end dates) resources could be allocated to one or more projects for various time periods within the lifespan of the project. (their role is the task they will be undertaking e.g project manager, contracts manager, design manager) during a planning period, e.g defined by start date and end date I would like to be able to see if I have resource availability during this planned period so i can reallocate to another project if so required). I have looked at the resource planning base Resource Planning - Airtable Universe. However resource is allocated to project by % time and availability then is dependan

Hello All, Sorry if this is an easy question, but I looked some of the articles and I am totally lost ^^ I have a table 1 like this listing my subscriptions internal code date of the subscription starting date of the subscription ending mensual fees I want next a view or a table or an app aggregating by month my revenues ! What seems to me the easiest way was to create a Table 2 like this : month internal code linked to table 1 subscriptions start dates subscriptions end start rollup fields summing the mensual fees And I am looking for a way to show in the “internal code” field only the one where “month” is between start date and end date. I have not found a way to do this ? I am lost :frowning:

Hi all, I’m working with a set of formulas for calculating a page count estimate for transcription based on length of audio recording, and then based on that page estimate, calculating a price based on a per-page and copy rate. Here’s the formula I’m working with for the page estimate: ({Recording Length}/3600)*50 And the formula for calculating the price is: ({Page Estimate}{Job Rate})+(({Page Estimate}{Additional Copies})*{Copy Rate}) The problem I’m having is that even though the {Page Estimate} field is set to be a whole number integer and displays as such, when the {Page Estimate} field is used in the price calculation, it’s not using the rounded integer but the precise decimal amount. For example, the page estimate is 233.666, rounded to 234 as a whole integer. But instead of calculating 234 * {Job Rate}, it’s using 233.66666 * {Job Rate}. Thanks for any ideas to solve this!

I have a lookup array of 6 scores, and I’m trying to see if there’s a formula where I can automatically grab and SUM the lowest 4 scores out of the 6. Does anyone know if this is possible? Thank you!

Inglés HI. I need to convert the following: I insert a number in a record, for example, 155. I need to convert or divide 155 into multiples of 40, 30 and 5. That is, I need the following result in a column: (3 + 1 + 1) … 3 * 40 = 120 1 * 30 = 30 1 * 5 = 5 Is it possible to get it? I need it very urgently! THANKS!

Hi everyone, I’m new, this won’t be clear. I’ll do my best. I have a sheet in a database that lists all the special words used in particular Greek scrolls. I have 19 total records (so there are 19 scrolls), but there are many fields within those records. I want to be able to count all the words in those records. For example, I want to know how many times a particular term appears in those 19 records. (So, it would tell me that it appears twice in scroll 1, three times in scroll 4, etc.) Is this possible?

Can someone assist and ‘teach’ me where I’m going wrong, please? Trying to pull data from Field B if Field A is blank. Here is the formula I’m currently using, which doesn’t return a value. IF({Field A} < 5000, {Field A},IF({Field A}=BLANK(),{Field B})) This formula works to pull data from Field A but Field C is blank, not fulling data from Field B. I don’t get a formula error, the field is only empty.

Ok, to get it out of the way, here is the formula: IF( {Which Iteration?} = ‘This Iteration’, ‘New’, IF( AND( DATETIME_DIFF({Created Date in Airtable}, {Custom field (Done Date)}, ‘days’) <= 19, (IF( {Custom field (Released Date)}, DATETIME_DIFF({Created Date in Airtable}, {Custom field (Released Date)}, ‘days’), DATETIME_DIFF({Created Date in Airtable}, Resolved, ‘days’)) <= 14) ‘New’, ‘Old’)) Hopefully it comes through semi-formatted, never posted before. Essentially, logic aside, I’m trying to figure out why it’s an invalid formula. That being said, the logic I’d like it to have is as follows: If the Bug is marked as belonging to ‘This Iteration’ (a formula column I have), mark it as new. Else, if the Bug was created in the past 19 days AND it was either Released or Resolved in the past 14 days (this iteration), mark it as new. ^ (What I would also like to know is whether or not DATETIME_DIFF returns a val

Is there a way to turn off the autocorrect feature? It’s autocorrecting words that I don’t want changed and I can’t seem to find a way to get it to stop. Mars is being autocorrected to March and Pisces is being autocorrected to fish.

Hi there, I am struggling to integrate make multiple lookups in one table to add shipping cost to my overview: I have on table containing all my Order Data (order number, customer, product, shipping address, price) and I would like to add a column with my shipping cost to easily calculate the profit that I make (Price - Shipping Cost - COGS). This table contains a column with product variant and shipping province. I have a second table with my associated shipping cost: State, Type of Shipping, Cost of Shipping for each variant - there are 7. (Shipping costs are dependent on State and Variant) I have a third table with all my variants and associated COGS. I don’t have an issue integrating my COGS to the Order Data, since those tables are linked. How do I get my shipping cost? I was able to link Order Data and Shipping Costs via the State column, but how do I avoid integrating all 7 columns as lookups and then write a long IF formula? That kin

My company has a warehouse full of shelves with differing heights. Rows are lettered, individual racks are numbered (rolling over into letters), horizontal locations on the shelves are numbered 1-4, and shelf height is lettered there is also sometimes a bin number at the end between 1 and 20). Examples: “M74M 15”, “Q11B”, “ZQ3A”, etc. The shelves have different vertical spacing depending on the parts, so it can be hard to tell at a glance whether the part you’re picking can be grabbed without getting a ladder or lift vehicle. Rack H4 has small spacing, so you can easily pick anything up to shelf G, but H5 has larger parts, so anything above C needs a ladder or vehicle. My current solution: searching for the rack number of each part in a spreadsheet that indexes it with the max shelf you can reach. I have a field for the location of each part in our Airtable Database. I’m using formulas to extract the rack number… LEFT({WH Location} , 2)

Hi All, I have a great working time sheet base that calculates day totals and accrued vacation and sick time, but now I want to be able to subtract USED vacation and sick time from those accruals. I’m wondering if I can use check boxes to identify entries that are sick or vacation and the work those checked boxes into a formula that subtracts the identified records from the total accrued. Here’s a screenshot: Thanks for any help you’ve got! Heather

Hey! I’m using a Zapier and connecting to dropbox. In my Zap, it drops the a URL of the file uploaded into dropbox back into my Airtable table. Problem is, I don’t want my users to have to use dropbox. Luckily there is a feature through Dropbox that allows you to turn any share link into a direct download link by adding a “1” at the end of the URL instead of the 0. Problem is, the Zapier API is giving me a shortened dropbox link… I can’t change this… is there anyway that I could expand the short link in another cell? That would allow me to create a formula that’d change the last digit of the URL into a direct download link. Thanks in advance!

I have a Zap that brings invoice information from Quickboks Online into AirTable. Many of these invoices have multiple lines for different charges (material, labor, etc). My Zap brings over the descriptions of the charges in one AirTable column and the charges are put into another column. I have then created columns that take this information and put it into lists. I want to combine the two lists such that the charge ends up with its description. For example: Faucet: $500.00 Installation: $100.00 Currenly when I try to combine the list the result is: Faucet: Installation: $500.00 $100.00 BTW - I have tried to combine the two within the ZAP itself and I get the same result. Thank you - Stephanie

I have a field called “Category” where each of the categories are plural (i.e., “Widgets”, “Cogs”, etc.) In the first field (record) i have a formula that grabs a product name, concatenated with the “Category” at the end. I would like to have the “s” removed from the category name. Here’s my current formula: MFR & " " & {Model} & " " & {Category} So essentially, I would like to remove the last letter, from the {Categroy} name. Thanks in advance for any help on this!!