Formulas

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! 

The cell gives an error (NaN) when the linked table does not contain any linked records. I want to create a formula that will fill-in the cell as '1' if the 'average (values)' is not possible. Thank you.

Colleagues top up holidays and travel expenses in Airtable. We would like to account for the empty dates 13 three random days in a given month. Is there any way to automate this?

Hi there,We are using AT as a project management tool and I am trying to create a field that indicates Project Status. The field would live on the Projects board and default to "Submitted", when we receive a request. We have a separate board for Tasks, and we use an extension to generate the tasks for each individual project. So once the tasks have been generated and assigned the Status would change to "Assigned". Once the team member views their assigned task the Status would change to "In Progress". We are using a formula to calculate the % complete so once all tasks have been completed the Status would change to "Complete". Because there are 2 boards and already a formula field we are pulling from, this seems like a complicated (but not impossible?) equation. I just dont know Airtable well enough to be able to figure it out! Does anyone know how to create what we are looking for? 

I am having trouble with my vacation day calculation when an employee is only taking one day off. I have a formula to count the days requested and they always show up as negative which is what I want to subtract from their approved days, but when they request 1 day is comes up as a +1 and throws the calculation off. Could someone help me with this? I know it has to be a simple fix. Thanks!  This is the formula I am using: WORKDAY_DIFF({End date (vacation)}, {Start date (vacation)})