Formulas

Hello,I am looking to create a rollup field to display the next task due for a project.  What I would like to do is have the rollup field display the task name, but the formula should reference something like the MIN({due date}) in order to select which of the many tasks are due next?  Can't seem to make it work and am wondering if it's possible or if I'm going about it wrong?Thanks!

How to Create Formula that Returns the Data in Field 1 (if data exists), or Field 2 (if no data in field 1 and has data in field 2), Same for Sequentially Fields 3-5? I see there are several posts on how to choose a “text” result by writing that text in the nested IF statement. However, I am looking to return the data in field 1 (check this field first and return the data in field 1 if data exists in field 1), and if no data exists in field 1 move on to check field 2 (check this field next and return the data in field 2 if data exists in field 2), and if no data exists in both fields 1 & 2 move on to check field 3 (check this field next and return the data in field 3 if data exists in field 3), and if no data exists in all three of fields 1 & 2 & 3 move on to check field 4 (check this field next and return the data in field 4 if data exists in field 4). There are 5 layers through field 5. Thanks so much in advance for your insight and time! I appreciate it.

I want to display the week like a calendar, but this formula is returning week 1 as starting on a sunday instead of a week starting on a monday and finishing on a sunday, how can i alter this?VALUE(DATETIME_FORMAT(Date,'w'))-VALUE(DATETIME_FORMAT(DATETIME_PARSE('01'&DATETIME_FORMAT(Date,'MM')&YEAR(Date),'DDMMYYYY'),'w'))+1

Hi,I am making what to me seems like a simple change to a simple IF Formula. The prior version of the formula was: IF({Money Out},-ABS({Money Out}),IF({Money In},ABS({Money In})))This is the new formula. IF({Money Out},-ABS({Money Out}),IF({Money In},ABS({Money In})),IF({Debit or Credit},{Debit or Credit})) For some reason, even after I press "SAVE" after updating the formula, when I go out of the field and come back to the field, I see the prior version of the formula. I do NOT get any ERROR messages.  And tried repeatedly to update the formula. I have tried the cache refresh with Com + Shift + R I even closed all AirTable browser windows, and logged out and logged back in to AirTable. No Joy. Any help would be welcome.   

I am sure it is quite simple, but for some reason, I can't find the way to do it.Let us say I have Table A. In the Table A, I have, amongst many others, a Date field.I also have Table B, with two fields in it. One field is a (unique) Date. The other field is just a number. Let us call it [Day Number].[Table A/Date] and [Table B/Date] are not linked.I just want [Table A] to look at its own [Date] field, then search for the corresponding [Date] in [Table B] and retrieve the corresponding [Day Number].Must not be very difficult, but I can't find the way! 

ROUNDUP is returning unexpected results.See screenshots, there are 3 records where ROUNDUP is increasing the value rather than truncating a '0' (I was expecting 0.070 roundup to be 0.07)  Is this an error, or am I not understanding ROUNDUP correctly?2nd screenshot added the FLOOR function as suggested in this pos, but had no impact to the results.: Solved: Re: ROUNDUP(number, 2) Incorrect rounding - Airtable Communitythanks for any insights!

Hi,I am currently getting an error message on this formula: {Contacts Clicked}/{Total Emails Delivered}*100 Does anyone know how I might fix it? Thanks.

I’m hoping someone might be able to help me figure this out… To put it simply I have one column full of images (just one image in each cell) but 1000’s of images total. I need another column, residing next to it that contains the URL for each image, and I haven’t found an easy way to do this aside from just copy and pasting each individual URL. Which gets very repetitive and time consuming doing this thousands of times. Is there a formula out there that could pull these image URL’s and fill each corresponding cell (to the right of the image cell) with the URL (of the image in the cell to the left of it)? Maybe I’ve just been looking at it for too long and there’s a much simpler solution?

Hello all,Brand new to Airtable and like what I see so far. I am trying to calculate a date from two sets of data.  In Excel I would have use Start Date + Duration - 1 to get the End Date.I thought I would use the Datetime_Format, but I am getting an #error!  I wrote DateTime_Format({Start Date1}+{Duration}-1, 'M/DD/YYYY')The Start Date1 is formatted as a Date US (9/5/2023)Duration is a LookUp field pulling from another tab in same base that is formatted as a number.I know this is probably something simple, but any help would be appreciated.  Also, a good recommendation for Airtable Resources. 🙂

Hello,Hope you guys can help me. I have a "Edition" field that contains three options: Extended, Standard and 360 Edition. The concatenate formula works however when adding the IF statement for the 360 Edition I get " Invalid formula." What am I doing wrong here? CONCATENATE({Order ID} & " " & Initials & " " &"Vol II") IF({Edition} = "360 Edition",BLANK()) Thanks 

Hi! I'm looking to create a formula where the "Due Date" is  7 days out from my "Send Date." However, I need a condition where if the "Send Date" falls on a Saturday or Sunday, the "Due Date" is automatically set to a Friday

I need to create a formula that will add 7 days to a manually entered date excluding Sundays and Holidays.--- This is an item that gets mailed, and I need to mail it 7-days before the "due date." Hence, no Sundays or Holidays.Any help or a point in the right direction would be most appreciated!

Hiya, I am fairly new to Airtable and have been tasked with setting up our CRM for the charity (HUGE JOB!) So... we have to get our dogs weighed roughly every month, but I need to be able to compare current weight with previous weight and how much the weight has either gone up or down. What I would like is when I come to enter a new current weight (in the {Current Weight kg} & {CW Date}(which is automatic date), the data that is already in those fields move to the {Previous weight} column and {PW Date} column (currently I am copying and pasting the data from current columns to previous columns before I enter the new current weight which is not ideal!.I then would like to merge my two formula columns {Difference} & {Difference in weight} into one formula so that all it basically shows is the weight difference and either a colorful up arrow or a down arrow emoji.... (oh and have the blank fields remain blank (not saying up!!) Can anyone help me do this?@Ben_Young1 

Dear community,I have two tables, one called Supply and one called Demand, full of engineering information. There could be several Supply records that match the properties of Demand records.I am looking to create a formula in the Demand table that returns the cell 'Specific Material' from the Supply table records, for each Demand record, via IF AND OR conditions.I don't know whether this is possible, and below is my attempt referring to the Supply table via 'Supply.' then the Supply field name. The Demand fields referenced all show as purple, indicating that they are correctly identified, but the Supply fields are all still black.The conditions are all AND except for Supply.X material colour, which could be a match to either Translucent Colour or X Material colour in the Demand table. One of these should match as the last AND condition being met.Could anyone help me solve this via formula? I'd prefer a formula solution to a suggestion to use Lookup fields.I had this working nicely in E

I would like to parse a date string of "2023-02-10", with no time information, into a specific timezone (Europe/Malta). We use daylight savings, so right now, 2023-02-10 00:00 Malta time would be 2023-02-09 22:00 GMT.  For this reason, I cannot specifically add a timezone of +2 because this will chagne to +1 later in October. I noticed DATETIME_PARSE() takes a locale as the 3rd parameter. However, this does not seem to be documented at all in the formula reference. I've tried using values like "Europe/Malta", which works in SET_TIMEZONE(), but doesn't do anything.  I've also experimented with SET_TIMEZONE(), but that seems the other way round, to convert an existing Airtable date with a particular timezone.Basically, what I would like is something where for example could parse "2023-02-10" into the Malta timezone. 

I need some help trying to write a conditional formula...The idea is IF any cell in column 1 is duplicative/match entry, then look at corresponding column 2 cells and merge merge into corresponding column 3.Is if A2 and A45 match look at/merge B2 and B45 into C2 and C45Is something like this possible?

I found this formula on the Airtable Developers Page // change these names to pick a view:let table = base.getTable('Invoices');let view = table.getView('Grid view'); let result = await view.selectRecordsAsync({fields: ['Amount']});let runningTotal = 0;for (let record of result.records) {// change the field names here to adapt this script to your baserunningTotal += record.getCellValue('Amount');await table.updateRecordAsync(record, {'Running total': runningTotal,});}Has anyone used this? It's not working for me.  

I am trying to have different items use different formula code, specifically to modify locks codes that will be different based on date from users and the limitations of each lock. I set up a list of locks and wrote and tested formulas that check for deficiencies in the lock codes that are created from the users phone numbers. An example is if the lock code starts with a "0", the lock with not accept the code. So there is code that looks for a "0" and replaces it with a "1". So if the users code was "0234" the code will change it to.. "1234". IF({Code},IF(LEFT({Code},1)=0,CONCATENATE(1,RIGHT({Code},3)),{Code}),ROUND((VALUE(DATETIME_FORMAT(CREATED_TIME(), '0.smh'))*(9999-1001))+1001,0))Also if for some reason there is no code present, a random code is generated.This code has been tested and lives in a text column in another table, my hope was to use a look up to use this formula code when it applies.  I have the lock code associate with a given rental property, the reservation

I have this IF Statement Formula:   IF(  {UNH Facilities}= "UNH Facilities - GH Cleared",    "🟣",     IF({UNH Facilities}= "UNH Facilities - Supplying Items",      "🟠") ) but I want to add one more piece that returns both an orange and purple circle if {UNH Facilities} has both ""UNH Facilities - GH Cleared" and "UNH Facilities - Supplying Items". The field it is pulling from is multiple select, don't know if that matters.

Work with print media plans and I have an Excel workbook I've used for years to calculate costs. There are many variables with cost calculations and I will stay with this workbook however I'd like to export the final costs for each publication at each page size for import to Airtable to automatically calculate the costs based on each buy. Most buys consist of two components. A full page ad and a small page that accompanies the ad for legalese. Attached is screen capture of my media planning table and costs table with how I am trying to map costs. Is what I'm trying to achieve possible and/or is there a better way? I tried searching for a solution first and could not locate a similar request so if you know of link to send me to that would be great. Thank you in advance. 

Hi All - I'm struggling with creating calculations on the base shown below. What I'm trying to do is calculate the % change in total enrollment growth by 1YR, 5YR and 10YR between Fall Years for each university (it would be a changing calculation with each new Fall Year record added for each university).While this is easy to do with a vlookup in Excel (i.e. matching University, Fall Year and Total Enrollment with Fall Year-1, Fall Year-5, Fall Year-10), I'm struggling with a finding solution here in Airtable.I'm feeling like it might require a linked Lookup table (or adding another field) to make it work. But just not sure how to go about it. Or, if it's even possible between records in Airtable. The numbers shown below for the % growth fields were imported from Excel. Any help would be greatly appreciated!Thanks! 

It seems like it should be a simple solution, but my brain is tired! I am using a rollup field to show the total discount so I can highlight it on a quote which I have made with page designer. If there is no discount ($0.00) I don't want it to show on the quote. So I am trying to figure out how to leave it blank when there is result is $0. I tried adding another field with a formula, but then I can't get a positive number to be in currency format, just a string. I tried doing an IF Aggregation formula but kept getting an error. I made sure that the source field (Amt Saved) does not return anything if the result is 0 hoping that would follow through to the rollup, but no luck. Can someone please help? 

I am attempting to make a Travel Sheet my Supervisors access through a Form view, that allows the selection of Employees (linked from our Employee Table) based on their current Employment Status (which is a dropdown selection on the other table). When I select the "Only include linked records from the Employees table that meet certain conditions" and have it to where: Employment - is - "Current Employee", however when the form is being previewed, the selection is pulling all employees, regardless if they're past or current. I have also tried the same conditioning filtering for Projects as well based on their "Status" as well, and it also doesn't work on filtering only what I want it to show. What am I doing incorrectly, as this seems like it should be a very basic and simple conditional request? Thank you for your time.

I have written this formula a few different ways and in each case, I get the result but am unable to format as currency. Error: Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.Here the two most successful attemps:IF(AND(OR({Channel} = "Print",{Channel} = "Digital"),{New | Repeat} = "New"),IF({Channel} = "Print","$" & VALUE("450"),IF({Channel} = "Digital","$" & VALUE("75"),"")),"")IF(AND(OR({Channel} = "Print",{Channel} = "Digital"),{New | Repeat} = "New"),IF({Channel} = "Print",450,IF({Channel} = "Digital",75,"")),"")Any insight is greatly appreciated!!!

I need a simple formula that will find a specific date AirTable Date Field is formated as ‘2021-06-10’ or ‘YYYY-MM-DD’ I can’t seem to get the right formula to find a specific date IF(Date = 2021-06-10, TRUE()) IF(Date = '2021-06-10', TRUE()) Neither one works. Please help. Edit: I think I found the solution IF(Date = DATETIME_PARSE('2021-06-10'), TRUE())