Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,940 Topics
  • 18,230 Replies
5940 Topics
R
Rachel_EricksonParticipating Frequently
asked in Formulas
Using MAX to get latest date among 3 date columns

Hi folks, I am fairly new to the formula game and I’m working on a formula where I have 3 columns with dates {LOI Target}, {ETA to AMZ}, {Content Delivery Date} and i’ll need to calculate the latest date out of the three of these Add 7 days to the latest date Calculate to the next Tuesday input date correctly into column It’s a bit of a monster. Any help even in little pieces would be great. I’ve been wandering the forums looking for help and this one was the closest formula to one of my needs (latest date out of the 3 dates). So, is the following formula on the right track for what I want? I know that the MAX function doesn’t really bode well with dates but that’s about all I know. IF( OR( {LOI Target}, {ETA TO AMZ}, {Content Delivery Date} ), DATETIME_PARSE( MAX( IF({LOI Target}, VALUE(DATETIME_FORMAT({LOI Target},’MM/DD/YYYY’)), 999999999999999), IF({ETA to AMZ}, VALUE(DATETIME_FORMAT({ETA to AMZ},’MM/DD/YYYY’)), 999999999999999) ) , IF({Content Delivery Date}, VALUE(DATETIME_FORM

841
kuovonne
J
Jake_BarnetNew Participant
posted in Formulas
Applying DATEADD to an array of Dates?

I built a data set to automatically apply a task due date relative to a main project release date, but I’m now trying to apply that function to multiple projects that feed in from a lookup field. I was able to get it to stop ERROR messaging with ARRAYJOIN, but it’s only applying the DATEADD to the first date in the string, rather than applying it to both dates and outputting a string. Alternatively, I’d love to output this into multiple records to be used like a to do list, but if I can at least get it to apply to multiple dates at once, I’ll be satisfied. Essentially, we have a ton of projects with similar timelines that fall on top of one another and I want to use this to manage the steps across multiple projects at once. Here’s my formula right now. Adding a 2nd ARRAYJOIN neither fixed or broke my formula so I’m not sure where to go from here. Thanks! ARRAYJOIN(DATETIME_FORMAT(DATEADD(ARRAYJOIN({Release Date (from Master)}, ", "), {Less Weeks}, ‘week’), ‘M/DD/YYYY’), ", ")

131
kuovonne
E
Erik_AndersonNew Participant
posted in Formulas
Data Matching and Referencing between sheets

Hi, I am creating a table which lists our shipments of containers. I need a column of data to pull data from another sheet, if a 3rd column’s contents match the data on the other sheet. For example. On sheet 1, in Column 1, I enter a serial number. In column 2 of sheet on is a Lot Number. Now on sheet 2, I have a table as well as a form view. On the Form view, a person on our ops team enters a serial number and creates a new record. Once that record is created, I want column 2 of sheet to automatically: a) check to see if the serial number entered in the form (which created said record) is listed in column 1 of sheet 1; and b) if it is listed there, to populate column 2 of sheet 2 with the relevant data in column 2 of sheet one. it’s basically a lookup function but is reliant on several different data points. I have tried the link function within Airtable but it doesn’t seem to be able to handle this. If it can, it’s not at all straightforward. PLEASE HELP Regards Erik

1118
A
D
Dennis_PetrouKnown Participant
posted in Formulas
Nested IF/AND with multiple columns

Hey everyone! Thanks so much for all the help you’ve given, I’m still learning and this community has made it a great experience! I think what I’m trying to do can be solved with nested IF statements, just can’t seem to find the definitive answer looking through old posts. Here’s what I want to do. I want to calculate the number of hours it takes to do a certain job for a certain employee, where each employee takes a different amount of time for that job. For example, Employee 1 takes 4 hours to do Task 1, but Employee 2 takes 3 hours to do the same task. There are a bunch of employees and a bunch different tasks, and the hours are different for each employee. Is there a not so messy way to do this? Thanks so much for the support! EDIT: this is the formula that I have that works for one Employee and one task IF( AND( {Employee} = “Employee 1”, TASK = “TASK 1” ), “4” ) Where it will show 4 hours for Employee 1 to do task 1. Where I get confused is how to nest a whole lot of these to c

84
D
A
AICInspiring
asked in Formulas
IF(FIND) formula not working?

Hi, I’m using the following formula in the column “is absent today”, but for some reason it isn’t correctly finding August 16th. I suspect this is because the linked “Absent” field is an array, which is why I included the &"" in my formula, but that didn’t fix it… IF(FIND({Today's Date},{Absent} & ""), 'Y', 'N')

192
A
R
reid_sweetNew Participant
posted in Formulas
The result of the calculation looks like a number but creates an error if you use it in subsequent formulas

IF({Pricing (How Sold?)}=“By SqFt”,{Takeoff SqFt From Installation}+{Overage Amt},"") Pricing (How Sold?) Is a single select field. Takeoff SqFt is a # field looked up from a different table Overage Amt is a formula that multiplies the Takeoff SqFt by a % to get a number. I get the following when I try to format the output: “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.” The result of the calculation looks like a number but creates an error if you use it in subsequent formulas. Any help would be appreciated

23
R
M
Markus_WernigInspiring
posted in Formulas
How to delete part of a text after and including a hyphen?

If someone could help me with this, I’d be extremely grateful. I can’t figure this out. This is what I"d like to achieve through a formula: Most Track Titles have a name followed by a “space hyphen space” and a suffix. I’d like to automatically delete everything after and including the “space hyphen space”. If Track Title is not followed by “space hyphen space”, just copy the Track Title over. Thank you so much for pointing me in the right direction.

665
M
L
LoletteNew Participant
posted in Formulas
Parent / Child Column Lists

I am using Airtable to connect several different contact lists by locations. The primary column is based on a 3-digit country code. This same column is used to connect all the data from each list. I need to build different grids based on city, region, etc. How can I setup different column/parents with several children? For example, ZAF is the country code for South Africa. Say Parent3 = Cape Town and Child1, Child2, Child3 are different areas in Cape Town. I need the LIST of children to auto-generate once I enter the parent in the previous field. Logic example: select continent (or enter) “Africa” > lists of country displays, select “South Africa” > new list displays, select “Cape Town” > new lists of displays, and I can select the final destination of “Cape Floral Region”. How can I use this defining area to connect the grid sheets by locations. For example, if I have different category lists (schools, restaurants, properties, etc) and each list is a mixture of count

20
L
J
Jodie_KolbeekNew Participant
posted in Formulas
Using last_modified_time in an IF statement

Hello, I am looking for some help. I’m pretty new to Airtable, so I don’t know if what I am looking for is possible. However, I want to create a formula with the below logic. IF value in column A IS "YES" > 30 minutes THEN "Needs action". I then want to create an automation that looks at the value in Column B, and if it has the value “Needs action”, it would trigger the automation. I currently have: IF(AND(LAST_MODIFIED_TIME(MINUTE(Column A)= > 30)), "Needs Action") Is such a formula possible in Airtable?

618
J
A
AICInspiring
asked in Formulas
Display a formula output that is date-dependent

Hi, I’m trying to set up a sheet to manage attendance for a preschool center. Kids have a different “weight” depending on their age, which I successfully set up. The weighting is used to determine how many kids we can have at once. What I now need to do is show a daily breakdown of those weights, because some children don’t attend preschool every day. I need a calendar where I can look at any given day and see how many total kids will be there. I’m trying to combine the weight formula with START and END date fields, but can’t make it work… Any advice is much appreciated!

612
A
Ian_Muyrong
Ian_MuyrongParticipating Frequently
posted in Formulas
Prefill form with multiple linked records

Hi! Would anyone know how to prefill a form using multiple linked record field? The prefill form works with a single linked record but does not work when multiple linked records are selected :frowning:

331
kuovonne
L
Lassi_SeppaInspiring
posted in Formulas
How to return a empty date in formula?

I’m using formula to create a date field, which is used in Calendar view as a calendar’s source. My problem is to create empty date, if certain criteria is occured. I try to use BLANK(), but this returns it in non-date format. Here is the formula: SWITCH(UPPER({ :bellhop_bell: Agenda Status}), “PENDING”, IF({Scene Setup date}, {Scene Setup date}, {Due Date}), “SETUP”, IF({Scene Draft date}, {Scene Draft date}, {Due Date}), “SKETCH”, IF({Scene Draft date}, {Scene Finalize date}, {Due Date}), “DRAFT FOR REHEARSAL”, IF({Scene Finalize date}, {Scene Finalize date}, {Due Date}), “FINAL FOR SHOW”, IF({Scene Show date}, {Scene Show date}, {Due Date}), “CANCELLED”, BLANK(), “COMPLETE”, BLANK(), “REISSUE”, TODAY(), IF({Due Date}, {Due Date}, TODAY()) ) Note, formula works, but this does not allow to set field formatting as date. Is there a way return empty date in formula, so that field is in date format and not string ?

111
Databaser
J
Jose_Alberto_FuKnown Participant
posted in Formulas
I need to calculate the percent of a group over the total

Hi guys!! I need to summarize the percent of a group over the total as shown in red in the example. Any suggestion? Thanks

1062
J
J
J_ENew Participant
asked in Formulas
How to use IF statements with logical operators for inventory

Hello, First challenge…I am trying to show when a number is input to the RECEIVED column, that it subtracts itself from the ORDERED column and puts the result in the BACKORDERED column (however if the RECEIVED column is blank, I need the BACKORDERED column to be ‘0’ or blank as well). Second challenge…If the RECEIVED number equals the ORDERED column then the STATUS column should show ‘complete’. If the RECEIVED number is less than the ORDERED column, the STATUS column should show ‘in progress’. If I was to say this in one sentence it would be “If received is not 0 or blank, subtract it from ordered and show result in backordered AND if received equals ordered change status to complete OR if received is less than ordered change status to in progress” Any help would be much appreciated! :grinning:

51
Justin_Barrett
Tiaan_Terblanch
Tiaan_TerblanchInspiring
asked in Formulas
Table and field formules

Hi All - I would appreciate any support In one base, I have two tables (Master and Prices). I have a list of products on the master table with a field (column) named costs, and in Prices, I have Product and new Price fields. I need assistance (not sure if this is possible) with a formula that will help me update the costs in the Master table if the product in the Master table is also available in the Prices table. Example: In this example, the Master table will only update products A and C’s Cost fields because it has not picked up a product on the Price table. This means that in Table 1, product A will change automatically to 150 and C to 250. Nothing will happen to B because there is not a unit for it in Table 2. Any help will be much appreciated. Thanks

112
Tiaan_Terblanch
W
WindNew Participant
asked in Formulas
Date difference in Months and Days

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, IF(MOD(YEAR({start Date}), 400) = 0, 29, IF(MOD(YEAR({start Date}), 100) = 0, 28, IF(MOD(YEAR({start Date}), 4) = 0, 29, 28 ) ) ), 3, 31, 4, 30, 5, 31, 6, 30, 7, 31, 8, 31, 9, 30, 10, 31, 11, 30, 12, 31) Is there a way to calculate the

344
W
G
Gillian_DowneyNew Participant
posted in Formulas
Subtracting items sold from sales table to inventory available table

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…

75
G
M
Mark_LittlerNew Participant
posted in Formulas
Ceiling Function With A Significance Above 10

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.

43
M
David_Anderson1
David_Anderson1Participating Frequently
posted in Formulas
Automatically +1 to a record

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.

101
Databaser
F
Federico_TriulzNew Participant
posted in Formulas
Help with IF formula choosing between three options

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

111
A
A
arjun_singhNew Participant
posted in Formulas
Is there a formula/automation for intersection of two arrays?

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!

598
A
A
automadienInspiring
asked in Formulas
Reminder formula for 30 days time

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

132
A
R
Rachel_EricksonParticipating Frequently
posted in Formulas
DATETIME_FORMAT not formatting when used with existing formula

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? How can I add the format formula to my existing formula to populate

85
R
M
Marc_ChenutParticipating Frequently
posted in Formulas
IF, AND, OR nested

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

95
M
C
Christopher_CanKnown Participant
asked in Formulas
Formatting as Number in Formula When Number is Manually Entered

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.

112
C

Badge winners

Show all badges
Terms & Conditions