Formulas

Hey folks,I have two tables:Table 1Table 2On table 1 I would like to get a count of the number of records that satisfy certain criteria from table 2 and return one of two values: Limit or Not Limit.The conditions I'm looking to count with are as follows:1. Records created with a specific timestamp range. (For my use case it would be records created in the current month).2. Records created that are associated to values across one of three columns. (For my use case we would be Baisc OR Pro OR Premium. So in the screenshot below we would count the number of values in the "Basic Cover Design" column that have a timestamp within the current month for instance.)3. Run this counting logic/formula against each row in table 1 so in order to determine if the user has reached their limit or is not at their limit for the current month. (I mocked up an example of the end state I'm hoping to achieve below using single select fields. But am assuming we would need to use formula fields to populate the

Hello, how can I rename all the "Web" addresses to a "Link" for a cleaner user experience? Thanks!  

Hello,I can't find the issue here that is preventing what i thought would be a very simple table calculation.I have a table called Deals with many different fields but one of them is a currency field that i want to use a rollup field in another table to calculate the sum of the values in this currency field.I'm stumped as to what i am missing here but all i get are zero values. I tried to workaround the currency field by making a formula field that just turns the currency values into normal integers but no luck.I attached a few screenshots for context, hopefully someone can see what i'm missing.Thanks,Alec

Hi everyone,I would be grateful to everyone who offers solutionsI have two linked tables: Problem 1  ✅ ResolvedI need a formula for the "Status" column that will display such statuses: Problem 2When I add a new record to the "Sales" table, I need to make sure that the product does not appear in the list if the entire quantity is sold: Problem 3When I select the quantity of products in the "Sales" table, I need to make it impossible to select more than the quantity in "Purchase" table:Thank you.

How do I create a table that shows the number of emails sent per day?

Looking for a simple formula that spits out that last Friday of the calendar year. I'm trying to avoid needing to make this a manual update so would love to find a formula that just spits that out without me having to update it at the beginning of the year.  Thank you! 

Hi there, I am trying to calculate a formula for a discount. The discount category is single select (10% or 20%). I want to calculate what the total revenue will be with the selected discount. The current formula for revenue is (price * quantity) 

Hello community I have been trying to solve an issue. I am looking for the correct formula to automatically fill the columns, (CCC Loads, variable expenses, fixed expenses) in the cost per mile calculator listed below. Those entries will be pull from 2 other table pictured below as well title (operating expenses & ccc loads). Please let me if anyone has any input on how I may be able to solve this issue. 

Im not sure how to ask this in order to look up suggestions. I have an order form that I created on a workstation. Based on the photographer, the client is given the options offered by that photographer. Once they select the options/services they would like I am hoping to have a field that auto adds the selections they made. Is there a formula or script I can create where each selection they choose can be given a designated price that can be added up for a grand total much like an invoice?I am attaching an example of one of the photography options 

Hello friends of the community, I'm facing a problem when trying to filter the date in the interface. Whenever I select the month it shows me the total, the same happens when I use the specific date filter. Do you know any formula or trick to solve this problem?

I have a formula created that is predicated on creating a count by a criteria. We have a list of records and each line item has an associated email entered. I'm trying to create a formula that will be a running count based on the emails. Essentially this is a count of shifts someone works with us. Can anyone help with this?

I am struggling to get a Formula that would return my age, where the number after the decimal point indicates the number of months in duodecimal format. For example, my current age is 34 years and 3 months, therefore I want to see 34.03 as the return.So far I've managed to only get this:the formula I am using is:(DATETIME_DIFF(TODAY(),{DoB},"Y"))&"."&((DATETIME_DIFF(TODAY(),{DoB},"M"))-((DATETIME_DIFF(TODAY(), {DoB},"Y"))*12)) It is pretty close to what I am looking for, but how do I get 2 digits after the decimal point by default?*I understand that it may be a weird request asking to combine decimal and dozenal numeral systems like this, but would really appreciate if somebody helped me solve this one 🙏 

I have a description field, and would like to generate it based on values.  I could not find any way to format numbers, like there is DATE_FORMAT for dates.  The TEXT function in Google Sheets allows you to format exactly like you want it. In my case, I want to format the number as 0.00.  else, Airtable will sometimes show as 213.3, or 214.57777777 which is very ugly. 

Hello, I'm encountering an issue with a formula I'm using to calculate video aspect ratios in my Airtable base. I would appreciate any assistance in identifying the problem or suggesting an alternative approach. The formula I'm currently using for {test3} is: {test1} / {test2}Here's the expected behavior of the formula I'm using for aspect ratio calculation:- If the value of the field {test3} is 1.78, I want the formula to return "16x9".- If the value is 1.00, I want it to return "1x1".- If the value is 0.56, I want it to return "9x16".- If none of these conditions are met, I want it to return a blank result. The formula I'm using for this is:IF( {test3} = 1.78, "16x9", IF( {test3} = 1.00, "1x1", IF( {test3} = 0.56, "9x16", "" ) ) )Please note that {test1} and {test2} are numeric fields that store video resolutions.  However, the formula i

Hi there,I am hoping there is a formula for what I need. I want to be able to have a date populate when I put in 30 days from today, or 20 days, or 45 days.Example: I need to follow up with sales lead A 30 days from today. What is that date?Thank you! 

I have a column that has strings of option lists, and I would like to extract only one option "COLOR=" ,between the comma & " "(space), from them. These option lists sometimes include different language letter so It must extract english only.Here we have four examples of the string that shows the pattern.COLOR=WHITE 화이트, DETAIL=NONESLEEVE=SHORT 쇼트, COLOR=SKY BLUESLEEVE=LONG, COLOR=GRAY, TOP=DOUBLECOLOR=LIGHT YELLOW 밝은 노랑=>>>> I would like to see >>>>COLOR=WHITECOLOR=SKY BLUECOLOR=GRAYCOLOR=LIGHT YELLOWI tried REGEX-EXTRACT({option column},"COLOR=[a-zA-Z]+.*[a-zA-Z]+)"but obviously it includes after comma as well. something with '*,+' was recommended but do not know how to combine these..Thanks for the help in advanced!

Hey everyone! Can you help me to figure it out how to properly write this formula?IF(Result>={Goal120%},"Excellent",IF(Result={Goal100%},"Completed"),IF(Result<={Goal80%},"Partial"))Both Result, And Goals are Columms in my table. Thank you all, in advance! 

Hi everyone!I'd need help creating a formula that adds a number if there is one or more duplicate of that record.Example: if i have two record 'maria', one should be 'maria-1' and the other 'maria-2' or similar.For now i've tried a workaround using autonumbe, making a formula {name} & {number} but so they're not numbered sequentially by duplicates, just by the table order.Any ideas?

  Hey guys,I just started using AirTable today and i'm going crazzy on how to set up a percentage formula.I have column A which is named goal (set up as number), column B, which is names Actual (also set up as a number field), and column C which is the variance of the two. I'm trying to create a 4th column which would show me the variance in percentage and would be great if I would be able to also show it as a progress bar. Now, I have figured out the percentage bar, but how do I get the actual percentage instead of me having to calculate it every time?Is it actually doable?I have included a screenshot so you guys can better understand me 🙂Thank you everyone!

Ok so here's the main formula I'm trying to use for my "Monday Pay Hours" fieldIF({Monday Start} = BLANK(), 0,DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes'))/60-0.5This gives me the exact hours worked, minus a half hour lunch break It's almost perfect, but what I need is to tell it that - IF the formula all the way up to here: IF({Monday Start} = BLANK(), 0,DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes'))/60equals less than 5, then don't add the last step of subtracting the 0.5.  Is this possible?

Hello,We are a marketing & design team. We have a status field on each job that indicates if the job is currently sitting "with marketing", "with design", "complete", etc. Each time we hand the job back and forth between departments, we change that status field accordingly.  The change of status is subsequently tracked in the Activity/Comments panel of the job.  Is there a formula that can be written to look back through the last year's worth of job tickets, in each Activity panel, and count the number of times the STATUS field changed?(side note: 2 months ago I created an automation to begin tracking & counting that status change, so we have the data moving forward. However, I'm seeking to gather that data from months prior)Fingers crossed.....Thanks!

I have a form that users submit each week to log hours. I have a table that captures each submission in a new row along with how many hours they have left in total (not per week). I have fields that capture the hours logged that was submitted, the total available hours, and how many hours are left, but I am unable to get it to add up all of the hours for a single user and then provide me with the remaining hours. As an example, Jane K. (1st week)Hours logged this week = 4. Total available hours = 20. Remaining hours = 16.Jane K. (2nd week)Hours logged this week = 10. Total available hours = 20. Remaining hours = 10. How do I get the 'Remaining hours' total to be 6 (4 + 10 = 14 minus 20)? I would like to base it off of the first field in my table which is a concatenation of two values.  Thanks!

I am trying to create a formula for the following informationOvertime $This field is Overtime Hours *.50 * Pay RateHowever, I just get ERROR, and I am stuckThis was the excel formula =SUM(D4*0.5)*B4D4 being Overtime Hours and B4 being Pay Rate

Hello - hoping someone can help! I feel (hope?) the solution is simple, but I am totally stumped!GOAL: I am trying to summarize the total number of unique values across 4 linked record fields. CONTEXT: I run a coaching business that hosts group coaching sessions (called cohorts) for clients. These cohorts sessions can span across 2-3 months. I currently have a table that summarizes the total number of group sessions for each month / client. In addition to tallying up the total number of group sessions, I also want tally up the total number of unique cohorts that are meeting monthly. ATTEMPTED SOLUTION: I thought that maybe I could create two additional fields (1) that combines all the linked records for the month / client into a comma separated list and (2) a field that then counts the number of unique values within that string of text... but I can't get that to work. Maybe because its completely wrong! Should I be using a Roll-up field instead? I am totally st

Hi I need your help. I do have a database of records with a number of columns corresponding to each record. Now I would like to send out an email to the database in order to confirm the data they have given in previous communication (original table) and additionally I would like them to confirm their willingness to take part in the competition (checkbox) and approve new consent (checkbox). I would expect to direct them to a form page with some prefilled records (email, name etc) along with new fields (consent). After submitting the form records from original table should be updated. Is that possible and how can I achieve that. Appreciate your help. Greg