Formulas

I track consignment items in Airtable. This is my consignment pricing structure: Day 0-29: Initial List Price Day 30-45: Initial List Price minus 15% Day 45-90: Initial List Price minus 25% Day 91: Expired What formula will auto-discount the initial price by the above amounts based on the date when the item sells? Can the entire cycle of auto-discounts be calculated in one column? What formula can be used to mark an item “expired” on day 91? - could this formula also be included in the same column as the auto-discount column? :slightly_smiling_face: Thanks in advance for any help or insight.

I use Zapier Parser to bring over a PDF attachment and update a record in AirTable. The issue is it takes the email attachment and gives it a super long filename in AirTable with letters and numbers. Is there any way to have it be a set filename or custom filename instead? I tried using Format from Zapier but not having any luck.

Hi, having issues combining two different IF statements for contracts: IF(Phase=“ :inbox_tray: Lead”,“ :bell: ”) IF(DATETIME_DIFF(TODAY(),{Last Contact},‘days’) >= 7, “ :bell: ”) They both work separately but want to be able to combine them so that IF a contract is in Lead phase AND the last contact date was 7or more days ago, then follow up ( :bell: ). Every time I use the (AND) function, following the example here, it doesn’t work. Is it because the Phase field is a function?

I want to know (and then trigger an automation) when there are 10,000 records added to my table. How can I do this?

We want to know how to fetch a field from another table and make it multiselect depending on the record it relates to. Example in record one, I bring a field from another table (This one has several data in the same column) and I want to be able to select one, several or all; for record two, I bring the same field from the other table and I only want to select some that are not the same as the one in record two. In the first record, choose a data from the generic task package, in the second record, choose other data

I am trying to create a table that calculates equipment depreciation by straight line, sum of years, and double-declining, because we have a number of different equipment types that depreciate in very different ways, but want to monitor all of them in one place. I am struggling to find a way to calculate the accumulated depreciation in each given period. Straight line was no problem, but the sum of years is reminding me a lot too much of high school calculus and I’m ready to take it all back into an Excel sheet no one will ever look at again. I haven’t tried the double-declining yet, but I’m hoping it’ll be easy after sum of years! Is this even possible in Airtable? I suspect I will need helper fields and don’t really want to go there in case some future equipment has a much longer lifespan than I’ve built in. Excel has an SYD function to get the period Depreciation Expenses, but nothing (that I’ve found) to give you the Accumulated Depreciation without referencing another row.

Hello beautiful community! I hope you are doing well. I’m looking to optimize timesheets on airtable. I have almost everything set up, but, I am missing a feature: “cumulative time” / overtime. In fact I would like to have in the time bank who is the sum of the overtimes per employee over all periods to know how many hours the employee or the company owes (time balance) I was thinking of adding the SUM/Overtime by period and have it appear in the employee’s profile (Banque d’heure) Do you have any idea of the formula / process to do this? After that, From this bank of hours, the employee could with a form say: I take x hours for recovery. it makes sense? Thank you for your help! Template source : Payroll Time Sheet - Airtable Universe

Hi there, I’m facing a challenge with a lookup / rollup field. I will share a link to an example base I quickly threw together, feel free to check it out to better understand my challenge. Keep in mind that this is a very simplified version of my original base. I have a lookup field which lives in a table of drivers which gives me cars used for different trips. If a driver uses a BMW X3 on 3 trips, the lookup field shows “BMW X3” 3 times. If that same driver uses a Tesla Model 3 for 5 trips, the lookup field would contain BMW X3 3 times and Tesla Model 3 5 times. So far so good. Now, what I want to pull out is simply “Tesla Model 3”, because that was used the most times. If one driver has used two/three cars the same amount of times, I would like to pull out both/all. I’m not sure if it matters but in my base there will never be more than those 3 cars to select from. Also, it is not possible to use more than one car for a trip. Does anyone have any tips and tricks to overcome this?

Hi… the forumulas are quite challenging for me. I am using Airtable with the Airpower Shopify App. A total game changer but I have had to learn Airtable. I want to create a new column where I take my Product Title and make it into a slug (for a webpage title). However I often use hyphens in my titles and all my attempted formulas still create a double – hyphen. Example {Product Title}: Vintage Futa Lures - 9" Slant - MOP Slabs - Like New Result: vintage-futa-lures-9"-slant-mop-slabs–like-new (between slabs and like there is a double hypen but looks like a long dash here) What I want: vintage-futa-lures-9"-slant-mop-slabs-like-new Formula: LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( {Product Title}, " - ", " "), “–”, " “), " -”, " "), "- ", " "), " ", " "), " ", “-”)) Thanking you in advance for any suggestions!

Hi All I have a formula I am using to take a date and add a month to it. The problem is that I specifically have some fields blank. Right now the formula works, but where there is a blank date field, it shows ‘error’. Is there a way to modify the formula, so it would ignore blank date fields and not display ‘error’? DATEADD({Due Date},1, ‘months’) Thanks Emma

Hi - New to Airtable and hoping for some help. I need to find a formula that displays the next occurring Monday date based on a date in another field. Thank you for any suggestions.

Hi I have a formula I found in airtable for creating recurring events. This appears to mostly address when the year rolls to a new one - so that it changes automatically. What I’d like to do is in my table have a due date of an item - called RECURRING DATE. In a new column called Due Date - I want a formula that will automatically advance the date to the next due date once current due date goes by. I then want to use that Due Date in a calendar view. The formula I found is: DATETIME_PARSE(DATETIME_FORMAT({Recurring Date},'MM/DD')&"/"&YEAR(NOW())) I tested this with a December 21 date and it does in fact advance the year to the new year, but not the day. So if the recurring date is 1/5/22 and we are now on 1/27/22, I want my due date column to show 2/5/22. Is there a way I can modify this formula to achieve that?

Hey guys! I have the exact same formula on Airtable and Zapier, it’s working on Airtable but not working on Zapier, what am I doing wrong? (The purpose of the formula is to check if my “Shopify ID (Produto)” is inside a bunch of ID’s that I get from Shopify) The formula: Thanks a lot

I have a “All Completed Tasks” table that holds all of my completed ClickUp tasks - two of the columns are: Project Title and Total Time Tracked. I have a second “2022 Projects” table that lists all of my projects for the year. I want a field/column in this table to calculate the total time spent on the project, based on the ClickUp tasks from the first table. I know enough to have a Project Title field in the 2022 Projects table that would match the Project Title field in the All Complete Tasks table (which I think is necessary). But I can’t figure out how to do the calculation. EXAMPLE All Completed Tasks table may show: ABC Project - 12 min XYZ Project - 9 min XYZ Project - 7 min XYZ Project - 4 min XYZ Project - 9 min ABC Project - 11 min ABC Project - 8 min I would like for 2022 Projects to calculate: ABC Project - 31 min XYZ Project - 29 min

Hello! Noobie here trying to get a bit of help creating a formula. The goal is to get some visibility on when I last commented on a record vs when someone else last commented. This will help me see if they have replied or not so I know to follow up. I already have two formula fields: {last commented by me} (datetime) - this field is set with the timestamp of when I last commented {last commented by someone else} (datetime) - this field is set with the timestamp of when anyone but me last commented So far so good. Now, let’s say I commented on a record 2 days ago, so the {last commented by me} field was set to 23/01/22 6:30 pm. Let’s say it’s now 25/01/22 6:30 pm and I have not yet received a response, so the {last commented by someone else} field is empty, or maybe before 23/01. I’d like to be notified at this point so I can follow up. My thinking is to have another formula that calculates the time from now to when I last commented - sort of like a timer. DATETIME_DIFF(NOW(),{Las

Hi, can I get a blank space/nothing instead of “0” as the result of a formula ?

Hey guys! I need a little help with a formula. Basically I’m sending data to Airtable that contains text separated by commas, and I need to check if a specific column contains a part of that text that I’m sending. I’m currently using Zapier with the Find function, but I’m having problems because it’s trying to find the complete string of text in the table and not considering the commas. In short, I want to know if this record in my column contains a part of the full text that I am sending. Here’s a simple graphic to explain:

Hello, I don’t know if it’s a stupid question or not, but I was wondering if this exists. In a CONCATENATE formula I was collecting names of ingredients and quantities, but I didn’t like how “0.25 lt” was looking like, so I thought of changing “0,25” with “¼” using an IF function: CONCATENATE(IF({Q.ty} = '0.25',"¼", IF({Q.ty} = '0.5',"½",IF({Q.ty} = '0.75',"¾",{Q.ty}))),' ',Ingredient) And it works, but obviously it works only with 0.25 and not with 1.25, 2.25 or more. (I don’t know why but every attempt to upload the example picture fails.) I was wondering if I could use a letter or something, like you can do in Math with the letter “n”, to write the expression like this: CONCATENATE(IF({Q.ty} = 'n.25',"n ¼", IF({Q.ty} = 'n.5',"n ½",IF({Q.ty} = 'n.75',"n ¾",{Q.ty}))),' ',Ingredient) Probably I could do something like changing every “.25” with a “¼”, but it could works with every number except 0, because it will result in “0¼” which is more ugly :grinning_face_with_sweat: I know I co

We have a base that we use for our accounts receivable process. We created a column that calculates how many days old the invoice is with the following formula: DATETIME_DIFF(TODAY(),{Date of Delivery},‘days’) . This takes today and subtracts the day of the invoice and returns a total number of days old. We have the base grouped by company. We want the company that has the highest number for days old (Meaning most overdue invoice) to be on the top of the base. We are not getting this result when we group by the calculation, it is only arranging within the grouped field by this calculation. How do we get the whole base to continue to sort by this field every time an order is marked as paid, and therefore removed from the base?

Hi there, Lets say I have four fields: A B C D I would like to create a single formula field that could show which of the above fields are blank. So, for example: If only A is blank, the field should say “Field A is blank” If A and B are blank, the field should say “Field A and B are blank” And so on … I have been able to create a formula that tracks whether all the fields are blank, but I am struggling to find a way to use the formula field to display which specific fields are blank. If anyone has any advice, that would be much much appreciated!

Please could somebody help me troubleshoot this formula? I’m building a financial forecast and have a number of new business opportunities to build in. Not all opportunities have the same probability of converting into a sale however so I need to create a weighted value for each opportunity to give me a more realistic view of what my pipeline value is. The calculation needs to be Contract Value x Probability = Weighted Value, however I’m struggling to work out how I incorporate the multiple options into my formula. I have 6 probability options in total: 100% 75% 50% 30% 10% Lost (0%) If anyone could offer some advice, that would be much appreciated! Thank you.

I have some entries that have a long text field for example example.com example2.com HugeDomains Example3.com is for sale | HugeDomains Since 2005, we have helped thousands of people get the perfect domain name. example2.com Is there a way to show the total unique urls? I have this formula ABS(IF(LEN(SUBSTITUTE(TRIM({URL/Video Match URL}),"\n","")) > 1, LEN(SUBSTITUTE(TRIM({URL/Video Match URL}),"\n","")) - 1 , LEN(SUBSTITUTE(TRIM({URL/Video Match URL}),"\n",""))) - LEN(TRIM({URL/Video Match URL}))) But this formula only counts the total of the lines/urls. I wish to only count the unique urls. The name of the field is: URL/Video Match URL

Hello! Thank you everyone in advance for your help. I use Airtable to manage the availability of a little over 100 therapists who work for my company. Most of these people work part time and, thus, have wildly different working hours. I have a formula field that calculates their total daily available work time and that field is summed within their group so we also have access to their weekly availability. The Max 30 Min Sessions/Day column uses this formula: ROUND(((({Hours per Day}*.6)/3600)*2),0) to calculate the maximum number of 30 minute sessions that fit into 60% of their daily availability. (They need work time outside of therapy sessions.) In general we’ve been using this formula to determine how many students maximum a therapist can be assigned. So my first question is: does this formula actually tell us how many 30 minute sessions fit into 60% of a person’s day? Or does it need to be changed? We use the same general formula to calculate a therapist’s capacity for seeing stud

I am trying to create a to do list view. Some items are linked to multiple days and are grouped together when sorted by date. I am looking for a formula that woulds sort them by the first date only. I have tried a rollup with the MIN function and just get a result of 0.

Hello, I’m new to Airtable & I have a formula that an Airtable support doc says should work & I can get to work in Excel, but keeps returning an error in my Airtable base. What I want to accomplish is this: IF the Jan 22 Commission Field is not blank, Jan 22 Prof Calc should equal that. Otherwise, IF Jan 22 Actual is blank, Jan Prof Calc should equal zero. IF it’s not blank, Jan 22 Prof Calc should equal Jan 22 Remaining. Thanks in advance!