Formulas

Hello. I am having issue with my formula returning errors instead of 0. I tried using adding If to the beginning of the formula and also the ISERROR but nothing is working the way I need it to. My formula is returning 1 if Purchase Close Date was last month. Below is what the formula currently looks like. Thanks in advance for any help! DATETIME_FORMAT({Purchase Close Date}, “YYYY-MM”) = DATETIME_FORMAT(DATEADD(NOW(), “month”, -1), “YYYY-MM”)

Hi,I have a series of lookup fields that return multiple dates.  I'd like to combine those fields into one, listing all of the dates returned in M/D/YYYY.  I've messed with datetime_parse and format with a concatenate, but the formatting is either not M/D/YYYY or it only returns the first date listed in a series in the lookup field.Thanks for any help!

I have an Excel formula I'd like to replicate in Airtable.This formula converts different currencies into GBP.Excel:=IF(M2+(N2/1.25)+(O2/1.2)+(P2/8.75)=0,"",M2+(N2/1.25)+(O2/1.2)+(P2/8.75))Key:£ GBP$ USD€ EUR¥ CNYM2N2O2

I am trying to take the word count of a book (ex: 100,000 words)divide that by 9,300 (which is how many words an average narrator can read per hour)to come out with the actual time in HH:MM format. This is the formula I have currently and how it turns out. .   What formula would I use to divide the word count by 9300 to come out with a HH:MM?

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()

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 

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

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'),  &nbs

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 (£)

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

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}, )) &nbs

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 u

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 a

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 d