Formulas

Can someone please help - I’m not sure if a formula, rollup, lookup or another option is best for this scenario. In order to job cost correctly, I do not need the duplicates of the payment amount, as it will show more was paid. I see a way around this by adjusting the amount due, however, with multiple people entering data, and checks being cut differently, based on 3rd party financing department details of payment are not known until the check is cut, typically 30-days after the invoice is received. When a contractor submits an invoice and it is paid as a partial payment - I am currently entering the information twice to keep up with the accounting end, however, as you can see from the example below it looks as if there is still a balance due, when in fact the invoice is closed out with no balance. Thank you.

I need to re-establish a formula all ready created for our Content Title field?  I changed this to a Single line text and it changed the whole field, how do I change it back?

Hi,Somehow Airtable doesn't understand my dates as dates so grouping goes wrong. In an interface I have a list of booked events that I want to be grouped by their date. That way, I can easily see day by day how many events are booked. The grouping column is based on a formula picking out only the date from another column where the full date/time is stored.As a European I prefer 'DD.MM.YYYY' as the format, so my formula to give only the date is:DATETIME_FORMAT({date/time},'DD.MM.YYYY')This gives me visually 27.5.2024 for May 27, 2024, which is what I want. The grouping result, however, becomes like this:02.06.202414.06.202422.05.202428.5.202431.5.2024Grouping is apparently performed on the first digits, not as dates.Why doesn't Airtable understand a date that is only formatted into DD.MM.YYYY as a date?Rgds,Björn

Working with birthdays where I only know the client's Month/Day and not their Year of birth.  So text field is 5/12 for example but I'd like to have an actual date of 5/12 so I can set up reminders 5 days before the actual birthday.  I used the following formula but it's not working correctly.  DATETIME_PARSE(Birthday, 'MM/DD')Thank you in advance

So let's say I have a table that is going to have different numbers in rows listed in a single column, like below.2331618And in another table, I have some ranges filled into two columns, with a min column and max column, and a third column with a name, like so below.0-10 Sally 11-20 Joe21-30 Bob31-40 JaneNow how do I have the first table use the second table to evaluate a number to determine which range it fits into so that it can show me the name in the first table?23 Bob31 Jane6 Sally18 JoeI'm OK with formulas prior to this need, but now I am totally lost. I don't know if I am supposed to use rollups, lookups, linked tables or what. I've never linked tables before and I need a guru to translate my need into something that gives me a clue on what to try. 1st time here on this forum.It's like I want to say "see which range my number fits into and display the name for

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