Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,228 Replies
5939 Topics
A
Aysia_SaylorKnown Participant
asked in Formulas
Assistance with Dateadd & Workday Formula

Hello, I’m trying to create a formula that returns a workday date after 4 days are added. How do I adjust the field that is feeding the data, and the formula itself to always return a date that is on a weekday? It returns an “#ERROR” every time I try one of the formulas below. DATEADD({Process Vetting Start Date},4,‘days’)) DATEADD(WORKDAY({Process Vetting Start Date},4,‘days’)) WORKDAY(DATEADD({Process Vetting Start Date},4,‘days’)) Thanks in advance

581
kuovonne
B
Benjamin_JonesNew Participant
posted in Formulas
Calculations within nested IF AND statements?

Hi everyone, We have updated our service pricing in October and I’m trying to update the calculation for services rendered after 10/1. In the below code {Direct Time} is {Direct TIme} + {Indirect Time}. Is it possible to use calculations like this within a nested IF statement? (IF AND( {FY}=“22”, {MM}>=“10”, ({Total Time} * {Rate by FY by MM})+{Pass through Materials}, ({Direct Time} * {Rate by FY by MM})+{Pass through Materials}, ) ) Best, Ben

194
Justin_Barrett
K
Katy_RichardsParticipating Frequently
asked in Formulas
Nested IF Concatenate

Hi. I would appreciate help with the following: I have the fields Country (required field), Region and Title. I need to concatenate these fields in different ways depending on which fields are filled. -If just Region is filled then Region, Country -If just Title is filled then Country- ‘Title’ -If both Region and Title are filled then Region, Country- ‘Title’ -If neither are filled then Country Thank you in advance for any help. Katy

52
K
E
Edo_VigoNew Participant
posted in Formulas
How to reach 2021-06-15T15:30:00.000Z with the formula

I didn’t get how to reach this result 2021-06-15T15:30:00.000Z. Do I need to use the datetime parse or format? Could you suggest the formula please? My starting point is a date field (ex: 02/10/2021 5:08 pm) Best

21
Kamille_Parks11
F
Flore_LestradeNew Participant
asked in Formulas
What is wrong with my SWITCH formula?

Hi everyone, I have one “Status” column with different status; I want for each status to be linked to another status, slightly different in another column called “Client Status”. To do so, I wanted to use a Switch formula: for a status A, there will be a status A’ linked, as shown in image attached. I wrote this formula: SWITCH({Statut} ,“ :mailbox: Contact entrant - à traiter”,“ :white_check_mark: En attente de validation”,“ :arrows_counterclockwise: Offre en cours de traitement”,“ :1234: Offre validée → n° d’acquisition à créer (CAP18)”,“ :truck: Collecte à effectuer”,“ :date: Offre validée → collecte à planifier”,“ :truck: Collecte à effectuer”,“ :truck: Collecte planifiée → à effectuer”,“ :truck: Collecte à effectuer”,“ :computer: Collecte effectuée → prestations à faire”,“ :computer: Prestations en cours de réalisation”,“ :email: Prestations effectuées → livrables à envoyer”,“ :computer: Prestations en cours de réalisation”,“ :ok: Livrables envoyés → vérifier montan

61
Kamille_Parks11
L
Louis_HuberNew Participant
posted in Formulas
Counting series (for example: Winning Streak)

Hello, is it possible to track a Winning streak? And do you know how? For example Cell Win increments +1 –> Cell Series should also increment +1 -> If Cell Lose increments +1 the Cell Series should be reset to 0. How can I safe the highest value of the cell (best Winning streak)? Do you have a better logical process? And is it posible with airtable? Thanks and best regards!

53
V
S
SunLynxKnown Participant
asked in Formulas
Attempts to Correct Timezone Ineffective

I’m having trouble getting DATETIME_FORMAT to work, and I can’t figure out what I’m missing. My date field is in local time. I wanted to get week numbers from the date, which I have working fine, but it is seeing dates in UTC I think, and DATETIME_FORMAT did not work. Formula: DATETIME_FORMAT(SET_TIMEZONE(Date,'America/Los Angeles'),'MM/DD/YYYY') Referencing the red box you can see two entries with incorrect format. I’m a little stumped.

112
S
A
Adam_RekerNew Participant
posted in Formulas
Creating a formula to filter for a date range as well as a check box

I’m looking for help to write a formula that will help filter records that meet these requirements. I can’t seem to get it done using the filter function. Where date is within the next week or Where date is within the last 3 days and Where check box is not marked

43
A
J
john_klacynskiNew Participant
posted in Formulas
Count multiple values in field separated by semi colon

hi i need help counting a field with multiple values separated by a semi -colon. This column catches data from jotform, which is a multi-select field.

332
B
T
TVR80New Participant
asked in Formulas
DateTime_Diff formula with three different possibilities

Hello- I’d like to create a formula that allows me to track expiration dates status (three possibilities): Due soon, but not within four weeks (blank value in table); Due within four weeks (“Yes”); Past due (“Late”) I’ve been able to create this with the first two status items, but not the third (“Late”). Here’s what I have: IF( AND( DATETIME_DIFF({Expiration}, NOW(), “days”) < 28, DATETIME_DIFF({Expiration}, NOW(), “days”) > 0 ), “Yes” ) Thanks!

64
T
P
Paul_RobinsonNew Participant
asked in Formulas
Extract Firstname and Lastname from Email and Format it to Titlecase in 1 single Formula

This is not a question more so a time saving work around for anyone else that faced this issue. Scenario: We have a column of emails in the format of ‘firstname.lastname@domain.com’ We needed to break this down to their full name in one column but the standard UPPER and LOWER case formulas left the data looking ugly and wasnt great for merging into emails. Alas, this ugly AF formula is what I could piece together from various wild google chases. Enjoy I hope it helps you. REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX

543
Bill_Felix
M
MarlevNew Participant
posted in Formulas
Increment a date range

Hello ! I built a gantt project with starting date and due date in the same column (through the due date) It is displayed like below: If I do a copy/paste, it looks like 24/06/2021 → 25/06/2021 I would like to increment the whole range (not the dates) by let’s say 10 days. I already checked dozens of posts relating to incrementing dates, but not dates range. I tried the formula :blush: And I also tried all my Excel formulas to try to manually increment these dates but it does not work. Does anyone has a solution? Thank you :smiling_face_with_halo:

41
Rupert_Hoffsch1
P
Paul_BairdNew Participant
asked in Formulas
Summing WORKDAY_DIFF numeric outputs

Hi All - This will be my first post. I would like for our category row ‘Summary’ cell to sum the output of our number of work days driven by the following formula IF({Start Date} = BLANK(), “0”, WORKDAY_DIFF({Start Date}, {Finish Date})) The formula currently is working to output the numeric value of the number or work days, but AirTable ‘Summary’ does not recognize it as numeric and thus does offer the option to sum the results into the Summary cell.

122
P
T
Team_SmartStatsNew Participant
posted in Formulas
Extracting multiples occurrence of the same given keyword from a String

Hi everyone and thanks in advance for your help :), I’m building a Soccer Analytics tool with Airtable My goal is to extract the results of the 5 last games for a given team. (For exemple “Win - Win - Lose - Draw - Win” or " W - W - L - D - W") So far, I’ve build two tables, one that contain the teams info and one that contain the games info. Each game records is link with two teams (Home Team & Away Team) I tried using roll-up to achieve my goal, but I was only able to get the result for the games played Away and the games played Home separately and in order to work I need the results to be in chronological order in a single cell. The closest I was able to get is by formatting the result of each game in a single string with the team names (ex: Alaves=L/Real Madrid=W) From here, I’m able to use a lookup in the team tables to get an array of all the results for the games played by a given team. It looks like that : Alaves=L/Real Madrid=W, Alaves=L/Mallorca=W, Alaves=L/Osasuna=W,

202
T
K
Karim_BadraParticipating Frequently
asked in Formulas
Blank & Not(Blank) Formulas for Contact Information

Hi, I have landline and mobile numbers in my data base. If one of them is missing, i want to show the other, if both are missing, i want to keep it blank, and if both are full i want to concantenate them. This is my formula syntax but its not working. Can you please help correct it? IF( AND( {Mobile Phone 1} = BLANK(), Landline = BLANK() ), “Both Blank”, IF( AND( {Mobile Phone 1} = BLANK(), Landline = NOT(BLANK()) ), “Landline”, IF( AND( {Mobile Phone 1} = NOT(BLANK()), Landline = BLANK() ), “Mobile”, “Both” ) ) )

812
K
K
Karim_BadraParticipating Frequently
posted in Formulas
Vlookup for {Named Field}

Hi, Airtable seems to only offer a lookup for a specific string. I am trying to do a lookup based on another cell’s value. Example: Company receives a request for one table. In turn, it needs to make a request for quotation from 3 companies for wood. So you want to a lookup that says "If (Table Request) + IF (Vendors Chosen), Lookup {Contacts: Table Request : Vendor). As in, show me the applicable contacts that relate to this particular vendor and this particular request. Thank you!

22
K
E
Eric_RomoffNew Participant
posted in Formulas
Nesting IF Function for multiple fields

Hi Everyone, I’m trying to nest a long string of “IF” statements to populate a field and keep getting errors back (Sorry, there was a problem saving this field). I think it might be because I’m targeting different cells with my “IF” parameters… Can someone help me design the correct formula to handle the following: IF({CELL1}, FALSE(), “INPUT1”), IF ({CELL1}, TRUE(), “INPUT2”), IF ({CELL2}, TRUE(), “INPUT3”) IF ({CELL3}, TRUE(), “INPUT4”) and so on… needing about 8 of these in total. Any help would be greatly appreciated!

32
kuovonne
A
Aysia_SaylorKnown Participant
asked in Formulas
Creating formula field for determining "In progress", "Overdue"

Hello, I’m trying to create a formula field that will return a value of “Overdue” or “In Progress” based on the values of 2 other fields: Writer Due Date and Status. Here’s what I’m trying to achieve: IF the Status is any of the following: In Progress, Awaiting Information (Outreach 1), Awaiting Information (Outreach 2), Awaiting Information (Outreach 3), AND the Writer Due Date is BEFORE the provided deadline, then the field returns “In Progress”. IF the Status is any of the following: In Progress, Awaiting Information (Outreach 1), Awaiting Information (Outreach 2), Awaiting Information (Outreach 3), AND the Writer Due Date is AFTER the provided deadline, then the field returns “Overdue”. How would I write this as a formula? Thanks in advance

132
A
P
paolo_contiNew Participant
posted in Formulas
Application Tracker Date comparison

Hi guys, I’m trying to come up with a formula that will calculate the number of days that has passed and based on that number, change a label in a field. Here are the specifics: I have an airtable for keeping track of job applications that I’ve submitted. I want to automatically update a label field based on the day that I applied. Example: I apply for a job and mark the date (date field), then I manually set a field called Follow up to “recently applied”. Then after 7 days, I manually change that label to “Send 1st follow up”. So how do I create a formula that would basically do this: if(date applied < 7 days){ set the label to “Recently Applied” } if( date applied is between 8 and 16 days){ set the label to “send 1st follow up” } if( date applied is between 17 and 24 days){ set the label to “send 2nd follow up” } else{ set the label to “maybe you should send your application” }; Thanks in advance for the help!

32
A
R
Russell_FindlayInspiring
posted in Formulas
Extracting the first number from this type of text

I am looking for a way to extract the first value from a field that has a text result like this £38,331 - £41,216 per year It is to simplify the output of a data extraction of salaries Your help greatly appreciated

127
Justin_Barrett
G
Giacomo_CordaNew Participant
posted in Formulas
How to use a roll up field to fill a formula field

how can i use a roll up field to dynamically populate a formula? Is it possible? Thanks to everyone

893
Justin_Barrett
J
Jonathan_CoultaKnown Participant
posted in Formulas
Problems with rounding

Hi folks, I have a problem with the Round() function. I have a formula to display two percentages as text; ROUND({Australian Shares % of Portfolio},4)*100&"% vs target “&ROUND({Australian Shares Target},3)*100&”%" The output is fine in about 80% of cases but occasionally I get this; 28.999999999999996% vs target 35% I have tried formatting the original formulas with Round and formatting options but have not been able to stop this happening. Any thoughts on what I could do? Thanks, Jonathan

51
kuovonne
J
Jahsun_Daher_GoNew Participant
asked in Formulas
Discount formula and multiple functions

Hello everyone! I’m trying to migrate from Google Sheets to Airtable, but still having issues creating a budget template for my projects… As you can see in the screenshot below, my “total” and “tax” fields are working fine when I add numbers to the fields “QTY” and “COST”, however, my “SUBTOTAL” formula (also in the screenshot) always stays at zero, regardless if I do or do not add numbers to the fields “freight” or “Discount”. I also would like my subtotal field to calculate the formulas even if “discounts” and “freight” are numbered as zero as I don’t always get a discount or have to pay for freight. Can someone please help me understand what I’m doing wrong and how to solve it? It would be very much appreciated!

253
Dimitris_Goudis
N
Noelle_PoulsonNew Participant
posted in Formulas
New Customers per Product Each Year

Hello, I have a problem I can find a solution for in Excel but can’t seem to figure out in AirTable. Any advice much appreciated! Here’s an example that is analogous (not exactly our case but in the most simple terms I could think of): We have a table of Customers and a table of Products, with the junction being a Sales table with one customer and one product only for each record (customers can buy many products but in the Sales table we create a record for each Customer/Product combination) and also a date of the sale. For each year, I need to report the number of NEW customers per product. So if Customer X bought apples last year, I do not count them again this year (Customer X/Apples). But if the same Customer X didn’t buy oranges last year and did this year, we would count them this year (Customer X/Oranges). (Reporting ALL customers per product per year is easy enough, but capturing just the new ones is eluding me!) In Excel I could manage it with a countif formula like this

00
N
Nedra_Hines
Nedra_HinesInspiring
posted in Formulas
Dateadd formula isn't working correctly

Hi everyone! I’m using the Dateadd formula to add a certain number of days to a starting date to calculate a new date. For some reason, I have to include an additional day (32 instead of 31) to get the result to display ‘correctly’. Is there something I need to change in my starting date?

352
Nedra_Hines

Badge winners

Show all badges
Terms & Conditions