Formulas

I had searched through the community and seen a post stating about the date difference of a period showing data like 2 months and 31 days or 3 months 30 days. It is worse if it involves February as it will be calculated as 1 month 30 days. Example as below. I’m making all the months defaulted to have 30 days, as per below formula. DATETIME_DIFF(end,start,‘months’ ) & ’ M ’ & ( DATETIME_DIFF(end,start,‘days’ ) -DATETIME_DIFF(end,start,‘months’ ) *30 ) & ‘D’ I would like to know if there is any way to make the difference to be more accurate like those in Excel? I saw that there is a post regarding Switch() formula but I have no idea how to include that into this formula. The Switch() formula is as below. SWITCH(MONTH({start Date}), 1, 31, 2,

I am trying to make a simple sales table that will communicate with my inventory table to subtract what is sold and show me how much inventory I have left. I don’t understand most of the lingo…

Hey everyone! I’m new here and have searched the forum and google for this problem with no success. I saw the thread about rounding by 0.25, but I am seeing a different issue. It appears that the “significance” value must be 10 or less. If I enter CEILING(4,6) the output is 6 as I expected. If I enter CEILING(12,12) the output is 10, but I believe the correct answer here is 12. This happens any time the second number is over 10 then the result is always only a multiple of 10 instead of the number you entered. If I enter CEILING(27,12) the output is 40??? Is this a known issue? Thanks for your time.

Hi all, I have an AT base with numerous tables, one of which is a “Document Master Record”, with a linked table for “Document Revisions”. At the moment, the revision number is manually entered e.g. 7 for the 7th revision. Ideally, I’d like that to automatically increase as a new record is added (i.e. it is automatically creating Revision 8, the next time a new revision recird is added under the Master Record), but needs to be in sequence for the master record, so {autonumber} isn’t (as far as I am aware anyway) an option. I had a look at automation too to see if +1 could be added each time a record is created, but doesn’t seem to be an option either. Anyone got any ideas? Thanks in advance.

Hey guys, so I have a test which contains a series of questions. Each question has a single select field with 3 answers. Each of these answer has a different value (for example: answer X = 9 pts; answer Y = 3 pts; answer Z = 0 pts). I need to create a field where to insert a formula that generates the exact amount of points with every answer. Something like this: “question 1 > IF answer = X > 9 pts; IF answer = Y > 3 pts; IF answer = Z > 0 pts” Your help would be greatly appreciated, thanks a lot

My use case is that two people share availabilities. When there is a common availability, a calendar invite goes to both people on that common time. Thanks!

Hi there! I’m looking to create a formula that will set a trigger for a reminder in 30 days time from an activity, when it will display “Trigger Reminder” after 30 days have passed from that live date field. I thought this formula would work: IF(AND(DATETIME_DIFF(TODAY(), {Live Date}, ‘days’ > 30)), “Trigger Reminder”) But that just comes up with Trigger Reminder for pretty much everything in my base. Any help welcomed! Thanks! J

Hi folks, I cannot figure out why this formula is not outputting what I need. My current formula for the column is this: IF({ETA (AMZ) Date}="",“Need ETA (AMZ) Date”,DATEADD({ETA (AMZ) Date}, (7 - (WEEKDAY({ETA (AMZ) Date}) - 2)),‘days’)) If there is a date in the ETA AMZ DATE column, mark as Need ETA (AMZ) Date. If there is a date, create new date + 7 days from existing date and to the nearest Tuesday. When I use this formula, the output looks like this: 2021-01-26T00:00:00+00:00 SO, I’ve tried DATETIME_FORMAT IF({ETA (AMZ) Date}="",“Need ETA (AMZ) Date”,DATETIME_FORMAT(DATEADD({ETA (AMZ) Date}, (7 - (WEEKDAY({ETA (AMZ) Date}) - 2)),‘days’)),‘MM/DD/YYYY’) When I save the formula, it automatically removes the ‘MM/DD/YYYY’ leaving me with IF({ETA (AMZ) Date}="",“Need ETA (AMZ) Date”,DATETIME_FORMAT(DATEADD({ETA (AMZ) Date}, (7 - (WEEKDAY({ETA (AMZ) Date}) - 2)),‘days’))) This doesn’t solve my problem. What am I missing? H

Hi, I have a price table with 12 rows per activity that refers to the number of people. I have 4 different column “With transport / without transport” “Transport price for 2 to 4 people” “Transport price for 5 to 6 people” “Transport price for 7 to 12 people”. In “Transport price”, I am looking to get the right transport price depending IF it’s with transport. Then depending of the number of people, I want to grab the right column/price. Could you help please :slightly_smiling_face: I really can’t find the right formula. Many thanks

I have the following formula that spits out a number of days since the last event for an entry and if no event is found, it returns 9999 instead: IF({Days Since}=0,‘9999’,DATETIME_DIFF(TODAY(),{Days Since},‘days’)) My issue is that the formula field doesn’t recognize this result as a number and won’t let me format and filter as such. Is there any way I can return a result that allows formatting as a number? I tried using BLANK() in place of 9999, but the issue is that I need to filter results that return a number greater than say 90 or 180. So blank results get filtered out when I would want them to be filtered in. And this is part of a longer filter so I can’t use an OR statement in the filter.

I’ve got a rollup field tied to connected records with dates, and I’m not 100% sure how it works. My goal is to have this rollup field only show the most recent date. So if it is rolling up from two entries, lets say: 1/1/2020, and 10/10/2020, I only want to see 10/10/2020. I’m assuming I use the MAX() attribute for that…but I’m not sure. Can anyone confirm what the appropriate aggregation formula is to display the most recent date of several in a rollup field?

I’m working on a character generator for Dungeons and Dragons and I’m having trouble building a formula to get all the information in one place. This is the view on the form I created: My goal is to create a formula that get rid of the numbers and just displays what Race the player selected (elf, dragonborn, etc.) for easier readability in grid view. When I write the formula to only refer to a single number it works fine. But when I try and have the formula substitute multiple numbers with a blank space it returns an error message. I was using the example formula on the side as a reference which uses => instead of a comma to sepa

Hi I’m trying to find a way to display the email addresses of the collaborators listed in another field. but I can’t seem to find any way to display this. I’m syncing a Client’s table to my teams table, and the client has a collaborators field, they are fine adding in an additional helper field for us but just can’t figure out what this needs to be. Thank you for any help with is.

I am looking to filter properties for owners that live out of state. I get information from my data source in the format below. What’s a Regex formula I can use to separate out the City, State, and 5digit ZIP?

I have a look up field that contains a list of companies’ names. I want to get the most mentioned company in that list. More details: I have three tables: Tenders Entities Companies Tenders table has a linked record from both entities and companies. From the entities table I created a look up table that retrieves the companies column. I’d like to get the name of the company that was mentioned the most. The idea is to answer the following question: Which company gets the most tenders of each entity? regards, Motlaq

I can’t figure out how to summarize a count of unique records across a grouping, either with a rollup or formula or even with a pivot table. I’ve created a sample base here with creator permissions. On the Interests tab, you’ll see the Categories I’m looking to count unique People across. Each of the groups should have 4 unique People. How do get that to show up somewhere? Thanks!

Hi folks, I’m trying to find a numerical value from the “Title” field and have it appear in the “amount” (e.g. 27.21) I’ve used this formula from looking at a few post’s but it only copies the values before the decimal: MID( {Title}, FIND( “For”, {Title} ) 6, 50 ) Another formula I found only copies the values before the decimal: IF({Title}, VALUE(RIGHT({Title}, LEN({Title})- FIND(“0”,“1”,“2”,“3”,“4”,“5”,“6”,“7”,“8”,“9”, {Title}))), 0) What formula do I need to get the full total which in this example is 27.21? Thanks in advance! :winking_face:

Hi, I would like to return all rows that contain a specific term. For example, I have rows that have two terms, [Default, Bar]. Using AND(term='Bar') will not work, but using FIND('Bar',term) will work. Except that it will also return results that have the term “Barber” as well. Is there a way to select rows that contain a term? Like FIND([Bar], term) for example? Thanks

Hello! I am working on writing a formula, and running into a few issues. To preface, I am tracking employee trainings in this database. We have 3 trainings that expire. 2 expire after 2 years and 1 after 4 years. I am trying to write an if/then statement where if the training name matches one of those 3 and it’s within a specific time frame, it will output “Overdue”, or “Up to Date”. Logical statements that I need: If the training name = “First Aid” or “CPI” AND the Days field is greater than 730 days, the training is overdue. If the training name = “Notary Public” And the Days field is greater than 1460, the training is overdue. I’ve been working on it for a bit, and have a first attempt at the logical statement, but it’s not working. Any ideas would be beyond helpful! IF( AND( {Trainings} = “First Aid”, {Trainings} = “CPI” )& IF( {Days} > 730, “Overdue”, “Up to date” )

Hello! I am trying to make a formula to calculate X number of days before a Launch Date. I do not want to exclude weekends, however if the Due Date happens to fall on a weekend, I want to make it that Friday instead. I know about the DATEADD({Launch Date},-7,‘days) formula, and I’ve seen the WORKDAY function but not exactly sure how to make this work. So when calculating the total days before launch I DO want to include any weekends, but if the Due date falls on a weekend, it should add an extra day or two to make it that Friday… Thanks!

Hi, I need to calculate the age of a Deceased. I assume a formula is needed. I have Date of Birth and Date of Death to work with and I need the know the age in Years, Months and Days. Any help would be greatly appreciated.

I obviously don’t know what I’m talking about, so apologies in advance. I was inspired by this Team Schedule using Airtable blog post where they have a formula column that delivers “team member + Status” so I’m trying to create a formula that achieves something similar, but is a little more complex: My columns are: Brand = lookup from another table example “Client 1” Project = lookup from another table example "Awesome Website" Person = lookup from another table example “Betsy” Status = single select example “Working” or “OOO” My goal is to get the formula to deliver [Shortened {Brand} name | {project}] - Status] or {person} - Status where the result EITHER shows: shortened brand name + project + status (example C1 | Awesome Website - Working) OR person + status (example

Hello all, I would like to use an If formula: Everything before a certain date should be “True”, everything after “False”. When I write it like this, it doesn’t work: IF(InvoiceDate<3/8/2021,1,2) (InvoiceDate is a Field in Type “Date” with Format “European”). Do you have any idea and could you help me please? Thank you very much!

Hi there! I’m using an API to retrieve the balance of crypto wallets. The issue is that the balances are returned in Hex decimal format and there isn’t a native function to convert from hex decimal to regular decimal in Airtable. The manual conversion is rather complex and not ideal at all. Wondering if there are any workarounds? Thanks in advance!

Hi guys, Recently, I ran into some weird cases while trying to copy-paste a formula field cell values. When I’m pasting the content, I’m getting the string with quotation marks at the beginning and the ending of the text, while in the field itself I’m not seeing them. Example: You can see there are no quotation marks in the cell itself, but when trying to paste the cell value, I’m getting: “Text text text” Any idea on how to avoid these quotation marks?