Formulas

I would like to recreate a transaction sheet and P&L sheet from Google Sheets into Airtable. In Sheets, there is a "Transactions" sheet with all of the company's expenses. The relevant columns for the P&L sheet are data, amount, category 1, subcategory 1, subcategory  2, and subcategory 3. On the "P&L" sheet, which tracks the transactions by categories and month, I am using SUMIFS equations to get the weekly and monthly totals for each category, as well as the total expenses for each month. For example, this is a snippet of the table. It is continuous for the whole year. I would like to recreate this in Airtable. Right now, I have a "Transactions" table in Airtable. The first field in the date, then a field for the category (Employee, SAAS, contract, office), then a field for the first subcategory (USA salary, PH Salary, Intern,

Hi all,I am a Campaign Coordinator and I use Airtable to help with my reporting. I currently have every campaign uploaded within Airtable for the entire fiscal year. However, I am looking to filter a bar chart extension to only represent the campaigns for quarter 1.For example, I have a table that represents the campaign goal for each campaign. I created a formula: {Campaign Count}/{Campaign Count Rollup (from Goal) (from Label)} *1However, this represents the percentage of total campaigns for each goal. I am looking to filter the bar chart to represent only the campaigns for Q1. Does anyone have a solution to this?Thank you in advance!

We have a base that has start date and end date columns. Need a formula that counts how many days worked in the month on that project. Anyone done this before? 

I think this is a formula question .... Maybe extensions or something. I have an interface with two key components: attachments and comments.  Since there will be many attachments at any given time, I want to add a data field that looks up/displays only the last uploaded attachment. How can I accomplish this? 

I have two tables, one with a list of payroll dates(vertically) for a company and another that is a payroll download that includes payroll dates(vertically), gross pay and employee information. In excel I used the sumif formula to say in sheet 2 sum gross pay for all the rows that equal the payroll start and end date in sheet 1.Example: There are 10 employees so there are 10 rows with different employees names and gross pay amounts in sheet 2. In sheet 1 I have a single row with the start date 1/1/2023 and end date 1/15/2023. I want to be able to to say sum the gross pay for all the rows in sheet 2 that have a start date of 1/1/2023 and end date of 1/15/2023. I need these dates to be referencing something though so that it will sum different date ranges based on the row it is in. So if the next row down in sheet 1 is 1/16/2023 and 1/31/2023 then it will sum all the gross pay amounts in sheet 2 for that date range. I tried the rollup but it is not dynam

I am trying to do a formula where I have a row with numbers (=calculation field/column) and depending on the number a certain text value (Tier 0 / Tier 1 / Tier 2 / Tier 3) should be the result in my formula fieldTier 0 : 500,000 - UnlimitedTier 1: 100,000 - 500,000Tier 2: 40,000 - 100,000Tier 3: 500 - 40,000I tried several approaches but none of them worked, I always get an error message.IF( {Calculation} < 40000, "Tier 3", IF( {Calculation} < 100000, "Tier 2", IF( {Calculation} < 500000, "Tier 1" , IF( {Calculation} < 100000000, "Tier 0"))))ORIF({Calculation} < 40000, “Tier 3”, IF({Calculation} < 100000, “Tier 2”, IF({Calculation} < 500000, “Tier 1”, “Tier 0”)))ORIF({Calculation} < 40

Hi, i have a formula field named "Jenis Parts Regex" where its value is "15.9mm Karet stabil FR RH/LH ES1|05".I want to extract anything that matches "end_of_name" field, in this case "ES1|05", and the result shall be in "regex replace" field.But what i got there is "15.9mm Karet stabil FR RH/LH |". Why am i getting "|" ?Here is my formula in "regex replace" field:REGEX_REPLACE({Jenis Parts Regex},end_of_name,"")Please helpThanks 

Hi there!I am trying to create a formula that looks at an entry (single select) and if that entry is = A, B or C say "Blabla" and if it says anything else say "Haha".I have hence tried to nest OR() into IF() as followed:IF(entry=OR("A", "B","C"), "Blabla", "Haha")I am not shown any error but the formula result is wrong (It says "Haha" when it should say "Blabla').Any thoughts?

I am trying to build an automation or formula for this scenario:Student #1 has been enrolled in school from the dates 7/1/2018-6/30/2023. I have a field in this table (Students table) for Enrollment date (7/1/2018) and graduation date (6/30/2023). I need the field " School Years" to populate with the respective school years this student was in school for. Ex: SY 2018-2019, 2019-2020, 2020-2021, 2021-2022, 2022-2023 - this is a multiselect field. This will be unique for every student, as their enrollment and graduation dates are varying...I have build a table named School Years and have the start and end date for each school year as a record. I would love this to be automated so that every time we have a new student and their graduation date changes, it will update what school years they were here for.This seemed so easy to do in my head and I have tried every way to get this to work, but I cannot see

Hey all - I'm running a formula column with a lot of IFs in there. Only 1 out of the 6 possible responses returns a text field, the rest are date fields.The dates come out in a very ugly way, this is how it looks: 2023-07-12T23:06:37.000ZIt basically makes it impossible for me to use / property view.After some playing around with it, I've understood that the issue here is with the fact that one of the values returned is a text field.However, the way I have the formula and the intended use. Unfortunately, I can't do anything else but have a text in one of the IF's (since it's the if true, not false, that I could just leave empty).I've tried using the time parse formula like this: DATETIME_PARSE(the if formula), but the result is an error.How do I make this work?

Hi I want to extract a portion of a text from a google drive share link.The format of the drive link is like this:https://drive.google.com/file/d/1IklI1OAW8XLtTKDfwW75BNcve8z8xQ57/view I want to extract the text after "d/" and will end at before "/view". So the intended result should be "1IklI1OAW8XLtTKDfwW75BNcve8z8xQ57 "Any ideas how?Thanks a lot!

I’m new to the airtable community and wasnt sure how to reply to an original post that has been closed. But I found a solution to this #ERROR! when using ENCODE_URL_COMPONENT() on lookup? post. You can use ENCODE_URL_COMPONENT({lookupFieldThatYouAreTryingToEncode}&""). This adding (&"") solution is a lot neater than adding another already encoded field to the parent table.

Hello! I am currently trying to extract information from an inconsistent string of text and having a tough time.Ideally, I would be able to drop the MONTH and DATE from the String of text and KEEP the all the text along with just theYEAR. The trouble is, both the intro text (represented as X's) and the dates are not consistent lengths. EXAMPLES OF TEXT:  DESIRED OUTPUT: XXXXX.RTW.Men.7.5.2023XXXXX.RTW.Men.2023XXX.RTW.Women.12.10.2022XXX.RTW.Women.2022XXXX.RTW.11.6.2022XXXX.RTW.2022

Hi community, I'm French and when an order is generated on my Shopify, i receive the shipping adress in this format : "7 rue notre Dame" (On shopify and Airtable)For another application, the house number (7 in my exemple) need to be at the end and not the beggining.Do you have a formula or a solution to take the number at the beginning while whe can have 1, 2 or 3 numbers or nothing at the beggining of the shipping adress ? Thank's a lot for your help, Matthieu

I am building an implementation plan for my projects split by a 2-week sprint cycle. While I was able to get Week number from date of delivery, I am stuck trying to covert it into a Sprint number.

I want to use an airtable search formula, I already have one working, but I need to upgrade it a bit and make it a little more complicated. Current: FIND("*{{1.postcodeZone}}*", {Postcode Zone})I want to add 2 more requirements, so the first is a particular checkbox field must be set to yes/true.IE "Field 1 = true"AND then additionally, I want another requirement to be that a longer field, contains certain text, or the phrase "all"IE "Field 2 CONTAINS abc or 'all'" So I want all these 3 above parts to be required for anything to be returned

This might be the most random formula ever, but having a hard time figuring it out/ wondering if it is even an option.I have a list of records that represent tasks to do.I want to have these tasks in sets of 3 records.I dont want the tasks grouped by airtable where you see all of the various groups.I want to have the table literally only show the current set of 3 tasks and then they have a checkbox field next to them and as you check them off they are filtered out until all 3 are filtered out and the next 3 tasks come into view and it repeats like that.Totally not sure if this is even something that can be done, I was thinking of manually adding in set #s and filtering for that, but then I dont know how you filter just the set that is between the one that is checked off and the rest of them.This might be a too complicated one to create, but appreciate any help, thank you!

Hello!How I can join two columns what I need Join with IF - example:AreaCityLocationHaljalaHaljalaHaljalaHiiumaaPaladeHiiumaa | PaladeMuhuMuhuMuhuKullamaaKullamaaKullamaaSaaremaaAsteSaaremaa | AsteIf the City and Area name are same then formula shows that name only one time

Hi all. I'm trying to create a formula field that will calculate if the date should be the 1st or the 15th of the month based on another date and time field that can be any day of the year.If the date field is any date between and including the 1st and 14th of the month, the formula needs to show the 1st of the previous month.If the date field is any date between and including the 15th and 31st of the month, the formula needs to show the 15th of the previous month.For example, if the date is July 13th, the formula should show June 1st, but if it's October 17th, it should show September 15th.Any ideas on how to accomplish this would be much appreciated. Thanks.

Hoping for some help with how to setup for a formula to calculate the "Stabilized Occupancy" as shown below (% shown is wrong and is a placeholder in my table design).  On a table, I have "Leased" "KV" and "Committed".  I need to calculate the following(Leased - KV + Committed) / 1,000,000 (633470 - 45472 + 290) / 1,000,000 What would be my best solution to pull this formula in the "number" Element in my Interface?  Thanks!  

Hi all, I'm looking at creating a Button on a base which creates a draft email to send to a client. Within the body of the drafted email, I want to include a unique Calendly Link hidden beneath a "click here" button on the drafted email. I've got so far with it and the formula is correctly drafting the email, and body, but the link stops at the 'clickable' link part. Here's the part of the formula that works;"mailto:" &{Email} &"?subject="&ENCODE_URL_COMPONENT("Book a Meeting")&"&body=" & ENCODE_URL_COMPONENT("Hi "& {First Name}&",\n\n I hope you're keeping well...")&  The next part is the bit that stops working;ENCODE_URL_COMPONENT("<a href="&{Calendly URL}&">click here </a> to book a meeting\n")

I received some contact info (Excel format) from another source that I'd love to incorporate into my Airtable. It's over 1000 records. There are two columns. The first column is fine as is.The 2nd column lists a person's name and email in this format: First name Last name (email address)It's complicated by the fact that many of the fields in that column have 2 or sometimes 3 people, so it's listed like this within the field:  First name Last Name (Email), First name Last name (Email)And sometimes even 3 people, like this: First name Last Name (Email), First name Last name (Email), First name Last name (Email)Help!!! Thank you!!!!-Amy  

I am trying to figure out how to create a field that will update every time the "CLIENT STATUS" field is changed and maybe another field that says how many days that record has been in that status. Any ideas? Thanks!

Hello all, I'm working with formulas, but got stuck on something. What I'm trying to accomplish here is simple, but not sure how to formulate it. I have a linking record for an invoice for NET Terms: NET15, 30 etc. I have the formula set up, but I want that converted into a date format rather than what I have.  IF({TERM}="NET15", "15 Days from 'Due Date'", IF( {TERM}="NET30", "30 days from 'Due Date'" ) )  Simple, but this is the output:Pretty simple, however I was testing the formula, and for invoice structure - I wanted a date format approach. So If NET15, then have the "Due Date" 15 days BASED off of the "Date" field. I don't know how I'd structure that, but that's the primary goal. Instead of showcasing "15 days from 'Due Date'", I'd rather it show a date format. EX: D

Good afternoon! There are several product sections, each product has its own identification number. How to make the id is filled in automatically in a row (example P00005) and you need to save the number of characters (example, P00009. P00010)