Formulas

Hi! Currently I have a field which is returning a "Modified At" date. This field is a standard field (no formula) and it is returning the modified date of another field (our "Feedback" field).This is what it is returning in a problematic record:However, when I look at the revision history of the record, it says that the most recent update to this field was one month ago.Can someone help me understand why there is a lack of parity between this modified at date and the revision history?

I've found a formula which is partly working for us, originally it was showing the years and months as negative numbers but I've managed to sort out the years aspect but I can't figure out what needs to change to make the months show correctly.currently shows the response like this 0 Years, -7 MonthsFormula being used is DATETIME_DIFF(NOW(),{D of B},'Years') & " Years, " &DATETIME_DIFF(DATEADD({D of B}, DATETIME_DIFF(NOW(), {D of B}, 'Years'), 'Years'),NOW(),'Months') & " Months" can anyone read this formula or add to it to make it respond with years, & months 

Hello, I am a user who studies airtable. I would like to inquire about the function of using airtable.In Excel, a fomula can be calculated by referring to previous row (previous record) values.So it's easy to implement how to get the following balance, but I haven' t found a way yet in airtable.Nocontentincomespendbalance1apple10 10 2banana20 30 (=10+20-0)3cherry 1515  (=30+0-15)4hotel 510  (=15+0-5)5fox 100  (=10+0-10)I would like to add or subtract this record value to the previous record value so that the cumulative balance comes out.I asked the same question a few days ago, but I didn't receive the answer I wanted, so I'm requesting it again.Thanks. Have a nice day.

Hello! I am trying to format my data in a very specific way and require underscores between the text. I know when using a Lookup and formula there are challenges because the Lookup format is not a string and the commas within the field do not actually exist. I tried converting the field to a string and using a substitute formula but that just removed the commas and spaces from the field. Current Lookup Format: pa, nv, flSubstitute Format: panvflDesired Format: pa_nv_flDoes anyone know if there is a formula to add underscores to a lookup field that contains multiple options?Thank you in advance and I appreciate any support in solving this!

Hi there,I dont understand the difference between 2 formulas : IF( AND( {Startdatum Leverancier (rol repowered)} != BLANK(), {Einddatum leverancier (rol repowered)} = BLANK() ), "OT", IF( AND( {Startdatum Leverancier (rol repowered)} = BLANK(), {Einddatum leverancier (rol repowered)} = BLANK() ), "", "BT" ) ) This formula does  NOT work as intended and ALWAYS shows OT regardless of the 2 fields content (empty or filled). I need to add that these fields are date fields.  IF( {Startdatum Leverancier (rol repowered)} = BLANK(), "", IF( {Einddatum leverancier (rol repowered)} = BLANK(), "OT", "BT" ) ) This one DOES work as intended. Although I fixed the issue my curiousity is getting the better of me what I did wrong in the first bit of code. Anybody ideas? 

Hi everyone!I'm trying to organize my sales data in my Airtable base.I add the data through CSV upload. For every day of sales I add I add a new column to my sales data table.Now I want to run automatic calculations on the data. To give an example: if sales for a particular product are decreasing for a few days in a row I want to make this visible in Airtable, or automatically send an email as a warning. Now the problem is that if you create calculations to do this you do that for the existing columns, but every day I'm adding a new column to the table, so than the calculation won't include the newest data.Does anyone know how to deal with this?

We are implementing time tracking fields in multiple bases and need to calculate the total amount of time between two date fields (in days, hours & minutes) but also excluding weekends and possibly holidays. I've tried the following but the calculations are returning errors. WORKDAY_DIFF( {To In Studio},{To Color Correction},    'days') & " days " &MOD(WORKDAY_DIFF({To In Studio},{To Color Correction},        'hours'),    24) & " hours " &MOD(WORKDAY_DIFF(         {To In Studio},{To Color Correction},        'minutes'    ),    60) & " minutes" We have the following implemented and working, but weekends are not excluded

Hi all,Struggling with this one and I'm not sure if it's even possible.I have a table that works out total cost of a product, total cost of a product inc fees on Etsy total cost of a product inc fees on shopify.Etsy fees are always higher than shopify, so it makes things slightly easier.I currently set the retail price manually and then it shows fees, profit margin for shopify and etsy separately.What I'd like to be able to do is suggest a retail price for etsy, based on matching the profit margin of shopify. The fees of Etsy are higher so I can't just add the profit margin difference to the retail.I do have individual columns for each fee type if that helps?Here are my columns I currently have - Total CostRetailEtsy Transaction FeeEtsy Processing FeeEtsy Operating FeeTotal Etsy FeesTotal Cost with Etsy FeesEtsy Profit (£)Etsy Profit Margin (%)Shopify FeeTotal Cost with Shopify FeesShopify Profit (£)Shopify Profit Margin (%)I'm assuming I'd need to do something like work out the %

Hi there, im new to airtable and need some help. 🙄I would like to create a form in which people can register for a work shift. There are four shifts with a maximum number of participants. For example:Shift A = Max. 5 participantsShift B = Max. 2 participantsNow I would like only the free shifts to be displayed in the form. So if 5 participants have already registered for shift A, then this should no longer be selectable or available.Can anyone help me here?

Hi all - I need help with this formula as I was able to use Chat GPT to get me close to where I need to be, but it still doesn't work.Can someone rewrite this formula so it works: IF({End Date} > "7/1/2024" , {Latest Revenue Projections} * ("6/30/2024"- {Start Date}+1) / ({End Date} - {Start Date}+1) , "")

Hello! Im looking to recreate this function in Airtable=COUNTA(UNIQUE(Masterlist!C5:C))Where the column in Airtable I'm looking to count unique entries in is called CLIENT NAME. Unfortunately, it doesn't look like the UNIQUE function works, is there a replacement or workaround?In short, I'm looking for a way to count unique entires in the CLIENT NAME column, i.e.:McFly, MartyMcFly, MartyGambini, VincentIpkiss, StanleyIpkiss, StanleyIpkiss, StanleyHillard, DanielCounting this column would come back as a count of 4Thanks in advance for any help 🙂

I am using a formula cell to check a date/time cell to confirm if the time in EST has occurred or not yet.The Date/Time cell contains something like 5/12/2024 2:30pm ESTMy Formula cell uses the formula:IF(AND({Start Date},DATEADD({Start Date}, -5, 'hours') <= DATEADD(NOW(), -5, 'hours')),"true",BLANK())What I'd like to happen is for the Formula cell to return "true" when the Date/Time cell reads a time that is equal to or before the current Easter Standard Time.  Note: I don't want it to use my current time zone because that can change based on where I am.However, the problem I'm having is that it seems to be using my current time zone (which is CST).

Hi everyone!Following problem: I have a table where each record is represents a deployment of a worker, sick leave, a project or Vacation. We differentiate between them with a single select field. Each record has a start and an end date + is linked to a Worker and/or a Project. Now currently the Name field is just a Code identifying the Type + an autonumber. However if one particular worker is linked, I would like the Name field to Dispay "Linked Project". The reason is that this particular worker needs to sync his records to an external calender application. But its quite useless if that only displays things like "deployment-322" or "Project-102". Now technically this would be very simple, the formula works fine as long as its not the name field.  IF({worker} = "John", {project}, SWITCH({Typ}, "Projekt", 'PROJ_' & {nr}, "deployment", 'DEPL_' & {nr}, ))  However once I try to use this formula as title field, airtable complains a

Hi, does anyone have any tips on how to create a formula that  would calculate the number of days something was in each stage? We track various stage gates for our content: Draft, Proposal, Ready for Production etc. Thanks! 

Hey there. I’d like to alphabetize one set of records in a table but would like to write a formula that would exclude the word “the” at the beginning when alphabetizing. (I rather not just format as Title, The.) Any possible way to do this, or no, because this is essentially a sorting issue, not a formula/record-naming issue?

Hello all,New to Airtable and I'm trying to build a product costing and BOM system.I have worked out from searching for posts on this forum that I can split a Lookup cell based on text using the following IF( FIND(",", {Product Costings}), LEFT({Product Costings}, FIND(",", {Product Costings})-1 ), {Product Costings} ) But when I try to use this same formula to split the cost cells it doesn't seem to work.I basically have a 'Total Cost' column which is currently filled with £0.70, £1.00 and £0.11I want to split these into separate cells as I want to specify quantity for each value. For some reason the above script will not work for numericals, and it just lists the full 0.70, 1.00, 0.11Is there something I need to change to make this work? Or are there any other alternatives?What I want is -Kit Row - Combined Individual products that make up the kit - Number of each individual product needed to create the kit - Price of each individual item - Total price of each ind

Hi,  I am trying to clean up info from online forms we receive from another company. I need to extract the mobile numbers so that I can automate the contact process as CRM.Anyway, I have a reference column with the name "message". In there is text as follows: Gender: maleFirstname: BrianLastname: DawsonEmail: brian@valuesystems.com.auStreet: Albert DrNumber: 73ZIP: 2071City: SydneyCountry: AUPhonenumber: +61418212496OR AS FOLLOWS:Street: Ponderosa paradeNumber: 8/13ZIP: 2102City: WarriewoodCountry: AUPhonenumber: 0450294193All i want to do is generate a result of a standard australian mobile number to look as follows: All australian mobile numbers are 10 digits... So i would need to convert the +61 prefix to 0 as well...0450294193Please help... I have tried this all day and get close, but not reliably so! Current formula: (MID(message,FIND("04",message,1),10)) Thanks!

Hello,I have a very simple request.I simply need to generate a formula to compute the first and last name of my contacts.However, as simple it is, I do not have the text format in the formatting bar. Only Number, Currency, Percent and Duration.Then it is impossible for me to create the basic field {First Name} & {Last Name}Is it possible to allow the text format anywhere?Thank you very much for your help.Franck

Hello All, I am new to Airtable & have searched but could not find a solution to my query. My table has the following fields (and a couple of more). 5 days before the due date, I send myself a reminder mail. And, this seems to be fine (preview tells me it is fine). The issue is the format of the due date. I want the format to be 22-Jul-2022 but the email shows 2022-07-22 I used the formula in the email, but it is treated as text in the email. I created another field (column) with my kind of format and the column shows the format, but when I reference this column in my email, the content is blank. Can you please help me? Best, Vikas

Hi,I am creating a formula for my fixed field for a sponsor base. Not all sponsors have business names; therefore I am looking for a formula that will be something along the lines of:IF({Business Name (from Contacts)}, {Business Name (from Contacts)}, "{Contacts}"). However, the " " shows up as the physical text rather than what is in the Contacts field.  What should I be doing instead? P

(i edited this to make more sense hopefully🤪)I want to create a new date and time, by stripping the time from a date, add a certain number of days, then add a duration field back as a new time to the date such as :1/3/24 11:00 am   to   1/3/24    add 126 days becomes   5/8/24  then add 15 hours to get the result of 5/8/24 3:00 pmhere is my formula:DATEADD(DATETIME_FORMAT(DATESTR(DATEADD(DATESTR({Initial Date}),{Days to Add},'d')),'MM-DD-YYYY'),{Duration},'s')  However when I use DATEADD to add the duration as a time, it assumes the starting time of 18:00 / 6pm  AND cuts the days short by 1 so when I add the duration it adds it to the 18:00 / 6:00 pm for some reason not from 0:00 /12:00 am and is the day prior so 5/7/24 not 5/8/24 If I just use this it gets me the right date with no time: DATESTR(DATEADD(DATESTR({Initial  Date}),{Days to Add},'d'))  FYI the initial date field is formatted for the right

Hello-I have a lookup cell that is pulling data from multiple cells. The results are showing a list of topics. The topics are repeated (as they're pulling from different cells). You can see, for example, that "Space" is being pulled multiple times. Is there a way to tell it to only list topics that haven't been listed, meaning only unique topics (not duplicates)?  Thanks in advance!!

I have a table that is creating a record for each client that completes a form. I have another table on the same base which is a synced google drive table with all client folders. Is there a formula/automation I can use to link the "open in google drive" button from the synced drive table to the client table? 

Hi! I thought a simple formula shown below would calculate price per square foot for my table. {Price} / {Building SqFt} Both Price and BuildingSqFt are columns in my table, so I am not sure why the formula gives me an ERROR response. What formula do I need instead? Thank you!

I am using a DATETIME_FORMAT formula to reformat a Date input field and make it pretty. For some reason no matter what time I put in to the Date input field the formula field spits out a time exactly four hours later. Anyone know why this is happening?