Formulas

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 form

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

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

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

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

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!!

I've used hours to search for a solution via Airtable support pages, this community, Youtube (interesting video) and even ChatGPT, but I can't seem to find an out-of-the-box solution. Hopefully someone like you can help 🙂In an Airtable dashboard interface my use case is just a 5 seconds configuration, and I can select it in a view's summary bar. But unfortunately not in the data tables as part of a formula so it seems.The situation:I'm tracking expenses by importing transactions in a table {expenses} as separate recordsEach expense record has a {Date} fieldI'm categorizing each expense by selecting one category which is a linked field {Category} from the table {budget} with 73 category records.What I'd like to do is create a formula in the {budget} table which uses - among other data - the latest date of all records in the {expens

I want to remove the root domain from all of my URLs after a specific character. I want to remove the root of our URL (https://cotoncolorscompanywholesale.com) AND I want to remove /pages/ so that I am only left with the actual page name. Take these two URL's as an example: https://cotoncolorscompanywholesale.com/pages/faq     https://cotoncolorscompanywholesale.com/pages/for-poppy-campaign   I want them to be trimmed to read as: faq for-poppy-campaign Can you help me with this?

Hello!I am trying to set up a formula to when a checkbox in the field "Invoiced?" is checked, the "Status" field is then changed to "Invoiced". I am setting this up so that Zapier can (hopefully) check a box if a record is still there and override anything that is no longer in the Excel file. I am trying to create a formula that would do that and can't seem to figure it out. Any help is greatly appreciated! Thanks so much!

Hello everyone!I have two tables:Table 1, where records correspond to products,Table 2, where records correspond to sales of products.I'm trying to create a single roll-up field in Table 1, so that for each product record, it gives me the sum of all revenue generated from all sales records in Table 2 which correspond to the product in question.I seem to understand that the conditional rollup formula will only allow me to sum records in Table 2 for which a particular record (e.g., "Product Type") equals a fixed value (e.g., a single-select). I am trying to set up a rollup formula which will execute different sums according to the different values that could show up in a particular record (e.g., different Product Types for each individual sales record).For example:Table 1 has 2 product records: Books and CDs.Table 2 has 10 sale records: 4 for Books and 6 for CDs.

Hi,I have two tables I work with:1. table of employees,2. Table of events3. table of enrollment to each event.I want people to enroll to events each time an event is created.I created an enrollment form in the enrollment table to insert the data there. I created a formula in the employee table that an automation sends a mail with a link to the form for each of them.However, I need the formula to prefill the event name and type to this form, and so there are two prefills to be made on the same formula from two different tables:1 employee table - the name of the employee and, more importantly, their email2. event table: Event name and type.Reminding the location of the formula is in the employee tableAny Idea how I can insert the event name and type into the formula?Nitai

Hi folks,Trying to create a column with one email address in, it's currently showing 3, any idea of a formula to remove two of the addresses so I can send an automation?

Hi everyone, I need to have a “Filter” column that is pulling date values from 3-4 different columns and selecting the one that is the most recent. So if we have 3/10/2021, 3/10/2022 and 3/10/2023, the date that populates the “Filter” column is 3/10/2023. It’s not always consistent which column will have the most updated date. Is that possible to write a formula for? Update: MAX () doesn’t seem to work for anything other than Number fields. Desired outcome is to look at multiple Date column values, pull latest date value or if all the values are the same, output the same value.

I've got a handful of companies that had meetings taking place on various dates.  I want to filter out those companies who did not meet on both 07/17/2023 and 12/05/2023.  For example, if Company A met only on 07/17/2023, it would not be shown.  If Company B met on 07/17/2023, 09/26/2023, and 12/05/2023, only the July and December meeting dates would be shown.  If Company C met on 07/17/2023 and 12/05/2023 only, that record would be shown as-is.  I've attached a file as an example.  Thank you!

Hi everyone! I'm trying to tackle an issue that I'm not sure I can solve with Airtable, but hoping to alongside Zapier integrations. I want to randomize monthly meetings across our staff, volunteers, and board. Not everyone is willing to use Slack, where I could just install an app integration, so I'm hoping to automate monthly emails through Airtable. I have a list of about 30 people in our CRM, so what I need to do is create a formula/automation that randomly pairs those people together on a monthly basis. Does anyone know if and how I could do this in Airtable? I would love for this to just run in the background, rather than me having to manually do this every month. Thank you!

Hi, I am trying to populate in a cell, "Urgent" when a project is coming up on its {Due Date} within 7 days of today AND the cell that has my completed date, {Date Filed} is Empty. If {Date Filed} is Not Empty, i'd like it to populate "Completed". If {Due Date} is over 7 days away and {Date Filed} is Empty, i'd like it to populate "Pending".Any assistance is appreciated. Thanks!