Formulas

Hi, Rookie question; I have 3 columns “Full Name” (which has the formula to join the next two columns) “Last name” and “First Name”, putting a comma in between. The formula I’m using is: CONCATENATE({Last name}&", "&{First name}) The issue is when Last name and First name are blank (empty records), the formula runs indefinitely in the Full Name column creating rows and only putting in the comma (see pic). How do I stop this?

Hi Airtable Community, When applying the TRIM function to fields that are lookup fields from another table I receive an error. However when testing other formula’s E.g. Concatenate that does work this particular situation using Linked/lookup fields from another table. I would assume that the all functions gather data in the back-end in the same way. Can one confirm this is actually not possible to use TRIM in combination with Lookup Field type? Thank you

Hi! I am unsure if it is possible to create a formula to make this work for my school, please help!I have a base created to track all applicants. We offer 5 trade classes each held 3 times a year that have a minimum age requirement; age 16yrs and 8mths (Culinary), age 17yrs (Welding and Phlebotomy), age 17yrs and 6mths (Heavy Equipment and Electrical). Currently I have a formula for DOB and age as of the current date. I would like a way to populate which upcoming class the applicant will first be eligible for. e.g. Applied for Culinary Arts, will be age eligible starting with our fall class in October of 2024. Right now I am plugging each DOB into an age calculator based on our start dates and selecting a drop down myself but would love if I could make this work with a formula in Airtable! Thanks!

I have a extremely complex Airtable formula that I need to figure out for a financial company.It's a 3 step formula, calculating Grant $ amount generated per project within a 12 month time frame which begins at client onboarding, so each client's time frame is different.

We have a database of transactions that is monthly via csv import. This contains information like transaction id, amount, duration.Most transactions are simple one-time occurrences; however, some transactions happen over the span of months.For these long duration transactions, we apply a partial payment at day 30, 60, 90, etc. until the final payment which is a remainder of the total transaction. The issue we run into is that the transaction length and amount can adjust part way through.For example, transaction A has a value of 1,000 and duration of 40 days. Day 30 has passed and day 40 is in the future so our system flags this transaction for partial payment.Calculating the day 30 partial payment is simple:Daily rate * 30(1,000 / 40) * 30 = 750The issue happens when we import the following month's transaction data. transaction A imports again, but this time it has been adjusted to have a duration of 43 days and value of 1,100.So a different calculation is needed to reconcile and

Hi there, i'm having issues with letters' case in my filterByFormula text and API call.I'm trying to do a searching box where user can give an input of what they are looking for, and Airtable fetches from a big database. Right now if i'm looking for "Hilfinger" it actually works alright and returns a few records, but if I type "hilfinger" it won't find anything.Here is my API call, i hope you can help:https://api.airtable.com/v0/appk7bV1OTtj*****/Offerte?filterByFormula=SEARCH(%22TEXT+HERE%22%2C+%7BNome+Oggetto%7D)&sort%5B0%5D%5Bfield%5D=Identificativo&sort%5B0%5D%5Bdirection%5D=descWhat would be ideal to happen is that Airtable gets the records even if cases are lower of uppercase.Thank you very much, any help would be much appreciated

Hey Wonderful Community! I am currently having an issue with a formula that I am trying to show as a progress bar. Here are the specifics: I am calculating the percetage of time passed between a start date and an end date. The formula seems to be correct: ROUND((DATETIME_DIFF(NOW(), {Start Date}, 'days') / DATETIME_DIFF({End Date}, {Start Date}, 'days')) * 100, 2) However, when I go into the "Formatting" section of the Formula Field, it gives me a very large percentage which seems to be incorrect (in this particular case the preview shows 345600% but in the formula section, the preview shows 35.56%. I have attached the two images of the settings below:  Please can someone help me? xx

I'm trying to write a formula that will count days between two dates (assignment_start_date and assignment_end_date) that overlap the current week, not counting weekends.First example:current date is 4/5/2024assignment_start_date =  3/25/2024assignment_end_date = 4/2/2024The result of this would be 2. As there are two days (4/1 and 4/2) that occur during the current week (3/31 - 4/6) and are in the range of 3/25 - 4/2Second example:current date is 4/5/2024assignment_start_date =  3/25/2024assignment_end_date = 4/15/2024The result of this would be 5. As there are 5 days (4/1, 4/2, 4/3, 4/4, 4/5) that occur during the current week and are in the range of 3/25 - 4/15I've tried several versions of this with ChatGPT, however, I have yet to have any luck. The closet I've gotten is the following. However, sometimes the days are correct, but sometimes they are anywhere from 1 to 3 days off.  IF( AND( assignment_end_date >= DATETIME_FORMAT(DATEADD(TODAY()

I have a switch formula setup to calculate Experience Brief Due Date and Opportunity Case Due date based on the Estimate size of the project, when an item was added during Quarterly Planning.Is there a way to add onto this formula to return the "Created" date if "When was this added to the Plan?" does not equal "Quarterly Planning"?Current Formula:IF({When was this added to the Plan?}="Quarterly Planning", SWITCH( {Estimated size},"Small  (2-4 weeks)", DATEADD({Target in market date}, "-35", "days"),"Medium (4-6 weeks)", DATEADD({Target in market date}, "-49", "days"), "Large (6-8 weeks)", DATEADD({Target in market date}, "-63", "days")))

I have a switch formula setup to calculate Experience Brief Due Date and Opportunity Case Due date based on the Estimate size of the project, however I only want these values to pull in for items that are added during Quarterly Planning (not Intake-Ad Hoc Requests)-- Is there a way to set up the Switch Formula to be conditional on the value in the far right column (when the project was added)?Here is my current switch formula:Experience Brief Due Date:SWITCH( {Estimated size},"Small  (2-4 weeks)", DATEADD({Target in market date}, "-35", "days"),"Medium (4-6 weeks)", DATEADD({Target in market date}, "-49", "days"), "Large (6-8 weeks)", DATEADD({Target in market date}, "-63", "days"))

HII face an auto-categorization issue with no solution so farI update a table with hundreds of credit card transactions, with lot of them recurring with 95% of the text string similar from a month to another month.Is there any chance there is a solution that would save me from categorizing 10 times the line 'Airtable' as 'Online Services' , or even better, that categorizing once 'Airtable November 2022' would allow to automatically categorize in the same way 'Airtable December 2022', 'Airtable January 2023', 'Airtable February 2023', etc...?Many thanks for your help

This might not be possible (especially since our nonprofit is on a free plan) but we have a number of projects that require review by our general counsel before they can finish. We have a tab with the start and end dates of each project. Our General Counsel just sent us several ranges of dates when she'll be especially busy and need more time than usual to provide her review.The base started with just the "Projects" tab, with each project's start and end date. I've created a new tab for "Compliance Challenge Dates" with a name, a start date, and an end date. I would like to create a new field in the Projects tab that:Gets the End date of the project.Subtracts three days.Uses the range of dates between three days before the end of the project and the end of the project (let's call it the "last three days of the project")Checks all "Compliance Challenge Dates" tab entries and sees if there's any overlap between the date range of any of the entries and the last three days of the project.I

I need next result - Text & Date. Date should be in format MM/DD/YYYY. But now as a result I have time also. It is not expected result. Formatting tab is not available for this case. Below I attached the formula I use. I would be grateful if somebody helps me to remove time from my formula.   

I have a table linked to itselfA Lookup field type still works fineBut a Count field type always returns the value 1 when in some cases it should be more than 1

I have a field where people can enter a birthdate. I want that to show up on a calendar view in Airtable. Unless the year is the current year, it's not showing up on the calendar. I tried using a formula field but then I can't get the formula field to show up on the calendar. How do I get it so people can enter the actual birthday say 2-6-68 but have it show up on a Feb 2024 calendar view in airtable? Also, do I need to do anything different so that in 2025 it would show up in Feb calendar view?

Hi all. I was wondering if there is a possibility to use a formula to have the quarterly date shown automatically. I want to link it with automations, to send reminders each quarter in the year.Specifically I need this function, to be reminded to collect invoices each quarter. In my case the quarters are as followed:Q1 Jan - MarQ2 Apr - JunQ3 Jul - SepQ4 Oct - DecSo the formula field should automatically show ex. March 31, 2024. With the automation that I would then add it gets triggered once this date is reached. As a result then, once March 31 was reached, the field should update to the next quarter June 30, 2024 and so on. Which should make it easier to track and remind myself/the team to collect the quarterly invoices. Hope this makes sense.

I am trying to figure out how to create a formula that would remove the text before a URL in a field.Example:Website: http://2pillarschurch.comHow do I remove the Website: portion so it just shows the URL?

Good Day, I need assistance with a formula, I would like to count the status of registered learners for a specific project. I tried the Counta formula but the numbers does not add up correctly. It shows for example 2 learners registered against the project code, but none of the learners are registered yet, therefor should show 0? COUNTA({Learner registration status},"Registered") 

Hello,I have 2 columns; 'First Name' & 'Middle Name'I have a 3rd column with this formula which extracts the first letter of the above 2 columns.SUBSTITUTE(REGEX_REPLACE(({First Name}&{Middle Name}), "[a-z]", ""), " ", "")Example: Jon Reginald Mayo = JRMHowever, I want to add a space in between each character, any ideas on how to do this?

I'm a bit stuck. &#x1f605 The end goal is that I can determine how much everyone should be paying in a house depending on the conditions of the room, the minimum price of payment, and the number of people within a room. --Information: I have 1 airtable with different tables. Table one is called: Room (https://airtable.com/appLy4v2lQcr2yxf8/shrBKTdZxtE96rQOr)Within this table, I have 7 rows with different rooms. I have another table that has different conditions where I can rank the importance/benefits of a specific room. This eventually ends up with a total number of conditions which I then have a script that makes a ranking out of that in depending on the specific rooms.Within the Table Room I also have a column that shows he number of people that stay in that room. Then I have another table called: %(https://airtable.com/appLy4v2lQcr2yxf8/shr2ZXmJPEn7Te53l)Where I have a table that only shows the percentage a specific ranking should be paying for. So if a roo

Hello all,I'll be inputting a cost figure in one column, how do I automate a calculation in another column (ideally the same table) to work out the cost price inc VAT (20%)? 

Does anyone know the formula to subtract 3 hours from date and time field but... show just the time please? This formula provides the correct answer showing date and time but... DATEADD({Departure Date & Time},-3,'hours') ...when I add this to the formula to they and show just time -- the time is completely wrong. DATETIME_FORMAT(DATEADD({Departure Date & Time},-3,'hours'),'h:mmA') My attempts have resulted in a series of #Errors. Any ideas please? Thank you.

Hey guys,I'm having trouble using a valuta like € 1.333,61 in a formula. It's being recalled from another table using a Lookup field. I've tried adding a new formula column/field and reformatting it first, before using it in a formula, but without any success so far. I keep getting the NaN error when using the value in a formula. All other fields/data in the calculation are valuta's like € 1.333,61 as well. Can you guys give me a hint? Thanks! 

I’m trying to make shortenings of company names using a regular expression, by taking taking the first letter every part of the name. Fx ‘Great Garden Centre’ → GGC I’m not sure if this is possible without a loop, but I’m also no buff at using regex. I’ve sofar I can only extract the first letter using this: REGEX_EXTRACT(Company, "\\b[a-zA-Z]") Is this possible without looping through every word?

Hi Community!I would like generate with a formula a random hex color code for each new record in a database. How can I do that?Thank you for your help!Stefan