Formulas

Thanks for taking a look in this thread. I am looking to create a zapier step that checks for a row in a table which contains 1) a certain ID (exists as string), and 2) to see if a certain string exists as part of a rollup. How would I write the formula for this? This is what I have so far… it’s good for finding rows that have only one item in the rollup, but if I need to find a certain item within a rollup, it says none exist. AND({ID}=‘abcdefg123’, {Rollup}=‘xyz999999’) Thanks again! Happy holidays!

Hello Community, I would need help with the case below, please: So, I have 2 sets of 2 columns, The first one (Column A and Column B) contain names that refer to a code. In the second set I have the same codes that should refer to the same name of the first set, but they could be misspelled. The codes are exactly the same in column B and D, but column C and D don’t contain all the codes and the names contained in Column A and B. What I need to do is to compare Column A and C to find misspelled names of the rows with matching codes (column D and B). Is there a way to do it in Airtable please? Thanks!

Hi I’m looking for a formula to convert a field from First Name Last Name to Last, First. “Steve Smith” to Smith, Steve". I see a lot of people have ways to go the other way… thanks!

Hello, I use this formula to define the week number according to the month and it resets again starting the new month. LPSD is my Licence Period start date VALUE(DATETIME_FORMAT(LPSD,‘w’))- VALUE(DATETIME_FORMAT(DATETIME_PARSE(‘01’&DATETIME_FORMAT(LPSD,‘MM’)&YEAR(LPSD),‘DDMMYYYY’),‘w’))+1 it’s giving me a wrong week no: week -47. when it’s week 5 is there a slight modify can solve this? thanks

Hi there :slightly_smiling_face: I have a multiple select column (column A, not the primary key column) that includes values like: Test A Test B Test, C I use look up in another column to refer to column A. The output is: Test A Test B “Test, C” How can I display comma-containing records properly (without the quotation marks)? I found some references in this forum using roll-up instead of look-up, but that was not helpful for my case. Should I somehow parse it as a string first?

TLDR: Concatenated text shown in the formula field depicts consecutive spaces as a single space. The real, complete, result of the formula only shows up in the expanded cell view (or, as I learned, when you copy and paste out of the ‘abridged’ formula field) Description of Issue/Discovery This was going to be a question because I have been struggling to figure out how to get a CONCATENATE formula to allow me to include multiple consecutive spaces (" ") in the result. For example: {Label-Lot}&" | “&IF({Delivered Amount}=BLANK(),” “&” “&” “&{Unit},{Delivered Amount}&” "&{Unit}) {Label-Lot}&" | “&IF({Delivered Amount}=BLANK(),” “&{Unit},{Delivered Amount}&” "&{Unit}) When the {Delivered Amount} field has a value, I get the result that I want, eg: Lot ALOF19 | 16 oz But, when the {Delivered Amount} field is blank, both of these formulas (and many other attempted workarounds) give a result that shows only 1 space instead of the 3 that I n

CAN SOMEONE HELP ME FIX THIS FORMULAE: IF {GST Rate}= (g) THEN SUM({Total inc GST}/11, IF {GST Rate}IS BLANK() THEN SUM {{Total inc GST}/0

i thought it would be something like DATETIME_FORMAT(TODAY(-30 DAYS), ‘MMMM’) => October or MONTH(TODAY(-30 DAYS) Its the subtracting days part i dont understand simple i know but im at a loss to make the stupid red letters go away OR how can i find out what date was 30 days ago and format that? i also need this for 60 days and 90 days to populate on a notice thats printed from page designer and sent certified mail. but i am competent enough to parse those myself once someone lets me cheat off their homework for the first one.

Anyone have any clue why this concatenate function isn’t working on a stupid simple to do list I’m trying to make? I’m literally following a tutorial and have restyled it 20x lol I’m going nuts

Hi everyone, I need to look at a field called Month in my table, which is being populated with the names of the month based on date. So, that field will have, “January”, “February”, “March”, etc. all the way to “December”. I’m trying to create a formula that automates the business quarter each month belongs to, so if the Month is January, February, or March, it prints “Q1” in the Quarter field. I’ve gotten this far: IF(OR(Month = “January”, Month = “February”, Month=“March”), “Q1”), so that it works for these three months, but don’t understand the syntax required to nest the next IF statement so that it will assign “April”, “May”, or “June” with “Q2”, and so on. Anyone know how to do this? Thanks very much. RR

Hi all, I have a table of portfolio investments, and am trying to calculate the IRR of each individual investment (only within a record, no need for roll-ups). Is there anyone that has done this before? I’m afraid I’m a newbie when it comes to Airtable and scripting, so any help would be much appreciated!

Some context: I’m doing a content audit, and part of that is seeing which keywords are associated with a given URL. Right now, all of that information is in a single sheet, with each keyword on it’s own row. Problem is, since there’s only one keyword per row, I end up with multiple rows with the same URL but different keywords. What I would like to do is two-fold. In Google Sheets consolidate it so I can look at a single URL and see all of the keywords associated with that. Bring that keyword information into the URL fields that are in the AirTable base I’m using to manage the entire process. I imagine there’s something to do with pivot tables here, but I have no idea how those work and need some serious hand holding.

I can’t quite seem to get this right. Fields at play: Asking Price - this is how much a seller wants for their equipment Commission Included? - this is a yes or no single select field % Commission - this we type in as each sale is different, usually 10% Commission - this is #1 * #3. Net to Seller - this is the formula that is tripping me up. So, what I want this to be is if the Commission Included field is “Yes”, then calculate Asking Price minus Commission. But if Commission Included field is “No”, then Net to Seller would just equal the Asking Price. Here’s what I wrote that doesn’t work: IF {Comm Included?}= Yes, {Asking Price} - Commission, {Asking Price} What am I missing? Help appreciated!!!

Here’s the same thing that I’m looking to do with timeanddate.com timeanddate.com Year 2021 Calendar – Canada I’ve tried a few workarounds but there doesn’t seem to be a lot of low-level date functions in Airtable that I can use to do this. Any suggestions would be greatly appreciated. Thanks!

So I’m looking to employ a formula to provide a word count for my SEO keyphrases, which range from 2 to 8 words. Below is the formula that I thought would work, but I’ve had no such luck…does anyone have any thoughts as to what’s going on here? Thank you in advance! IF(({Keyphrase}=BLANK()),0,(LEN({Keyphrase}) - LEN(SUBSTITUTE({Keyphrase},’ ', ‘ ’))+1))

Hi, I would like to divide my week number field by 2 in order to group time entry by pay period. Our pay period is two weeks starting on sundays. Here is the formula I use to get the week number: DATETIME_FORMAT(Date,‘w’) And here is the formula I use in the pay period field: {# semaine}/2 I get #ERROR! in all the cell of the pay period field.

Hey Everyone, Would love some help with something from the forum! I feel like this is something I should be able to do but am stuck! I have a table in a base that simply put a user enters a score from 1-5 (dragged in from a survey). The table can have multiple users and each user may enter that score multiple times eg once per week for a year. What I am actually trying to do is track a users sentiment toward something over time. So it basically the table looks like this: UserID : Date of Entry : Score User 1 : Monday : 2 User 2 : Monday : 4 User 1 : Tuesday : 3 User 2 : Tuesday : 3 User 1 : Wednesday : 4 User 2 : Wednesday : 1 User 1 : Thursday : 5 User 2 : Thursday : 1 Thats all fine but what I want to do is do is add a third column to the calc that shows a rolling average for each user sentiment over time. So in the above example User1 sentiment goes 2,3,4,5. If 5 is good and 1 is bad that means their sentiment is improving over time. So the calc I would like to do for each user is

I’m stumped on how to add up duration fields (segment run times from different tabs) to add up a total run time comprised of multiple segments. I’m working with 3 “segment” types here. The shortest segments, “Tab A” are linked to the medium one “Tab B” So, records from “Tab A” are added together to get a run time for some “Tab B” segments, but not all. Some “B” segments have no elements from “Tab A,” So I created a function field that adds up “Tab A” durations, along with a duration field just for segments unique to “Tab B” Our longest, “Tab C” are made up of records form both “A” and “B”. But for some reason, the total run time for “Tab C” is not adding up! What I want is a total from A-Unique TRT, B-Combined TRT and B-Unique TRT to add up to a total duration time in the “Total TRT” field. In the screenshot below, the first record should total 10:00, but it’s showing as 0:01. The formula I am using is “SUM({B-Combined TRT}+{A-Unique TRT}+{B-Unique TRT})” Is the zero in Combined TRT ca

I have a Rollup field that takes a series of decimal percentages from another table (e.g .68%, .72%, and .55%) and then calculates the average (.65%). I have the formatting of this field as Percentage and precision of 1.00. Unfortunately, when Airtable passes that result to the 3rd party app I am using (Stacker), it includes more than 2 decimals. I want to stop the number of decimals at 2. I think I probably need to convert this percentage to a string and then concatenate it with %. Anybody know of an easy formula to do this? Or a different solution?

Hello, I have created a fuel log form using Jotform and airtable base to record the fuel usage which should match the meter reading at the physical fuel tank. There are 2 types of fuel; Diesel and Gasoline. Both are require to record the following details: Diesel Tank = This field shows the remaining fuel in the tank. Physically the fuel tank can holds 500 gallons. Diesel Start = This field shows the beginning of fuel reading. The start reading should begin with fuel tank 500 gallons = 0. Diesel End = This field require the employee to enter the ending of fuel reading after she/him done pumping the fuel into a vehicle/equipment. Diesel Total Gallons = This field shows the total gallons that’s has been pumped. (I already got the solution for this field). My problem in sample use case is — the first employee record the fuel log as following: Diesel Tank = 500 (Auto calculate/ formula) Diesel Start = 0 (Auto calculate/ formula) Diesel End = 30.9 Diesel Total Gallons = 30.9 (Auto calcu

Hi! I’m using airtable to track my daughter’s food, medication etc. as part of her complex healthcare needs. The table’s primary field is datetime for each unique record, and I have created a formula to calculate only the date to allow me to group by day and analyze a day’s activity. The formula works for some but not all records. For some records, the date calculated is a day later than it should be. I’m thinking there is a timezone discrepancy (as the incorrect dates are later in the day and I am in GMT -0700) but I’m not sure how to address this with formulas. Current formula I’m using to pull only the date out of datetime: DATETIME_FORMAT({Date and Time},‘MMM-DD’) Screenshots of the mismatched records, and field customizations for the two fields I’m working with are below. Thanks community for any assistance you can provide!

I am using Stacker as a front end for my Airtable base. In Airtable, when I use the rollup and formula functions to calculate or show currency data that I format with precision of 1 show no decimals are showing. Also, i do the same with integer data that I format with no decimals. However, when the data is pulled into Stacker it shows the decimals for each. So the value of 54,528,713 in Airtable displays as $54,528,713.48 in Stacker and the value of 180 is Airtable displays as 180.32 in Stacker. Does anyone know of a way to fix this or use a formula (like the TRUNC function in Excel)? If so, I figure I could add a field next to each field that needs the decimal removed and use the this function to create the value with no decimals and then pull that into Stacker. Thanks for any insights.

Hi all, I need a formula for anonymizing names in a column. Instead of just hiding the column I would like to have the name anonymized in a different column like this: Matthias -> XXXXhias In this case the name could still be verified but is not traceable for third parties. Is this possible and what could a formula look like? Have a nice Weekend! :slightly_smiling_face: Dan

Hi all, thank you in advance for any help! I have 3x columns of interest in my Airtable which contains TransferWise statement information which is copied and pasted by me (from TransferWise to Airtable) on a regular basis. These columns are: {TransferWiseID} {ExchangeRate} {Amount} I also have a formula column which is: {ExchangeRate*Amount} Below is the formula that I am currently using with some success. I am happy that if {TransferWiseID} contains “BALANCE-” then 0.00 is populated into the formula column. This is working fine. Where I am stuck is, is if {TransferWiseID} contacts “CARD-” or “TRANSFER-” the formula rounds to 2 decimal places as it should, but only if it is not a round number. What I really want it to do however, is round to 2 decimal places consistently. E.g., right now, if a total is 10, then the formula column displays 10, however, I would like it to display 10.00. Can anyone suggest any tweaks to my formula below to make this happen? Thank you! Martin IF( SEARC

My first table is a list of orders with a date and a customer name that is linked to the customer table I need to display the date of the last order on the customer table.