Formulas

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 col

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 ide

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

Hey,Something super basic to do in any excel, python script etc. but how to get in in Airtable?I want to create some kind of ID for each customer. First customer has number 900100, and each new one (this is a child table) should get the number +100, so 900100, 900200 etc.Is there any way to do so? (ps autonumber is not an option as you can edit it, if something goes wrong and you add a row by accident it ruins the sequence)

How do I concatenate these two fields so they show values with not so many digits. I would like to show 2 decimal digits. 

I'm new, so apologies for any blatant errors. I'm trying to create a SKU in the table primary field using the SWITCH function based on other fields in the table. My initial try included a lengthy list of colors (all unique) and the 5-letter results (not unique) for output, and the formula failed to even load. Removing the color section, I was able to generate the appropriate results based on the other columns. However, after struggling with the color field and failing, I reset the formula to the original (no color), and now get #ERROR!. I cleaned up and sorted the color entries to perfectly match the fields, and now get #ERROR!, which is progress, I guess, as at least the formula loaded.Next, I added a lookup field for the color code I want in this table and changed the color SWITCH section to reference the lookup field, but I still get #ERROR!. Here's my new formula:SWITCH({Product Type},"Apparel", "APP","Accessories", "ACC","Blankets

Looking for some help regarding a formula. Here's a little context of what I am trying to do. I am working on importing my teams marketing budget plans into Airtable, and so far everything is working well. As a final step, and in order to avoid have two column with repeated numbers for Actual Variance to Budge and Planned Variance to Budget, I wanted to create a formula combining the two numbers and then group by that column (and hide it), so that it is always present but it doesn't need to be repeated. The look that I am going for is ($ x,xxx,xxx.xx | $ x,xxx,xxx.xx)I ran into the same issue that I found a lot of people here have faced. Once I use the currency values in a text formula, I automatically lose the "$" symbol and the number separators. The symbol (even with my limited knowledge) is easy to put back, but the separators were not. I found this solution in the forums. I can't take any credit for it because I simply do not understand it, but it

I have a rollup cell that is pulling a range of data from another sheet.  Is there a way to:1) Format these so it's easier to read : MM/DD/YY-MM/DD/YY 2) Format these so I can create a calendar view that would show these dates in a row on the calendar?Thanks!!

I am a beginner and wanted to make a few custom changes through formula’s and other customisation’s. I need help with the two doubts below. Also take reference from the two photos. 1. How do I setup the “Hours” formula field such that even if its 10 mins more than a perfect hour it will round it off to 2 hrs and so on for any more amount of time.2. In the first photo, is it possible for the “End Time” to solely be a time field Instead of Date and Time.Would be really helpful I could get some solid advice on this!

Per day, the employee works 8 hours and can be distributed in one or more projects (tasks) in this period. If he worked on only one project it would be 8 hours allocated to the project (easy), however if he worked on two projects it would be 4 hours each and so on. I need to generate a report of each person’s allocated hours by project. The challenge is that the tasks from different projects overlap for a period of days and I don’t know how to evenly subdivide those 8 hours by the amount of overlapping tasks.

Apologies in advance if this was answered elsewhere, I did try to find it.I have a lookup cell that is pulling information from another sheet.  In that other sheet, the cells all share an attribute called “Daily Flow.” I’m wondering if there’s a way for me to format this cell so that if there are duplicates, (like you see “An insect’s world” is shown multiple times), that it only lists it once?Thanks for the help!

I have been using this formula, and it works: IF({Company (from Distribution Locations) (from Invoice 2)}="Key Fresh 8890",{Cost *1 (from Products 2)}, IF({Company (from Distribution Locations) (from Invoice 2)}="Marathon 16050", {Cost *4 (from Products 3)}, IF({Company (from Distribution Locations) (from Invoice 2)}="Chevron 10255", {Cost *5 (from Products 4)},IF({Company (from Distribution Locations) (from Invoice 2)}="Chevron 3251", {Cost *2 (from Products 5)}, IF({Company (from Distribution Locations) (from Invoice 2)}="Marathon 5", {Cost *2 (from Products 5)},{Cost (from Products 2) 2}))))) Now I want to add another nested IF; however, with "and," it is not working for me. I want to include the following: if(AND({Company (from Distribution Locations) (from Invoice 2)}="Chevron 3100",{Date (from Invoice)}<"03/19/2024"), {Cost *4 (from Products 3)}, {Cost (from Products 2) 2}) When I add this last

I have a formula in the primary field of my table that combines a Date/time and the name of the brand in the appointment.  Formula I am using: DATETIME_FORMAT({Appointment Time},'MM/DD, H:MM A')&" - "&Brand results are randomly adding 2, 4 and 6 minutes to the time format, ex:04/09, 9:04 AM - Gregory(appointment time is 9:00 am in the calendar column) I'm lost, what am I not specifying that would prevent this? Thanks!

Im trying to make a task management system, where task 1 would have multiple subtasks and subtasks would have their own subtasks etc. The issue i'm having is im trying to make a function where if a task has no "task parent", then the "Task Tier" would be 0. If the "Task Parent" has a "Task Parent" of its own, to start counting how many steps up the tree it had to go to find no value, and count how many steps it took. Any other way that would not force me to have additional tables, or limit the number of Task Tiers, would still be fine Thanks in advance

Can anyone recommend an Airtable Consultant, preferably in the UK, who can help me with some fairly simple formulas which for some reason, I cant work out why, are not working and/or producing missing data in some rows. I feel very scrooge-like and miserly saying this, but its the reality: I work for a charity so they cannot be too expensive. But I would be very grateful if somebody could help me. I have designed a multi-layered survey that evaluates the diffferent well-being dimensions of our clients and if I cant get it working soon I fear my project will be binned and I will be devastated as I've already done a lot of research and learnt airtable especially from scratch just do this- plus I love Airtable. So if somebody could help me that would be brilliant. 

I have a table "Products" and a table "Artworks". The Product table has a lookup field to the Artworks table. In this lookup field, I assigned multiple Artworks to each Product (e.g. "Product 1" has assigned "Artwork A", "Artwork B", "Artwork C", and "Artwork D").Product 1 can only go into production once all Artworks assigned to Product 1 have approval status "Approved".In the Products table, I added a rollup field that shows the records from the "Approval Status" field from the "Artworks" table. By default, the values appear like this in the rollup field: "Not submitted, not submitted, Approval pending, Approved"Instead of listing all the records in the rollup field, I want the rollup field to show:IF all records = "Approved": output "ALL ARTWORKS APPROVED"ELSE: output "APPROVAL PENDING"or alternatively:IF any record is not = "Approved": output "APPROVAL PENDING"ELSE: output "ALL ARTWORKS APPROVED"