Formulas

Hello all, Within Zapier, I am trying to “Find record in Airtable”, with the search by field being a “Last modified” field. Zapier also needs a search formula, does anyone know a formula I could use to pull the most recent Date/time within a column full of datetimes? Thank you!

Hi Brains trust. I am wanting to show the days a job has been active for, from the date the job was created. All the formulas i’ve been trying aren’t working. Any tips please?

Hello! We could really use some help on a dynamic formula for our affiliates. We are open to other alternative suggestions if there’s a better way to solve our issue. TL;DR: We would like to have a single table VIEW that we can share with our affiliate reps, but have the VIEW show different commission values, respective to the rep and their contracted comission percentage. We could easily have a different VIEW for each rep, respective to their commission rates, but it would be cleaner/easier if we could have all reps use the same VIEW, but the permalinks have their respective referral ID’s baked in. Here’s an approach we came up with, but not sure how to execute it. Perhaps there’s a better way altogether. Our main table has all of our products and their variations, with over 20 columns/fields for specs We have an “Affiliate Comissions” table VIEW, which only shows: Product name Product permalink Im

Hi Everyone, I was wondering if I can create a formula that gives me a certain number of months ahead instead of changing the filter from a certain date to date each month, sometimes we miss it, so I want to set up the filter to read from the formula I want the formula to read from my ( Release Date ), this exiting month and 2 months ahead like give me Feb, March, and April as true, so when I tell the filter to give only what is “true,” it will give me those 3 months, and it will automatically drop one and add another each beginning of a new month. Thanks a lot Ahmed Elagami

I am getting an error on a switch statement that checks a field for a value. if the value matches then calculates a formula. So lost and appreciate any help. switch({Table Data Code}, D2710, {Table Data Paid}.5, D2712, {Table Data Paid}.5, D2740,{Table Data Paid}.5, D2750,{Table Data Paid}.5, D2752,{Table Data Paid}.5, D2799,{Table Data Paid}.5, D5110,{Table Data Paid}.5, D5120,{Table Data Paid}.5, D5130,{Table Data Paid}.5, D5140,{Table Data Paid}.5, D5211,{Table Data Paid}.5, D5212,{Table Data Paid}.5, D5213,{Table Data Paid}.5, D5214,{Table Data Paid}.5, D25225,{Table Data Paid}.5, D5226,{Table Data Paid}.5, D5282,{Table Data Paid}.5, D5283,{Table Data Paid}.5, D5284,{Table Data Paid}.5, D5286,{Table Data Paid}.5, D5511,{Table Data Paid}.5, D5512,{Table Data Paid}.5, D5520,{Table Data Paid}.5, D5611,{Table Data Paid}.5, D5612,{Table Data Paid}.5, D5630,{Table Dat

Hello guys, i want to build a booking form with calendar for an apartment. The UX should be like on https://www.airbnb.de/. But It’s only about “check-in” and “check-out”. The calendar should also show the blocked dates, so u can only book available dates. The blocked dates are stored in an airtable database. This should be connected to the calendar. After picking a free time the user should see the price for the booking (prices also stored in airtable database) and then a form with personal data and so on. At the end this form will be integrated in a WP site. Thanks a lot!

Hey Airtable Community, I’m in need of a wizard helping me with figuring out how to send the same mail to multiple users. Here’s my issue: I created an automation of sending out emails which works great. The only thing is that most often, I need to send out the same mail to multiple people (but they have to think that it’s an individual name). Their name should be addressed in the mail, but whenever I sent out the testmail, all receiver names appear in the mail. Is there some formula that can send out multiple emails addressed to each one of the recipients? Happy to get more into detail if it isn’t clear :slightly_smiling_face: Thank you so much in advance Anabell

Is there a way to use the datetime_diff formula (or maybe something else) to calculate how much time an employee missed by coming late, leaving early, or not coming in at all? Thanks!

IF({Deposit Collected} = 1, “Deposit Collected”, Total/2) I want the last part of the formula (Total/2) to output a dollar amount with a dollar sign ($). Right now it’s just putting out the number, it has the decimal point and everything. I can’t figure out how to add the $ sign in front of it so the output looks like $1234.50. Any help is appreciated!

I want to create an airtable column that will say Yes if two other columns are identical, say No of those other columns are not identical, and say N/A if one of the columns is empty. Is there a way to do this? For example: Column A says Cow Column B says Cow So conditionally formatted Column C says Yes Column A says Cow Column B says Duck So conditionally formatted Column C says No Column A says A Column B is blank So conditionally formatted Column C says N/A I’ll take any solution, doesn’t have to be conditional formatting. Thanks ahead!

Hi! I am trying to add one layer of complexity to an IF formula. The original formula is this: IF(DATETIME_DIFF(TODAY(), {Date Last Updated}, ‘months’) > 24, “Possibly”) But, before returning the value ‘Possibly,’ I want the formula to exclude all records that have a category called ‘Personal Posts’ This is what I’ve got thus far, but it’s not working. Any advice? IF(AND(DATETIME_DIFF(TODAY(),{Date Last Updated}, ‘months’) > 24, (Primary Category}!=‘Personal Posts’), ‘Possibly’,)

I have a base that I’m using to track projects, which have a start date and an end date. Sometimes those projects get modified for a variety of reasons with one of those reasons sometimes being that the end date gets extended. We track all modifications in a separate modifications table that gets linked to the original project. We want to be able to dynamically display the new project end date by referencing all mods and returning the latest date. To do that, I have set up a Rollup Field linked to the Modifications table that returns the maximum value: MAX(values) But if there are no modifications, that Rollup Field will be empty, which won’t work. So, after reading the forums, I created a formula field date with the following logic: IF({Rollup Project End Date}>{End Date}, {Rollup Project End Date},{End Date}) It appears to work at first, but upon closer inspection, the date getting returned in that formula field is one day earlier than it should

Hello! Hoping I could get some advice on a formula field that accepts my formula but the cells won’t return any data (not even an #error message). Maybe it’s a bug…? Since I’m dealing with multiple formula fields, lookups and a Rollup result that is being fed back to the original table, I’m thinking it’s an issue with Airtable recognizing the data…Any workarounds or syntax advice? Problem formula is in table 2: IF({Legal Wraps After}=‘Episode’, {Episode Legal Wrap Date}, IF({Legal Wraps After}=“Season”, {Season Legal Wrap Date}, BLANK())) Table 1: SEASONS {Legal Wraps After} - Single Select Field (episode / season) {Last Shoot Date} - Rollup Field (MAX {Shoot Date} of all Episodes in a Season) Table 2: EPISODES {Shoot Date} - Date Field {Episode Legal Wrap Date} - Formula Field: ({Shoot Date} + 7 days) {Season Legal Wrap Date} -Lookup Field: (Lookup the {Last Shoo

Hello everyone! I’m working on a project that involves air tables and was wondering if there was a way I could use the formula function and if the cell is blank, then type in it. My current code is this IF(OR({Implementation outcomes}=“No”, {Implementation outcomes}=“No”), “N/A”, IF({Implementation outcomes}=“Yes”, “”)) It works fine, but when I click on a blank cell to type the tool used it won’t allow me to write. Thanks!

Hi! I’m trying to create a formula that would display a “week of” for a given date where Monday is the start date of the week. The below is the formula I came across on Airtable’s blog - it works great except it displays the start of the week’s date beginning with Sunday. My formula expertise is very limited so there might be a very simple solution to this - I just can’t seem to find one. Any help would be greatly appreciated! CONCATENATE(“Week of”," ",DATETIME_FORMAT(DATEADD({Date},-DATETIME_FORMAT({Date},‘e’),‘days’),‘M/D’))

Hi everyone, I’m a new airtable user who needs some advice how to perform the following task efficiently. I want to track some subscriptions like this: The renewal date column should auto increase under this circumstances: If interval monthly: And {Renewal Date} <= Today() {Renewal Date} = DATEADD({Renewal Date}, 1, ‘months’) If interval yearly: And {Renewal Date} <= Today() {Renewal Date} = DATEADD({Renewal Date}, 1, ‘year’) I have already tried several ways, but I am not very satisfied with them. Thank you :slightly_smiling_face:

I’m currently using the following formula for tracking Project Health: IF({FOC (Current)}<TODAY(),“LATE!”,“ON TARGET”) however there are times when the “FOC (Current)” date field is appropriately blank. Right now it’s labeling it as “Late” but I need it to show “No FOC” instead. If FOC (Current) is < today = “LATE” If FOC (Current) is =/> today = “On Target” If FOC (Current) is blank = “No FOC” I can’t seem to make the multi-IF’s to work. Thanks in advance!

I was wondering if someone could help me come up with a formula for if there is a certain status you want, that it will automatically put approved/not approved. We have a status that says Approved – so when someone has that associated with their name, we would like for them to get an automatic approval to show up. Any ideas what the formula would look like for this? Thanks in advance!!

Hello, I have a column that contains a bit of data separated by commas. For example: ROW 1: Teeth Cleaning, $150, John Smith ROW 2: X-Ray, $400, Jane Doe All this data is in one column because that is the way it is imported using zapier. Now that the data is imported, I’d like to break the data up by the comma bracket. I’d figure this would involve creating 3 new columns that are formula generated: Service Name Price Patient Name Does anyone know of a formula that will pull data before the first comma for the service name and then for the second column have a formula that pulls data after the first comma and before the second comma? Thanks for your help!

Hello, I would greatly appreciate your help finding a solution to concatenate each of the following fields while ignoring fields which contain (just) the letter “X”. (I am using “X” to denote empty instead of leaving the filed empty). FYI. This formula works for concatenating all fields while ignoring the blank ones. IF({Login Companies}, " " & {Login Companies}) & IF({Login Titles}, " " & {Login Titles}) & IF({Login Persons}, " " & {Login Persons}) & IF({Login Places}, " " & {Login Places}) & IF({Login Differentiators}, " " & {Login Differentiators}) Many thanks!!!

I am building a gym membership system. People pay for X visits which they can use in a 30 day period. When they arrive, they check in (which adds a record). I want to count the number of records for a member (COUNTALL)… …but I only want to see how many records in the last 30 days. Blaggin my head trying to work out how I can do it. Any ideas?

Hi I would like to create a formula in Google Sheets, in one whole column. I have a currency field that I would like to place the decimal in cents i.e: $0.05 I would also like to include: $0.01 Right now the format is: 05 (with no decimal place) I tried to add the decimal as an option in both Airtable and Google Sheets, but it formats it into dollars. I want it in cents, and also dollars, too. I have over 200 cells/records, so I don’t want to have to do this manually, but rather automatically, either by a formula or another method. I appreciate any help. Thank you, Mary

Looking for help creating a formula that can determine how good a certain price is compared to an average price calculated in Airtable. For example, let’s say the average price of something in a record is $100. If my price is within 5% (+/-) of $100 (i.e. $95-$105) it should conditionally show “Average”. If between 5%-15% higher (i.e. $105-$115) it should conditionally show “Fair”. If more than 15% higher (i.e. $115+) it should conditionally show “Poor”. Alternatively, if between 5%-15% lower (i.e. $95-$85) it should conditionally show “Good”. And if more than 15% lower (i.e. $85-) it should conditionally show “Excellent”. Alternatively, a rating system (e.g. 1 star being the worse and 5 stars being the best) would work too. Anyone know if something like this is possible and if so how to do it? Thanks in advance!

Hello, I have this formula below: IF( AND( {Course Payment Duration}, DATETIME_DIFF( TODAY(), {Course Joining Date}, ‘months’ ) > {Course Payment Duration} ), ‘Payment Complete’ ) this formula is meant to tell me when as user has paid a subscription based on the amount of months it been since they joined but it seems to only show payment complete when the next complete month has passed. An example is if the Course payment duration is 3 months the payment complete only changes when it it’s 3 months + the days of that month have passed. I need the payment complete to change when the 3 months have been reached. Hope someone can help Chris

What I am trying to do is use 2 nested if statements to check if 2 variables are blank (A and B). If A isn’t blank it will post A, but if A is blank it will then check to see if if B is blank and if B isn’t blank it will post B. But if B is blank as well it will post the safety string C. I thought below would work but its not working. A and B are cells and C is a string that I have as a safety net. IF({A} =BLANK(),IF({B}=BLANK(),“C”,B),A) Any help would be great thanks!