Formulas

Hello,I have a table that is synced with transactions from my bank account, but I need help with extracting data from a JSON string stored in the Notes (Long text) field.An example of the JSON looks like this: { "transactionId": "2022142460021", "endToEndId": "\/VS2501000545\/SS\/KS308", "bookingDate": "2025-01-10", "valueDate": "2025-01-10", "transactionAmount": { "amount": "-390.00", "currency": "EUR" }, "currencyExchange": [ { "exchangeRate": "1.00000000", "quotationDate": "2025-01-10" } ], "creditorAccount": { "iban": "SK9111110000002303283006" }, "debtorName": "COMPANY s.r.o.", "debtorAccount": { "iban": "SK8511000000002622224000", "currency": "EUR" }, "remittanceInformationUnstructured": "Platba faktury 2501000995", "remittanceInformationStructured": "[{\\"reference\\": \\"2501000995\\", \\"referenceType\\": \\"VariableSymbol\\"}, {\\"reference\\": \\"308\\", \\"referenceType\\": \\"ConstantSymbol\\"}]", "additionalInformation": "Platba 1111\/000000-2303283006", "bankTransac

Hi, I have a table that contains the first and last names of people and what I’d like to do is to have a field beside this that counts the number of times a first name + last name shows up in the table. This is because it is possible that I can have multiple entries for the same person and I eventually want to extract unique users for use in another table. The first thing I need to do is to identify duplicates, but I’m struggling with this. My approach was to concatenate & lower case the first and last name to get a standardized format to check for duplication. Would appreciate any help on figuring out how to find duplicates in the table. Also, it’s not enough that I just know if a record is a duplicate or not, I need to know how many times it shows up in the table, so some sort of count formula but I can’t seem to figure this out. (names are not real) Any help would be much appreciated. Thanks so much.

I'm trying to set up a formula that will give me the date of "Monday" relative to a given record's date. So if a record was dated today Wed June 21, I'd want the formula to give me Mon June 19.I've done some searching on the forum here and found this formula: DATEADD(Date,1-DATETIME_FORMAT(Date,'E'),'day')But I'm having some issues with it—it's returning the date of Sunday, not Monday, and I can't for the life of me figure out how to adjust it!Does anybody know how I might fix this? Thank you!

I've tried to work my way around this problem several ways and I can't seem to figure it out... I have a nested IF statement that uses a value from a text lookup field that just doesn't want to play nice. Essentially, I have a formula title for a record that should work like thisIF {single select}= "A", {text1} & " - " & {text2}, IF {single select} = "B" & {lookupfield} = Not Empty, {text1} & " - " & {lookupfield}, {text1}))I can't get the lookup text to cooperate and express itself as not empty (or empty). I tried T(), BLANK(), and COUNTA(), and even though in a separate formula field COUNTA() gives me 1 and 0, trying to incorporate that into the overall formula doesn't work. I get that the lookup field isn't actually text... I'm going crazy trying to figure this one out.

Hi there - I schedule shipments to be picked up on specific dates from specific sites at specific times, and I need a way to identify if I've scheduled any shipments to be picked up on the same date/site/time to make sure I'm not double booking a site. I've created a formula in my Shipments table that concatenates the three fields:IF(  AND({Load Date}, {Site Abbrev.}, {Load Time}),  DATETIME_FORMAT({Load Date}, "MM/DD/YY") & "_" &  {Site Abbrev.} & "_" &  {Load Time},  "") And I've created a Rollup in my Sites table using ARRAYJOIN(values, ", "). All that seems to be working just fine. But the formula I've created to identify any duplicates in the Rollup field always returns No Duplicates even if there are duplicates.  IF(  LEN(ARRAYJOIN({DEV | Load Date, Load Site, Load Ti

If my event is September 2, 2025 (aka "program start date"), it would be in Quarter 3, 2025. But I need to list that commission would be paid the following quarter (aka "quarter earned", so Q4, 2025. Fiscal year runs Jan - Dec like the calendar.I currently have the formula (see below), but its marking everything in FY 2024. instead of in the appropriate next quarter. I know I am missing a small tweak, but cannot figure it out! 'Q' & IF(ROUNDUP(MONTH({Program Start Date})/3, 0) = 5,1,ROUNDUP(MONTH({Program Start Date})/3, 0)) &" - FY" & IF(ROUNDUP(MONTH({Program Start Date})/3, 0) = 5,25,24) 

I am trying to sort by the Week # of the year that an entry is assigned on my base. Its sorted by year, then by week - so 2024, week 1 through 52, etc.However, for anything this week up until the 31st, its showing up as the 1st week of Dec 2024 (which confuses me as you would think it'd be 1st week of 2025?). I would like that to show up as the last week of Dec 2024 (week 52) or even Week 1 2025.Formula is:WEEKNUM((Assigned), "Monday") It is pulling data from the "Assigned" column, which is listed in the format: 12/30/24 11:00amThanks!

Hi again,Is there a way for you to have data from a field "written" to that field and fixed for those changes elsewhere will not affect the result in this field in the future?An example of this might be;A fee in one table is designed to be updated in future years, and that field is referred to in other locations. Once that booking has been confirmed and paid, I want to be able to "fix that price" for that booking so that if the lookup value in the first table changes in future, that the confirmed bookings either do not change or, that a field has been updated which then remains at the agreed price.I hope that makes sense? Thanks S

Hello everyone!I need help with a formula that computes two fields as {field 1}/{field 2}.However, the base is new for my team, and we're working to input backdated data, so some records that use these fields are 0 (zero), at the moment. The formula field that computes the two fields is used on current data, and we'd love to eliminate the infinity returns so as not to interrupt the SUMs we use in other areas of our work.I've played around with several iterations of the formula and solved one to lose another 🤪 The current formula I'm using to keep us sane in the interim is:IF({KGM 500 Total Attendance}>0,{KGM 500 Total Attendance}/{KGM 500 Total Capacity}) Attached is a screen shot of the fields in the base for reference. As we work to gather missing data, Total Attendance may be inputed first or second to Total Capacity. When the missing data is entered, both fields will have a number greater than 0, I want the Formula Field to compute {KGM 500 Total Attendance}/{KGM

HelloI would like to build a base where I have tables with planned income, planned cost and summary table that reflects incomes and costs on monthly basis.My question is, if it is possible to take bank balance as a basis for the calculation and calculate Bank balance +income - cost for each record so that the basis for each calculation would be the result of previous row? For clarity, here is how I would like it to work:I have seen some similar questions here as well, but I am not sure if they would work for this use case as well.Thank you in advance.  

Hello!I am wanting to add a DATEADD formula to my base to set deadlines for different tasks based on a hiring date. I've done this in a previous base, but have since lost access. Can someone help me with the formula I need? For reference, some of the formulas I need help with are:Task: Email for photo, new hire survey, and short bio of new hire (-7 days before Hire Date)Task: Assign desk space for new hire (-3 days before Hire Date)Task: Give Uniform (on Hire Date)Task: Collect how to contact employee while OOO (1 day after Hire Date) This is my current formula:IF({Task} = "Email for photo, new hire survey, and short bio of new hire", DATEADD({Hire Date}, -7, 'DAYS'))When I add another {task} = ..... , it changes the format of the first deadline from a date to T00:000:00.OOOZ and then the next task's deadline just shows up as "true." When I reopen the formula, it looks like it changes itself from IF({Task} = "Email for photo, new h

When I do a rollup to get the unique documents, it is correctly retrieving just the unique values - but while a lookup brings up the actual attachment documents, the rollup displays Airtable's Internal URL for the document.How do I then retrieve the document?   

I have a few currency fields that I am adding up in a simple formula field. I am then trying to display the output of that formula field within a text string I am building with another formula. However, I’m getting some strange decimals when showing the numbers.In an effort to troubleshoot this, I create a new blank Airtable base for testing and I’m getting the same result. For no apparent reason (to me), when I reference the total field in the string, it will sometimes add a ton of decimal spaces, like this: Here’s a link to my sample/testing base that is showing this issue: https://airtable.com/appqBYuTjL8QbhmF4/shrM4rk6GDRaJQ4QNMy formulas are pretty simple:Total of Amounts 1 & 2: {Amount 1} + {Amount 2}Display Total in a String: "The total of both amounts is $" & {Total of Amounts 1 & 2} & "." I can’t figure out what might be causing this, especially when I manipulate the Amount 1 and Amount 2 values to reach the same sum, the decimals somet

Trying to figure out how to do a search to see if a number falls within 2 fields.For example a field with Start year and a field with End year.  Lets say on one record my start year was 1978 and my end year was 1986.  I would like to do a search for 1983 and have it retrieve that record.  Can someone point me in the correct direction??

Hi, to be honest I’m not sure if it actually is a bug, but this function does not follow the ISO standard of numbering weeks. For example, today (2016-10-13) should be week 41, but the function returns 42. Probably because the first week of this year was week 53 according to the international standards.

Hello!I was recently helped on another post to create parameters for the dateadd function. However, it is not calculating dates correctly. Why? How do I fix this?Here is my function:SWITCH(  Task,  'Email for photo, new hire survey, and short bio of new hire', DATEADD({Hire Date}, -7, 'days'),  'Assign desk space for new hire', DATEADD({Hire Date}, -3, 'days'),  'Give Uniform', {Hire Date},  'Collect how to contact employee while OOO', DATEADD({Hire Date}, 1, 'days')) The complete by dates should be: 12/24, 12/31, 12/28, and 1/1 

Hi,I have a base where I keep my business's time entries for each of the employees.  However, they may be in a different state based on the day, so I'm looking for a way to populate a field for each time entry showing their location.The time entries have a date, a person who performed it (currently as a linked record), and the the number of hours.I currently have a sheet with a separate record for each date and single select field for each employee showing the state that they were in.  Note I can change the organization of this table if needed (for example to entries with a date, a single employee and a location).  Any thoughts on how to best set this up?  I currently have lookup fields for each individual employee in the time entries table, looking up their location on the given date, and then a formula field that uses a switch that selects which lookup field based on the user who performed the time entry.  However, it requires a new lookup field and an u

I need to calculate different due dates for tasks based on Workday instead of dateadd. Here is my original formula: SWITCH(  Task,  'Email for photo, new hire survey, and short bio of new hire', DATEADD({Hire Date}, -7, 'days'),  'Assign desk space for new hire', DATEADD({Hire Date}, -3, 'days'),  'Give Uniform', {Hire Date},  'Collect how to contact employee while OOO', DATEADD({Hire Date}, 1, 'days'),  'Verify that background check has been completed', DATEADD({Hire Date}, -7, 'days'), 'Hiring Manager select starting date', DATEADD({Hire Date}, -21, 'days')) When i change DATEADD to WORKDAY it gives me an error message. What else do I need

I want to filter to the infinity values but am unsure how to do this. I don't want to remove them using an IF statement or the other tricks you can use to have them become zero or empty values. The first screenshot shows the data and that there is one value with Infinity. When I try to use the filter condition to equal the infinity value I am unable to enter the word infinity because it only allows numbers. Next I tried to filter to a value greater then the highest value other then infinity and this filters out everything including the infinity value. How can you filter to the infinity values?    

Hi,I want to create a formula to create a name for a field that consists of "FIELDA" or "FIELDB" If "FIELDA" has no value i would like to show "FIELDB" in the name instead. If "FIELDA" and "FIELDB" both have a value, I would only like to have "FIELDA" filled in the name of that field. If both have no value, no value should be shown. Thanks for helping out! Mark 

I'm stuck. I need a field type and formula to display the cumulative annual profit or loss (P/L) displayed as a percentage (%) and based on a Q1 starting amount and subsequent amounts entered in Q2, Q3, & Q4 fields.This seemed easy until I got started. Now I'm digging a mental hole I can't climb out of :-). Help... thank you.

I have a [Anniversary] field and a field that calculates the next anniversary, so I get reminders and write a card for my volunteers. Here is my problem:The basic formula works fine when there is an anniversary in the field. When there isn't one, I get the #ERROR, which one would expect. I tried ISERROR(...) and IF(ISERROR...) to get it to show blank, but I can't get it to work. All the fields go blank, even if there is a date.Here is the working formula in the AV_Next field:DATETIME_PARSE(DAY(Anniversary) & "-" & MONTH(Anniversary) & "-" &(YEAR(TODAY()) + IF(DATETIME_FORMAT(Anniversary, "MMDD") > DATETIME_FORMAT(TODAY(), "MMDD"),0, 1)),"D-M-YYYY") When I tried this, it worked for showing blanks, but then the date formatting ignored all normalcy. I get this: 2025-07-20T00:00:00.000Z IF(ISERROR(DATETIME_PARSE(DAY(Anniversary) & "-" & MONTH(Anniversary) & "-" &(YEAR(TODAY()) + IF(DATETIME_FORMAT(Anniversary, "MMDD") > DATETIME_FORMAT(T

Hi,I've created a formula to calculate my patients' current age based on their date of birth (Lookup field). When I look at the record in grid view, it gives me an error message, but when I check the formula for accuracy, the calculation preview gives me the correct calculation. I'm not sure why there is an error in the record in grid view if the formula is accurate when I check and edit it. HIPAA-protected info has been redacted in the screenshot below. DATETIME_DIFF(TODAY(), {Child's Date of Birth (from Lookup) 2}, 'years') & ' years, ' & MOD(DATETIME_DIFF(TODAY(), DOB, 'months'), 12) & ' months' Thanks so much!

Hello dear community,I have already searched but found nothing. I want to use a value from one row (for example, row 1) in a formula in row 2. In row 2, the value from row 1 should be used minus the value from row 2 (same field). How does that work?Automation is not the right way, as the dataset is not newly created (trigger), but already exists. Specifically, it is about the following:I want to use the value "km" from the field "refueled at km" in row 1 and calculate it in row 2 in the field "kilometers driven". This value is calculated from the field "refueled at km" in row 1 minus "refueled at km" in row 2. Can someone help me? Thanks in advance.

Hello,Our team has reduced available hours in the summer, and I'm trying to figure how to represent this when using the Utilization summary in Timeline view so that their Available Hours are reduced during a set start and end date. Thank you!