Formulas

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 {expenses} table. Why? To calculate the difference of my spending per category, which is now based on today instead of the last transaction date of all

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.In Table 1, I want to create a single rollup field which (a) for Books, will sum revenue from all 4 sales records for whic

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

Hi everyone,I'm trying to create a formula field to calculate last week.I have a view that shows me the events that refer to last week, I added a formula field to calculate all the events that have the "Date" field dated to last week, but it doesn't work in any way, because it continues to insert the camps of the current week. Can someone help me?

I have a complex formula I am trying to write, and unfortunately it says there is an error in my code, but I can't seem to work out what! I am trying to write a custom field that says LIVE, FUTURE or COMPLETED depending on the number of days away an event is, and in what region. For example, I want a project to be live if its 180 days away or closer for AUS/NZ, but only want a project to be live at 120 days for EMEA. Here is the code I have written so far, which doesn't work (for now I have simplified to just be LIVE or COMPLETED, as once I have worked this out I'm sure I'll be able to add future!): IF( AND( (OR(Territory = "SEA",Territory = "AUS/NZ")), AND( DATETIME_DIFF(RHO, TODAY(), 'days') < 180, DATETIME_DIFF(OPEN, TODAY(), 'days') >= 0), ), "LIVE", IF( AND( (OR(Territory = "CAN",Territory = "NAM",Territory = "EMEA",Territory = "MENA")), AND( DATETIME_DIFF(RHO, TODAY(), 'days') < 120, DATETIME_DIFF(OPEN, TODAY(), 'days') >= 0), ), "LIVE", IF(DATETIME_D

I'm using the highly useful Prefilled Forms extension by Kuovonne and it works as intended. However, there are some fields in my source table that apparently aren't qualified to be prefilled on the form and I can't figure out the principle. (The form itself is in the Assets Updates table.)The Assets table contains the prefill form URL in a field called Update Asset. It works fine but does not include a field called Asset Type that I want prefilled on the form. This field is not included in the list of possible fields to use when filling out the wizard for Prefilled Forms.Asset Type is a linked field to a third table called Types. In this screenshot you can see that I've reduntantly added two additional fields that contain the value from Asset Type, thinking there was something about Linked records that was a problem:But I can't use any of these fields to prefill either. Why is that? At least one other linked field, Modules, works fine and is prefilled on the form.Here's a par

Hello allI am trying to copy/paste a line from an HTML table into airtable. The table has several columns which are delimited by tabs (unicode 0009).Nothing unusual so far...I created a form so I could paste in the line into that maps onto a single field. When you do this it retains the tabs in the string...BUT...I am struggling to find a way of sanitising this data and splitting out the columns.Yes, you can paste directly into the table field and split it out that way. I don't want this, I want to be able to paste the string/line directly into a form.Using "\t" in formulas doesn't work.With Windows 11 and pasting from Chrome, CTRL+SHIFT+V does not strip out any tabs (it's for formatting only)So how else can I strip out these pesky tabs?

Hello All,I have looked around the discussion boards for an answer with no luck. I have a formula as follows:REPT("|",{Line Count}) When I use this, it just gives me an #ERROR! as the entry in the field. Entries with a zero in "Line Count" are blank. I am not sure what I am doing wrong. "Line Count" is just the result of number (from another field) being divided by 10,000. Is it an issue that it is a formula field? Any help that anyone can provide would be greatly appreciated. Thank you!

Trying to get 'Full Name', 'Organization' in my primary field. My problem is that sometimes both 'Full Name' and 'Organization' are filled in, and sometimes it's only one or the other. I tried using the following formula, and it worked when one or the other is filled in, but when both are filled in, it doesn't add the comma and space. I'll attach an image so it's clear what I mean. IF({Full Name}, {Full Name} & IF({Organization}, {Organization} & IF(({Full Name}+{Organization}),({Full Name} & ", " & {Organization}))))  

Hello,I would like to not have a space at the beginning of each entriesI think I have to use Trim but My formulas is long and I do not know how to make sure each line and trimmed.Right now with this formulas each lines as a white space at the beginning (see below)IF(AND({MARQUES},{DESIGNER}),{MARQUES} & " X " & {DESIGNER} & "\n",IF({MARQUES}, " " & {MARQUES} & "\n") &IF({DESIGNER}, " " & {DESIGNER} & "\n"))&IF({FABRICATION}, " " & {FABRICATION} & "\n") &IF(AND({DATATION},{COLLECTION}),{DATATION} & " - " & {COLLECTION} & "\n",IF({DATATION}, " " & {DATATION} & "\n") &IF({COLLECTION}, " " & {COLLECTION} & "\n"))&IF(AND({PRODUIT},{MODELE}),{PRODUIT} & " - " & {MODELE} & "\n",IF({PRODUIT}, " " & {PRODUIT} & "\n") &IF({MODELE}, " " & {MODELE} & "\n"))&IF({MATIERES}, " " & {MATIERES} & "\n") &IF({COULEURS}, " " & {COULEURS} & "\n")&IF({DETAIL}, " " &a

Hi There,Seeking some help writing a formula to show the minimum of two date fields in a specified format. I have it working but showing without a forward slash.Formula used so far is =MIN(VALUE(DATETIME_FORMAT({6QIssuedDate},"DD/MM/YYYY")),VALUE(DATETIME_FORMAT({6QTriggeredDate},"DD/MM/YYYY"))) This is showing as DDMMYYYY but need the format at DD/MM/YYYY Thanks

Basically,I have a field that lists days of the week with no reference to date. I have tasks that need to be done on a particular day. Would like to another field to automatically update itself with date of the current week for that given day.  Is this possible?  Thanks

Hello All,This is my first post here & I am an Airtable newbie.I am trying to get my table to show the number of unique values for 1 column based on another column.Here is an example of my data:I want to insert a column that counts the number of unique TU ID's by the School & County column.Thank you for your help! 

I have a formula that I'm attempting to show "Sold" if tickets to an event have been sold, but if the tickets have not been sold, to display the number of days until the event. I already have another formula that will give me the number of days until the event.Generally, the formula below is working - except for one scenario, when a ticket was sold for $0.00. You may wonder why a ticket would be sold as zero, but that indicates to us that the ticket was given away - as compared to just not used.I've tried to use the presence of a value (my best working version below), and I've also tried to use the =BLANK variable, because if there is no sale at all then the cell has nothing in it, but Airtable seems to treat a value of $0.00 the same as BLANK.So then I tried a variation of this formula with >=0 but then ALL values were returned as true - even values where there's nothing in the cell.Here's what I have for a formula currently:IF({Event Sale Price}, "Sold", {Days till Event})The