Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,942 Topics
  • 18,243 Replies
5942 Topics
C
ccreamer11New Participant
posted in Formulas
Help with recurring dates in formula

Greetings! I am pretty new to Airtable, so forgive me if this is a silly question: For reference, I am maintaining a book of business for an insurance office. This office manages multiple lines of business (home, auto, life, business, etc... which are a single select field in my table).  I am trying to create a column that displays the "next renewal", based on the field "inception date".  Here is what I have so far:IF(  DATETIME_FORMAT({Inception Date}, "MMDD") > DATETIME_FORMAT(TODAY(), "MMDD"),  DATETIME_PARSE(    DAY({Inception Date}) & "-" &     MONTH({Inception Date}) & "-" &    YEAR(TODAY()),    "D-M-YYYY"  ),  DATETIME_PARSE(    DAY({Inception Date}) & "-" &     MONTH({Inception Date})&n

154
B
C
cmhollisNew Participant
posted in Formulas
Sum by Group Value

Hello,I'm trying to create a schedule table to track team members availability each month using the total # of budgeted hours by month.E.g. for the month of May  If Sum of {Budgeted Hours} > 160 = "Overbooked" , = 160 "Fully Booked",  < 160 "Underbooked". I would then use these values to determine the shading on the Gantt chart  

171
TheTimeSavingCo
M
mmr1443Inspiring
asked in Formulas
Two "parallel" IF statements

I am trying to create a field that will say "❌ Incomplete Info" with a conditional statement.We two columns for custom products (price and description). If both columns are empty, info is complete. Record has no associated custom products.If both columns are full, info is complete. Record with custom product has description and a price.If ONE column is empty, and the other is NOT, we have incomplete info. If there is a price, but no description, or there is a description but no price, then that is when I want the "INCOMPLETE INFO" to appear. I can't seem to figure it out. I've successfully nested, but it will only ever apply to some of the scenarios provided.Thanks in advance!

114
TheTimeSavingCo
A
aleajaInspiring
asked in Formulas
Parsing time string with time zone

I have a table that includes a date field as well as a single select field with options that are times of day (e.g., 9:00am, 12:00pm, 3:00pm). This allows users to select a date and time from a limited set of time options rather than Airtable's unrestricted datetime selector.I'd like to compose these fields into a single, calculated datetime field. This composition is a fairly elementary task, but the formula keeps parsing the time string in UTC. How can I get Airtable to read the time string based on another time zone (i.e., the time zone my team works in)?Here's the formula I've written so far. I've tried using `SET_TIMEZONE()` within the various `DATETIME_FORMAT()` functions, but can't quite get the formula output right: IF(NOT({Time option}), {Date}, DATETIME_PARSE( DATETIME_FORMAT( {Date}, "YYYY-MM-DD" )&" " &DATETIME_FORMAT( SET_TIMEZONE( DATETIME_PARSE( DATETIME_FORMAT( NOW(), "YYYY-MM-DD"

343
TheTimeSavingCo
R
Russell_FindlayInspiring
asked in Formulas
Prefill forms in email automations - Loses the _ and so the prefilling

Hi I have a prefill for, formula in my base which ends with ?prefill_STWorkshopReference=PA007130&hide_STWorkshopReference=trueand it works fine when clicked in the base But when included in an automated airtable email the _ and _ are omitted and the text in between is italicised - presumably due to markdown languageIs there a way of solving this ?  

41
TheTimeSavingCo
Y
YviaParticipating Frequently
asked in Formulas
Adding Datetime Format

I have three columns: 1st is the Sales Last Month, 2nd is Sales This Month and 3rd one shows the ratio of the two columns. I want to add the DateTime format so Sales Field will look - Sales: 137/150 (Apr: 201/200) instead of Sales: 137/150 (201/200).   

54
Y
O
Operations11New Participant
posted in Formulas
Get a list of records with filter based on a linked record

So I have two tables, Project Reviews and Students. The Project reviews tables has a column that is linked to the student that submitted the review. Now, I am trying to retrieve records submitted by a particular student and I kept getting an empty array. Here is what I tried: .all(filter: '{Student} = "#{[student_id]}"') I am using the Airtable Ruby Gem on a rails app. Any help is appreciated. Thanks.

21
V
J
Jesper_HolmstroKnown Participant
asked in Formulas
Rank field of dates

Hey GuysI have a dynamic datefield (updates thru automations) that begins from todays date and records 10 days ahead. Is there a way to write a formulafield that ranks the date with numbers 1-10, i.e so todays date is 1 and tomorrow 2 and so on? I could use that info for navigation in an app.Thank you in advance.Jesper

134
J
E
enhompeNew Participant
posted in Formulas
Exact Match in search results

Hi, I'm wondering how to set up a search that returns only an exact match. Can anyone help? Thank you.

61
TheTimeSavingCo
J
jakerockwellNew Participant
posted in Formulas
Open link in a button in the same tab

Currently, when I click a link in an Airtable 'button', it opens that link in a new tab. I do not want to open the link in a new tab.I want to open the link in the same tab (which will navigate away from Airtable).Any idea on how to accomplish this?

402
L
Sean_Lake1
Sean_Lake1Inspiring
posted in Formulas
Updating formula - Nested IF statements

Hey all!I've got a long formula, that currently has just one IF statement, however, I need to update to add another alternate IF.Here is the existing formula, that works:IF({Course Status}='Retired',"RET-"&CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name},CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name})&" "&{Engine Version}Here is what I was attempting to add:IF({Course Status}='Retired',"RET-"&CONCATENATE(LEFT({Course Level},1),IF({Course Status}='Archived(DO NOT DELETE)', “ARC-”&CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name},CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name})&" "&{Engine Version}Any ideas how to fix?Thanks!

20
Sean_Lake1
D
dangelneiraNew Participant
posted in Formulas
How can I have the separation (enter/space) in aritable, but do not show the character "\\R" at sofr

I am using Softr and Airtable.Create a formula. If the field meets a criterion I require it to display a text "x", otherwise it must display a text "y".But the text has a separation (enter / space) between two paragraphs. When copying the text it has the separation (enter / space). But when displaying the information in Softr I am getting the next character "R" to create the hyphenation (enter / space).How can I have the separation (enter / space) in aritable, but do not show the character "\R" at softr?I appreciate your help.Translated with DeepL

10
D
A
Andy_Lin1Inspiring
posted in Formulas
Circular Reference Error in Primary Field

I'm sharing a solution to a problem I had; hopefully this will help others. Note: you will need to be on a plan that includes Automations.When you try to reference information from a linked field in a primary field, Airtable will warn you of a circular reference, even if no dependencies will be affected by the contents of the primary field.For example: I work with cars, and I have two tables (among others) in my base: Vehicle Generations and Vehicle Years.In Vehicle Generations, I'll have a record with the primary field "Jeep Wrangler, 2007–2018"; in Vehicle Years, I'll have a record with a primary field "2007 Jeep Wrangler", or "2008 Jeep Wrangler", and so on. And each of these Vehicle Years records is linked to a Vehicle Generations record.Now, if I want to dynamically generate the primary field for Vehicle Generations (e.g. update the end year as new model years are added), I could use a formula field like{Make & Model} & ", " & {Start Year} & "–" & {End Year}, w

260
A
K
kschnaenbergInspiring
asked in Formulas
Snaptshot Roll-Ups At a Specific Date

Have a base that looks at deal flow, including anticipated close date and value of each deal.  While we can easily view our "pipeline" at any time, struggling to figure out a way to store that snapshot of data on a routine basis.  We don't store updates to pipeline as a record itself, but update the record to the anticipated value.  At the end of a specific month in the past, what did our pipeline look like in terms of deal value, grouped by when we expect it to close?  Used to look at accuracy (anticipated vs actual) of our project pipeline projections.   

247
TheTimeSavingCo
J
JDC_361New Participant
posted in Formulas
Sum up COUNTIF across multiple tabs in a table.

Hi, I have this table in which each tab has a column with a checkmark ('Interested Lead'). I would like to sum up the total number of checkmarks in the 'Interested Lead" column across all the tabs in this table. 1). What should I use? a function? a roll-up? or a look up table? 2). When I try a look-up table, it only gives me the option of selecting one tab {PMf Sales} but not the other tabs.  Thanks in advance!  

648
TheTimeSavingCo
H
Hal_AtkinsNew Participant
posted in Formulas
Formula to show if a date is within the past year

Good afternoon!I'd like to create a field that would simply show if a date within the record is within the past year. We already have a field "Date Received" for each record. I'd like to have a field that can quickly show if that date is within the past 365 days. I'm guessing the best way would be a formula field that would display either a "Yes/No" (or a check or something) if the "Date Received" value is on or before one year from the current day.Anyone able to help craft that formula? Or is there a better way to do this?

691
B
A
AnnieTacticombaParticipating Frequently
posted in Formulas
How to multiply and add numbers in a cell

Hi Airtable Community,Looking for some advice. I have columns where I have cut length of webbings in a cell.  I have noted the placement of the webbing, cut length and quantity in the cell.  I wanted to have a column that can take the numerical data and divide by 36" (i.e. [(9.75 x 1)+(17x2)]/36)), to give me usage of the material.  Is that possible without having to manually split each cut length into multiple columns?  I do need the text description of the location (i.e. Haul loop) or else I cannot identify what the cut lengths are for, if I need a column for each description of cut length then I will have a lot of columns which I'd like to avoid.  Any suggestions would be appreciated!Thanks,Annie 

183
A
H
HarrryNew Participant
asked in Formulas
Date Toggle Automation

Hey all!Currently, I have a client database with start date {start} and finish date {finish} fields.Once I have added a finish date, I then manually update the {status} field from 'In Progress' to 'Done' - it is a single-select field).However, I'd like to automate the above process, meaning I'd like to automate the following:If {finish} is not empty (meaning a date has been selected and the project has been completed), change the {Status} field from 'In Progress' to 'Done'I've attempted to do this but the automation comes back as an error.I look forward to hearing any ideas!

84
T
L
LeighAnnMoltzKnown Participant
posted in Formulas
Rollup concatenate formula help

I have a rollup formula that returns a bullet pointed listed, which works beautifully (supplied by this incredible community - thanks!). I need to concatenate that with additional information though and am at a total loss.  This is my current output.  I have an additional column with the tentative open date that I'd like to add to the list in Pending Branches.  My current formula:   IF(values,IF(COUNTALL(values) = 1,"• " & TRIM("" & values),IF(COUNTALL(values) > 1,"• " & TRIM(ARRAYJOIN(values, "\n• "))))) Please help!!! And thank you 🙂 

183
L
O
obzervrNew Participant
posted in Formulas
Time zones for formula datetime fields

I'm trying to find a way to display time zone for a 'formula' field that ends up being a datetime, in the same way that time zone works for actual datetime fields.I've reviewed the new SET_TIMEZONE function and some posts here, but not finding an answer.A simple scenario to help clarify:- A have a datetime field, my settings are to use the user's time zone when presenting datetimes. So that everyone from around the world understands what time is referred relative to their time. E.g. a meeting start time- I also have a formula field that calculates a different datetime using the datetime field mentioned above, for example, add x hours to that date time to produce a new date time.- Now, this is where it all falls apart. The formula field doesn't behave like a regular datetime field, even though, for all intents and purposes outside of its own calculation, I want it to work as though it was a regular datetime field.- It would appear, that I have no way of this formula datetime appears wit

1091
ScottWorld
K
kennypurnomoKnown Participant
posted in Formulas
Difference in attachment url

Hi, I have many rows with multiple attachments. Some attachment urls are having https://v5.airtableusercontent.com/v1/ format. Some of them have https://dl.airtable format.How do i tweak the script if possible to get https://dl.airtable format? I need the direct url link with .png in the link for another purpose

455
C
H
HarrryNew Participant
asked in Formulas
Airtable Date Calculation

Hi everyone!I am currently setting up a database to calculate how long a project has been active.This means I'd like a formula that calculates the difference between (a) the start and finish dates or (b) the start and current date (if a finish date has not been specified).I know of formulas that can do either of the needed functions, but not one that is conditional and selects which calculation is appropriate for each project. Hopefully someone might be able to help me out? Thanks again!

102
H
RonniEloff_VKDe
RonniEloff_VKDeInspiring
asked in Formulas
Looking for a formula to parse out date to auto-populate two other fields

I have tried a ton of suggestions but I am not figuring this out.  And I thought it would be a simple auto-parsing formula.I have an interface that allows our team to enter a new task/call record into our task tracking base (see pic below)I have a field that is Date-Time field that when an entry is created, the person adding the entry (through an interface) selects the current day/time when they start the task. (There is a lot of behind the scenes processing that happens with this information)I want to be able to auto-populate a Date only field based on what was entered in the Date-Time field. Right now the Date and Month field are manually updated by the person filling out the entry.  (I want to remove this manual set of steps)I also want the Month field to auto-populate the Month field - so again we can present the what happened in the month (Closed, Open, Pending tasks)Reasons: We can have dashboards and the DB Views that organize/present what happened on each da

52
RonniEloff_VKDe
Nathan_Ramey
Nathan_RameyKnown Participant
asked in Formulas
Removing an abbreviation from records

I have a column that has a list of counties and they all have the state abbreviations next to them. Is there a way to remove the abbreviations with a formula inside of airtable? 

93
Nathan_Ramey
M
mmr1443Inspiring
posted in Formulas
How to combine two fields with itemized prices

See screenshot below. I'm using this to generate invoices. It does everything I want it to do, except for one thing. For the final invoice that the customer sees, I want all products, both custom and standard, to be listed in a single field (success!). Then I also want all product prices, both custom and standard, to be listed in a single field (no success! Circled in red).Issues I've run into with using "&" and CONCATENATE:1. I want everything separated by commas, but I don't want there to be a comma at the end, for example if there are only standard products and no custom products.2. I want the column to be formatted as currency, the same way the "Itemized Prices" and "Custom product price" are already. When I have tried using various formulas, it tells me that my field is no longer a number or a currency and cannot be formatted. Please help!  

122
M

Badge winners

Show all badges
Terms & Conditions