Formulas

Hello! New Airtable user here! I’ve been racking my brain for days trying to figure this out. I am trying to create a formula based on a selection. What I am trying to accomplish is: If the rate is HOURLY, calculate date/time difference for # of hours If the rate is DAILY, calculate date/time difference for # of days if the rate is TOTAL, calculate the rate X 1 Any help or suggestions would be awesome!

I love Airtable’s ability to create views and then sorting, filtering, and grouping to see things like average, median, range, etc. I am using Airtable for a pricing database across multiple vendors (basically, see how much vendors are charging other companies with similar characteristics (e.g. by revenue, employee size, a combination of the two, etc.) I am user Stacker.app as a front-end. As pricing data is entered and updated, you can slice and dice with different views. However, that takes effort by my users. I’ve tried the pivot table app and while that’s ok, you cannot run multilevel analysis like you can with the views (e.g. show me average price for companies with $5-10m in revenue and 100-250 employees and compare by vendors X, Y, and Z). Also, embedding these apps opens up the whole base which I definitely dont want. Ideally, I’d like to just be able to create a table or view (maybe using a Gallery) that I can then embed through Stacker. Anybody know if there’s a way

Hi there ! I have been struggling a lot for i guess a simple formula … I have a table with contact informations (name, product of interest) Then a second table for tasks (see attached) I want that when a product A is selected, to calculate the task completion (as you can see on the 100% cell) the formula I used the following formula : IF({Product of interest}=“A”,SUM({A-1},{A-2}),"")/2 But, if a customer select product A and B, I want the task completion to be calculated with the checkbox “B-1” and B-2" as well … And same in the case a customer pick product A, B and C, I want it to be calculated with C-1 and C2… I have been looking with IF formula, Switch formula or even Substitute, but i can’t find a correct solution ;( Any good way to do this ? thanks in advance !

Hello, I need help trying to figure out this formula. I have spent the last three days looking through the community and I believe this is the closest article I can find, (Formula Help! IF AND + checkboxes) but I still can’t seem to get this working. I originally created an IF statement based on blank fields. The purpose was based on keeping track of our workflow. Now, we have the need to added other “flags” that I will need to change the formula to "Done! " without completing all the steps from the original IF statement. The first flag is a “Canceled” Checkbox. If the checkbox is checked I need the status to change to “Done !” regardless

I am importing a financial summary from my bank which comes in CSV, and the amount of expenses comes in negative numbers. How can I convert a whole field with negative sign before the number to positive/delete the negative symbol? And how can I switch the dot and comma symbol from US to international? e.g 1,234.56 to 1.234,56 Thank you!

Hello, in the past i used extract url from img with the following function MID({Media},FIND("(",{Media})+1,LEN({Media})-(FIND("(",{Media})+1)) But this time, the FIND function isnt counting well for some records. i don’t know why. here is the results .

Hi there, I recognize the power of regex expressions, but I’m just pathetic at composing them, so I’m reaching out for help from someone who actually understands the syntax. I have a table in which all entries contain a URL with this structure: https://scholarship.ode.state.oh.us/Student/Home/Details/555555?stdntID=444444 From record to record, only two components of the URL change: The number immediately following Details/ (i.e. 555555) The number immediately following stdntID= (i.e. 444444) I need to write two formulas, one for each of the components named above, to extract these unique numbers. If anyone could help in this regard, goodness what a kindness it would be. Thank you for taking the time to read and consider.

In the MP IMU% field I have this formula: IF({MP ICOGS},SUM({MP IGP} / {MP ICOGS})) My question is how do I change my formula to return a value in the MP IMU% field that is not a negative percentage? The true answer is 281.34% - I’m beyond stumped??? :woozy_face:

I am comfortable creating and linking bases, but I really need help inside my grid view for 1 item, I just need to understand how to create a formula that will auto-populate if one of my users has been assigned an item, this formula column should have 4 different selections either checked-out, available, damaged or lost. Could someone please help me? PS, I have been using the Filter options and another view meanwhile to display what I need, but I need this to not be having to change my database to check-out or damaged after every new user assignment.

Hello! Excuse me if there is an easy solution for this. I would like to be able to auto-populate my “price for ad” field based on my single select column “ad size.” There are 31 different selections and 31 different prices. Do I need to write a formula for each selection and the price, or is there an easier way to accomplish this? The formula that I am trying is: IF({Ad Size}=“10x10 PBPost”,1108.50) I’m having no trouble with the formula running, but it is not returning any values to the formula column.

Hi! I have a formula to calculate workdays difference between opened ticket and closed status, or today if not closed. Works great in the rest of cases, but in a specific record, a negative value is given, while a record with similar values returns the correct amount. Below is a screenshot, the translated terms are: Open days for this ticket, Created ticket date, :date: Solved ticket date The formula for the “open days for this ticket” is like this: IF( (IS_BEFORE({ :date: Solved ticket date},TODAY())), (WORKDAY_DIFF({ :date: Solved ticket date},{Created ticket date})), (WORKDAY_DIFF({Created ticket date},TODAY())) ) and the formula for :date: Solved ticket date is: IF( Status = “Finalizado” , DATETIME_FORMAT({Estado cambiado} , ‘DD/MM/YYYY’),BLANK())

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!