Formulas

Hello everyone! I’ve got several cols (days of the week) that have a time that I wasted in doing a task in minutes (ex: 120). All that I’m trying to do is a SUM of this minutes but in a h:mm format. Example: The task is “Book Design” and the minutes I’ve spent in doing that task are (120, 120, 60, 60, 30). The SUM of that minutes is 390… that’s 6:30 hours. All I want to have is this number 6 and I don’t know how to get it. Thanks in advance!

Hey there! Let me give you some context: I am creating a packing note for a shoe manufacturer. Here’s an example: Say we have four product batches, three of which are identical, one of which is different… I’m using the ARRAYUNIQUE rollup formula to condense the three identical product batches into one, which is fine, but I also want to add up the total pairs by size of that unique batch, rather than, say, add up all the size 8s in the total shipment. Here’s a screenshot… You can see i’ve managed to use ARRAYUNIQUE to only show the product reference and description once, but if I do the same with the size 8 column, for example, it’ll add them all up, won’t it i.e. 1 + 1 + 1 + 2 = 5, whereas I need it to be 3 + 2 I’m guessing I need a way of making the size 8 of one style unique to the size 8 of the other style??! Thanks for your help in advance!

Hi! :wave: I am having some difficulty with creating a formula in my database. I have one table - ‘Feedback’ - where each row is a bit of customer feedback, and each bit of feedback has a column for ‘Insight’ (i.e. what type of problem it is) and another column for ‘CSAT score’ (ranging from 1-5). I have another table - ‘Insights’ - which lists all the insights from the feedback table in the first column (these are linked). What I want to do is calculate the average CSAT score for each insight. I have managed to create a new column in the insights table entitled ‘CSAT Score (from Feedback)’ which looks up the CSAT scores from the other table and presents them for each insight, separated by a comma. What I then want to do is create another column which averages these scores. However, when I used the formula average({CSAT Score (from Feedback)}, I get the error message shown below.

Hi! I’m new on using airtable. I’m trying to count specific text values in a row (1 Used) and (1 Credit). In excel, I would just use countif, but I can’t figure out how to do it on airtable, can anyone help me? Thank you! Pammy

For anyone that has been trying to get a formula field to ACTUALLY substitute line feeds, carriage returns, and paragraphs here is a solution I (slowly) figured out. Use the following formula to URL encode the text, and then substitute the other stuff you do want back in. Despite all of the suggestions I saw in here, you can’t substitute the \n with anything because airtable just adds it back in. SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ENCODE_URL_COMPONENT({Your Field}),"%20"," “),”%3F","?"),"%0D"," “),”%0A"," “),”%3A",":"),"%2F","/"),"%21","!"),"%2C",","),"%3B",";"),"%27","’") If there are still some encoded characters showing up, just go here to grab it and wrap it again with another substitution. If anyone has a better solution, lemme know. DON’T SAY I NEVER DI

Hey guys, so im working on an invoicing system. I have three columns: Item Price, custom price, and applied price. I’m in need of a formula for the applied price column that says "default to item price, but if custom price is > 0 use that custom instead. any takers??

Is there any 3rd party formula editor that would show the parts of Airtable formula in different font? e.g. IF formula IF - would be one color formula conditions - would be another color field names - field names in the formulas would be different color “text” - text between “” would be in different color brackets - bracket pairs would be in different color and when I touch the bracket, the paired bracket would glow. Formula editor should give me basic error correction e.g. show missing brackets. If miniextensions had a better formula editor for Airtable with error checking, I would be willing to shell out the $39 per month for it.

Hi Everyone, hoping for some help…I’ve browsed several topics/posts and have not found the solution I’m looking for yet… Say the project event date is July 25th…and it has 16 tasks that are each dated 1 week prior. Example TASK1 = WK01 which is 16 weeks PRIOR to July 25th. TASK2 = WK02 which is 15 weeks PRIOR to July 25th …and so on…I tried a ‘switch’ formula which was promising but still didn’t give me the effect I wanted. Thank you in advance, Mel

Hello, Not sure what formulas/table configuration I should be using here. I have a table of responses to pre and post-program surveys and want to be able to compare the responses to the pre to those of the post (See image below). For each question in the survey, I would like to know the following: how many people responded with each answer for each survey (ie: 8 people said they weren’t at all excited in the pre-survey but only two said they weren’t at all excited in the post-survey). The percent of people who responded with each answer only out of those who answered that particular question. I know this will most likely involve linking records and rollup fields in a separate table but I have been trying to figure this out and nothing has been working. Any and all suggestions would be so helpful.

I have an if statement that adds 2 different types of fields together (Lookup and Currency). I cannot format the lookup field to become a currency field and I think that because of that, I cannot do any mathematical operations between the 2 fields. Is there a work around to this? I’ve tried concat-ing a “$” in to the Lookup but no such luck. Thanks

Hello there I have a list of shortened Product numbers in my table per cell. The content of a cell looks like this: Example one 1234567/8/9/6345/6 (01) What it means is: “there are unique Product numbers 1234567, 1234568, 1234569, 1236345, 1236346 and they belong to group 01” Example two 1234567/2434567 (12) What it means is: “there are unique Product numbers 1234567, 2434567 and they belong to group 12” Logic is as follows: if the preceding number contains all the values from the start, insert slash and display only deviating numbers afterwards. Repeat from next predecessor left. Print any number after the slash that is not contained (in order) in predecessor. Product Number length is always 7 digits there will be between one and 6 unique numbers displayed (example above shows 5, to be clear what I mean). Never less, never more. Group will a

Hi, I created an “adjuster value” column to tweak the amount of stock to bring it to what I need it to be as different types of consumption columns are subtracting from it. For this particular record, when I tried to use a negative number in the ‘adjuster column’ to bring the available qty to zero, I get a negative zero?!? Either this is a bug in Airtable or there’s something wrong with my formula. My formula is simply Produced - A - B - Adjuster Value = Available Any help is much appreciated. Thanks.

I want to be able to split a url address into its component and use them as I may need. What I have: “Airtable Universe” What I’d like to achieve “https://” “airtable.com” universe how would you handle this use-case? can you provide any smart solutions? thanks in advance

Hey Everyone! I need a little assistance with my formula here. Basically, I’m trying to pack alot of information into my primary field so that it appears on the calendar blocks. I have a few fields of information here: Clinic Name Clinic Date Type of Vaccine Offered Number of Doses Offered Primary Team working Supporting Team working (if any) My question is this: Sometimes there won’t be a supporting team. Is there a way to build in an “if” statement to my formula to indicate “If supporting team isn’t empty, add this clause”, essentially? Here’s my current formula: DATETIME_FORMAT({Clinic Date},‘MM-DD-YY’)&" “&{Clinic Name + Activity}&” - “&{Lead Team}&” Supported By “&{Supporting Team}&” - “&{# Doses Scheduled}&” "&{Vaccine Dose Type}

Hello wonderful community! I am still having trouble wrapping my brain around IF/THEN situations and thought I would just ask :slightly_smiling_face: I have 2 date fields in my record: Join Date Cancel Date I have a formula field that calculates months committed based on todays date that is working great: DATETIME_DIFF( NOW(), {Patronage Since Date}, ‘months’ ) But I want to have the formula look for a Cancel date and use that first…and if there is no cancel date to go ahead and use todays date. How to I amend my formula? Thank you so much!

I wrote the codes as below: IF({Priority of task} = OR("Emergency","Important","Urgent"), SWITCH({Days until deadline},3,"3️⃣",2,"2️⃣",1,"1️⃣",0,"‼️"),"This is not an important task") But the result is that all tasks shows “This is not an important task” even the priority is emergency. I wonder is it due to the misuse of OR() in the formula? Can OR() be used like IF(… = OR(…, …, …) , … , … ) this way? Thanks.

Hi, I’d like the date/time to be recorded based on the result of a formula but can’t seem to make this work. My formula basically compares two quantities (both numbers of course) and evaluates to “LOW” or “OK” depending the threshold I’ve set. I want another column to capture the date/time only if the formula results to LOW, but can’t seem to make this work. Things I’ve tried. Had the second column evaluate to NOW() if the the previous column formula result is “LOW”. This doesn’t help because as long as the prev. column stays as “LOW”, the date/time column keeps updating the timestamp based on NOW(). I only want a static timestamp of when the formula evaluated that to low. I tried to use the LAST_MODIFIED_TIME(), but that cannot work on fields that are automatically calculated such as a formula field. How would I accomplish this? Thank you.

Hello! Trying to figure out a formula to compute for hours worked between two dates/time given a condition. The formula only needs to compute for hours worked between 10:00PM - 6:00AM. Here are the scenarios: Scenario 1 Time in: May 4, 2021 4:00AM Time out: May 4, 2021 1:00PM Hours worked: 2 hours Scenario 2 Time in: May 3, 2021 11:00PM Time out: May 4, 2021 8:00AM Hours worked: 7 hours Trying to compute for “night differential” as part of our payroll. Hoping someone can help or lead me towards the right direction. Thank you!

Hi everyone! Thank you in advance for taking a look at this. I have a formula that I think is REALLY CLOSE but keeps giving me an error. The field I’m having the formula search contains a series of state abbreviations, so MI may or may not be present in the string, and I want to differentiate between MI not being present (but other states being present) and the cell being blank. The formula should work a little something like this: IF (MI is present, then “SLP”), IF (MI is not present, then “Applying”), IF(BLANK(), “Candidate”). Here’s what I have so far. Each half works by itself, but when I put them together they always give me an error. IF({SLPs Licenses (from SLP Onboarding Link)}=BLANK(), “Candidate”, IF( FIND(“MI”,{SLPs Licenses (from SLP Onboarding Link)}),“SLP”, "Applying”))

Hi, I need it to work out, IF “Recycled” is “Weekly”, make the date in “Next Post” 7 days after “Post Date”. IF “Recycled” is “Every Two Weeks”, make the date in “Next Post” 14 days after “Post Date”. IF “Recycled” is “Every Three Weeks”, make the date in “Next Post” 21 days after “Post Date”. IF “Recycled” is “Blank”, leave “Next Post” blank. This is the formula I am trying but it is returning an error: SWITCH({Recycle}, “Weekly”, DATEADD({Post Date}, 7, ‘days’), “Every Two Weeks”, DATEADD({Post Date}, 14, ‘days’), “Every Three Weeks”, DATEADD({Post Date}, 21, ‘days’), BLANK()) Can anyone see what I’m doing wrong?

Currently trying to create a formula that shows the count of a record. (i.e. first record will have a value of 1, second record linked to the same data will have a value of 2) See attached picture for ideal / expected output: Is there a formula you might suggest to achieve this? (The red-colored numbers is what I want)

Hello good people of airtable! I would like to know how to: add two percentages together, then minus that percentage from an amount? to get the sum of that calculation in this case- FEE (£40) - (CUT (10%) + TAX (20%) = 30%) = PROFIT (£28)

So im trying to calculate days that are left for a project to end. Im using DATETIME_DIFF({End Date}, TODAY(), ‘days’) but some ended projects show -2 (the ones that ended 2 days ago) how can I make them just stay at 0? Maybe an if condition. But Cant seem to find it. Thanks!

Hello! I have a situation that I can’t seem to find the best solution to. I have a reference field that needs to automatically enter the data from the first field of 4 that is not blank in a priority order. I am able to get two of the 4 fields with a simple nested IF - but I’ll still end up with blank data in the reference field. Was thinking of trying SWITCH but the text in each of the reference fields is unique. Is there a way to accomplish this via formulas? My last ditch effort would be to create two separate fields that use the nested IF statement, then use the reference field to have the same nested IF statement for the two fields created. Which isn’t very elegant at all. Thanks for the help.

I have a field in Airtable with L x W x H listed in one cell. The information follows the same format every time. There is a space followed by an x followed by another space. I would like to pull each value into it’s own cell. I know it would be easier to go the other way around, but we already have a lot of data in this format and it’d be easier for the team entering the information to continue their process. Can someone help me write a formula to get these 3 values into their own fields?