Formulas

Hi…I have a field with ‘arrival date’, a field with ‘terms’ (which is days prior to arrival payment is due. This is a number and varies from record to record)(Both are lookup configurations) and I want the third field to calculate the date it would be if the terms number (such as -3) is subtracted from the ‘arrival date’. I tried DATEADD({arrival date},terms,‘days’) but it didn’t work. I experimented with DATEADD({arrival date},-3,‘days’) and with the ‘terms’ look up field just being an integer instead of the look up and those did work so I am not sure how to do this. Thank you!!

Hello, In my base, one table has 2 records each. Currnetly, i do a lookup and it shows me both records. I only want the first to show up because it just doubles the name twice. How can I make it so that when i lookup from that table, only the first (or last) record shows vs having both.

Hi can you please help me return the value "qc" from this "inventory tags column"this formula doesn't seem to work. Thank you!

Is there any way to search for emoticons (any of them) in a text field, and replace with something like a dash or space? Is there a code for emoticon that allows a formula to search for this? Emojis and emoticons are often put into titles of blogs, but I am curating titles and creating url’s out of them, but want to remove any trace of these.

Hello guys! I’m new here and I’m still learning about Airtable, but I’m really enjoying it! I have some doubts about formulas, I would be very happy if they work for what I need, here are my doubts: 1 - Is there any way to add numbers in a column automatically every time you turn the month? For example: Column “parcels” on 12/22/2021 = 1 on 01/22/2022 = 2 and so on? This would be very useful because here in Brazil we make purchases in installments, so the number of installments, changing month by month alone, would make it much easier. 2 - Is there any way for the formulas to understand the “unique selection fields”? I would like to create a formula like: If the “unique selection field” has the option “green” the answer is 1, if it has the option “purple” the answer is also 1, but if it has the option "black " the answer is “1/2” Thanks in advance!

hey there,how would i go about creating a formula column where if a timestamp from a column (let's call that column "timestamp") is older than 24 hrs, then the resulting value would be "follow up", otherwise the resulting value would be "do not follow up" Thanks!!

We run a charity vege farm. I'm building a crop planning tool. For planning, a couple of pieces of key information about a crop are:-Number of weeks from Sowing in the nursery to Transplant (Weeks to transplant - WtT)-Number of weeks from Transplant to Harvest (Weeks to Harvest - WtH)I use this info to predict how long a crop will be in a part of the farm for, and planning to ensure we have the right amount to harvest at different times of the year.In summer, a crop like lettuce may have a WtT of 3 weeks and WtH of 6 weeks. In the middle of winter, that may be more like WtT of 5 and WtH of 10.There is a whole spectrum in between those dates!How would one write a formula for WtT and WtH to be adjusted according to the date of the year? There are input fields for "sowing" and "transplant" which would act as the date to adjust WtT and WtH for.

My table has four fields: Total Price, Total Paid, Amount Owed, and Payment Status. When the value of Total Price and Total Paid are the same, the value in the Amount Owed field, predictably, shows "$0" – however when I use the value of {Amount Owned} in a custom formula for the Payment Status field, it shows up as "7.275957614183426e-12" i.e. a near-zero value, but not zero. This is causing my formula to break, although I can work around it by checking if the value is "< 0.01" instead of "== 0". This appears to be a bug in AirTable, Total price is a manually entered currency value.Total Paid is a rollup field, summed from a different table using SUM(values)Amount Owned is a simple formula subtracting Total Paid from Total PriceAnd Payment Status is a formula that looks like this:IF({Total Price} > 0,  IF({Amount Owed} > 0,    IF({Total Paid} > 0,     "Partially Paid",     "Unpaid"),   "Fully Paid" 

Hello,I am very new to Airtable, and would love some advice or guidance bringing a few ideas to life. I am using Airtable for an applicant/candidate tracking system, interview scheduling, and job tracking for a recruitment firm.The current set-up is that we have a master database table that houses any and all candidates we've ever spoken to for any role, past or present, like a CRM. In the master database, I have a "Considered for" column, which shows which role(s) this individual has or is being considered for. Then we have a table for each active role.1. I want to create a formula/linked workflow so that if an applicant's "considered for" column is the same as the name of the open role of another table, they are automatically added to that table. Example: We are opening an Account Executive role and create a new table for it. If an applicant's "Considered for" column contains "sales" OR "account executive," or their skillset keywords include "sales," then they are automatically impor

Hi all -I have a table that acts as a timesheet for the same users to submit hours each week.I use the 'Hours Logged' from Fig. 1 (which is an entry for that week) and add it as a lookup value to my table with my people names (Fig. 2). There's multiple entries in the Hours Logged in Fig. 2 as it should, but when I go to my main table, it doesn't show the 'Hours Logged Sum' for that week. Instead, it shows it based on all entries. Is there a way so that I can get the 'Hours Logged Sum' to show what the sum would be for that week like a running sum for each individual? As an example, how would I show that the hours logged sum would be: Hours logged = 3.0; Hours Logged Sum = 3.0; Max Hours = 40; Hours available = 37Hours logged = 1.0; Hours Logged Sum = 4.0; Max Hours = 40; Hours available = 36Hours logged = 5.0; Hours Logged Sum = 9.0; Max Hours = 40; Hours available = 31Fig. 1 Fig. 2^ Shows the running sum here, but it adds them altogether for every entry that matches the

Hello!Currently I have a button column linked to a column with a webhook. When I press this button, the webhook is triggered and the row informations are exported to Make.I want to get in a column, the number of times the button has been triggered (+1 each time).Do you have an idea ?Cheers

I have a Date field (Date Asset Added) whose value is entered manually formatted like this:12/8/2023Another field (Date RM) contains this formula: DATEADD({Date Asset Added}, 0, 'day')  which outputs this: 12/8/2023.  12:00am I want to execute an automtion when these two values match, which they don't. So I tried using thiis: DATETIME_FORMAT(DATEADD({Date Asset Added}, 0, ‘days’), ‘M/D/YYYY’)) which outputs this: 2023-12-08T00:00:00+00:00  I think that DATETIME_FORMAT turns the date value into text. In any case, what formula can I use to get the value of the field Date RM to match the value in Date Asset Added? 

Hello,Thanks in advance for any assistance with this.We have a ticket system whereby people submit by form an action and it's completed with a time stamp; with a goal of completing every request within 8 hours.Currently we determine if the time completed was within 8 hours of it being submitted and that generates a "1" in a formula column.  That column provides a percentage of the amount of 1's filled and gives us a service level.Recently I was asked to refine this solution to only consider submissions during business hours (Mon-Fri between 9am-6pm).This way, anything submitted outside of business hours doesn't count against the 8 hour service level until it's within spec.If something is submitted at 6:01pm, the timer freezes until 9am the next day given it's not a weekend.Alternately as long as something is submitted within business hours the timer does not stop even if it exceeds business hours or goes into a weekend.I'm hoping to get some ideas on how this can work within airta

I have searched Airtable Help and Google and haven’t located a solution for the following issue: I have created simple formulas in Currency and Number fields. The results are always rounded up. I’ve tried different options (Integer, decimal places) but they made no difference. How do I get Airtable to show the correct result without rounding up? Thank you!

Hi, I am trying to count the number of months from the Start Date till today.  The actual day of the 'Start Date' doesn't matter.  For example, if 'Start Date' is 9/20/23 and today is 12/11/23, I want to count the number of months that have passed: Sept, Oct, Nov, Dec = 4.  I have tried this formula: DATETIME_DIFF(TODAY(),{Start Date},'months') but, I know that doesn't work. I have been searching the community and haven't found the right solution.  Any help would be appreciated.

I am trying to format decimals in a formula to no avail: To the left is the number the 3rd column is what happens when this number is used in a formula (the end is cut off for numbers ending in 0) (e.g. 12.50 > £12.5 middle column is where I have formatted the number with this formula: so this was a solution found on another thread: IF({Refill / lowest price}, “£” & {Refill / lowest price} & IF(FIND(".", {Refill / lowest price} & “”) = LEN({Refill / lowest price} & “”) - 1, “0”)) However, if you now look at the middle column it works for the £12.50, but for any round numbers under 10 it misses the decimal place. (i.e. 5.00 > £50. Any help with tweaking this formula would be greatly appreciated! THANKS! :grinning_face_with_big_eyes:

Hi,I have a database with special dates of each month (Christmas, Halloween, etc). I'm using the DATEADD formula to add 1 year to a date field.For example, for Christmas I have the date 24/12/2023 and I expect the DATEADD formula to result in 24/12/2024 (field "Próxima Fecha" of my attached screenshot).However, I'm getting 23/12/2024 as a result. This happens with all the dates (gives 1 day difference).Is it a bug or I'm missing something?Thanks!

I'm using a no-code tool and I want to have a multi-like with Airtable.On the no-code side, we've set up GET, POST and PATCH to make the like work, but the problem is that it's not attached to a single user. In other words, let's say user A posts a like that goes to the favorites page, user B and C log in and see A's likes and vice versa. After speaking with the no-code platform, they tell us that the solution must be found by Airtable because it's the backend -> I'll share with you what XANO is doing. The idea is the same principle, but with Airtable if possible: https://www.xano.com/snippet/qCmG9KcL/J I'm using a no-code tool and I want to have a multi-like with Airtable.On the no-code side, we've set up GET, POST and PATCH to make the like work, but the problem is that it's not attached to a single user. In other words, let's say user A posts a like that goes to the favorites page, user B and C log in and see A's likes and vice versa. After speaking with the No-code pla

I am working on a budget base, where each month has it's own amount connected to a milestone. I'm wondering how I can link a multiple choice field which refers to specific budget lines, to specific fields in the same row, which indicate monthly amounts. So if the multiple field refers to e.g. 'A' and the monthly amount for December field is e.g. '200', how do I make it e.g. '200A', while at the same time in the same row, I want November field amount e.g. '100' to be referring only to 'B' in the same multiple dropdown field, making it therefore e.g. '100B'. Just keep in mind that the milestone amounts are always different, while budget lines have fixed value. Thank you!

Hey all 👋The subject may be confusing.Basically, I have a column {Day}, {Day} is a single select field.The options in {Day} includes the following strings:mondaytuesdaywednesdaythursdayfridaysaturdaysundayI want a formula field that returns the date, based on {Day}.For example, this post is written on 13 Dec 2023, Wednesday.If {Day} is "monday" then the cell should return 11 Dec 2023. (last monday)If {Day} is "wednesday" then the cell should return 13 Dec 2023 (today)If {Day} is "thursday" then cell should return 7 Dec 2023 (last thursday)how should I write this formula?

Hello! I have a pretty basic formula that isn't working for some reason. Basically, I'm trying to see if a substring I'm extracting with a REGEX formula, and then trimming, is contained inside another string. Here is my formula: FIND({Formula Field of String to Search For}, "" & {Lookup Field of String to Search Inside}) The second parameter seems fine: I know that the lookup field needs the  ["" &] to force it to render as a string instead of an array, and that's working. The issue I'm having is with the first parameter, the string to search for.This formula field that the first parameter is referencing a REGEX_EXTRACT() on a long text field, that I need to edit (either using LEFT() or TRIM()). My goal is to then search for this extracted string in the string generated from the lookup field, using FIND(). However, no matter what I do, the first parameter it seems like it isn't accepted as a string within FIND(). I've tried UPPER(), CONCATENATE(), and the ""

I need to be able to write a formula that checks if the last modified date is after a specific date and time. Basically, I imported a large batch of data and I want to track whenever a change is made after the specific date and time of import. I'm having trouble referencing the specific date and time of import within my IF(IS_AFTER) formula. Here is what I have so far - but I know AT isn't reading the date and time I referenced. IF(IS_AFTER({Updated Post Initial Import}), '2023-07-14 120000', "Updated Post Initial Import")

Hi, I have been trying to create a status field that uses a formula to indicate when a project is completed IF({% Complete}=100%,"complete"). The problem is whenever I type a number into the formula, it says "invalid formula". What am i doing wrong? I even have copied and pasted formulas from the airtable help page but the number portion is always red, as shown below. The red indicates (to me) that this is where the code is broken, but maybe im wrong? How can i fix it?   

Hi all!I want to express my gratitude to @Sho  for being extremely helpful in sharing a formula that significantly improved our reporting process. Now, I am seeking assistance for a new development phase.Currently, I am looking into two specific requirements:Formatting Unchecked Boxes: Is there a method to ensure that unchecked boxes consistently appear at the top of the Long Text, rich text formatted box?Standardizing Date Format: Is there a way to format the text following the checkboxes to consistently include the date in the format provided by Sho (YYYY/MM/DD)? I aim to establish a standardized approach to avoid inconsistencies among team members.To provide some context, I've attached a screenshot depicting the current layout. While I personally remember to place unchecked boxes at the top and include the date at the end of the sentence, not everyone follows this practice. Therefore, I am seeking a solution to automatically achieve the desired format, as illustrated in th

Hi all! I have a couple of survey forms in one table - one is for user group X, the other is for a different user group Y. Questions are similar, so I want responses in the same table but want to be able to filter view the records by each user group (eg by form that was used to generate the record). I thought that generating record IDs would indicate the source of which form (and therefore which user group) created the record, but it seems to be random. Is another way I can extract and filter which form generated which record? Thanks!!