Formulas

Hi! First of all, I am french so I hope you’ll understand my question. :hugs: I’d like to calculate how long has an employee been with the company, so : since entry date (fr: date d’embauche) until now/today. I already have a formula that calculates the number of years (fr: années) and another formula that calculates the number of months (fr: mois) but I’d like to have these informations in one formula. Is that possible ? Thanks !

I thought this would be fairly simple but then it turned out to be a little more complicated, and I’m not sure if this is possible to do without Javascript. I have a work start date, I know how long the work is going to take (in hours) and I want to calculate an estimated End date. But different days of the week has different shift hours fx. Mon-Thurs has 10 hours shifts, Friday 9 hours and Saturday 5 hours. Is it possible to calculate an end date, with these informations? I’ve tried to use play around with WEEKDAY() and DATEADD() but nothing sofar has come close to any solution. Or is this only possible in JavaScript?

Hi there! I have the following categories in my table: Task Due Date, Weeks From Launch (Formula Field) and Launch Date. I want to write a formula in the Weeks From Launch category where if the Task Due Date is blank, then record cell stays blank, otherwise it uses the Launch Date and Task Due Date fields to calculate weeks from launch. My current formula (returning an error message): IF({TASK DUE DATE} = “”, “”, DATETIME_DIFF({TASK DUE DATE}, {LAUNCH DATE (from PROJECT)}, ‘WEEKS’) Any suggestions? Thank you!

Hey All! I am trying to check to ensure that all timesheets have a corresponding scheduling item but my FIND formula is only working half the time. Any help would be appreciated! IF(FIND(Timesheet,{Scheduled Dates (from Task)})>0,"",“Missing!”)

Hi All, I have a field which is pulling in from JIRA and displays like this: 154, 152, 157, 155 There could be more or less numbers. What I need is to create a new field which is the highest number in this field. (So it will display 157 in this case). The issue is the rollup feature is displaying 0. I assume this is because this is being viewed as text. Any ideas how to solve this? Cheers Simon

Hi Community, I want to use a formula to calculate the age of a product DATETIME_FORMAT(TODAY(),‘YYYY’) - {year produced} Some fields of {year produced} are empty, so the formula comes back as the age of 2022 years old. Is there some way to exclude the empty {year produced} fields? The year produced, has the number field type. Thanks in advance!

Hey everyone! Fairly new to airtable and still learning about automations and formulas. We are creating a fairly complex app for medicinal products. In our base we have a products table and an indications table. An example of an indication would be “Pain” - This is linked to a stacker front end. What I’m trying to achieve is to be able to count how many times a single indication has been given to a product. For example: When someone is adding an indication such as “Pain” to a product they can see “5 others added this indication”. and once added it’s then updated so that it appears as 6, 7 so on. I have been able to automate a count which listens for when an indication is added and ticks up however this doesn’t work because each time any indication is added or removed it continues to tick up. Trying to figure out how to make it dynamic so that it can update and change for any product. Any help would be greatly appreciated

I am trying to automate the calculation of an award based on an age category (“PVSA Age Group”) and a number of hours volunteered (“Total PVSA Hrs.”) (see chart). I think I need nested IF statements with an AND, but not sure. It’s not working so far (because I have a day job hello.) I need an output based on these two variables. Here is the first part of my formula, attacking only the second row (we have no contenders for KID category in the first row, just the remaining 3 rows): IF( AND( {PVSA Age Group}=“Teens”, {Total PVSA Hrs}<50 ), “No award” ) & IF( AND( {PVSA Age Group}="Teens”, {Total PVSA Hrs}<=74 ), “Bronze” )& IF( AND( {PVSA Age Group}="Teens”, {Total PVSA Hrs}<= 99 ), “Silver” ), “Gold” That doesn’t work. Once I get it working, I need to figure out how to concatenate the other two rows (for different age categories.). Do I eliminate the subsequent AND statements, to save them for the concatenation?

I have used this formula to separate data that is simple 4 letter abbreviations and it works perfectly. However, the data is also associated with dates and times that I need to separate and have readable and separate in an email automation. When I use the above method, the dates (which read fine in their native field) come out looking like this Has anyone run into this before or have a solution?

I am trying to create a quality score for a series of job applications. I have a series of fields returning a count of how many “excellent,” :“good” “poor” evaluations each application received, expressed as a numeric value. ie Count-Excellent Count-Good Count-Fair etc. First I want to multiply each field by a unique value in order to build up a score, ie if an application got 2 “Excellent” ratings (shown in Count-Excellent as “2”), give it 5 points for each Excellent rating and thus returning a value of 10. And 4 points for each Good, etc. Then I want to add all those values together, producing a combined score, and THEN… divide by the number of reviewers, which is stored in a field called ReviewerCount and is also a number. Basically I want to do something like ((Count-Excellent * 5) + (Count-Good * 4) + (Count-Fair * 2)) / ReviewerCount and have it produce a score, but I seem to be getting hung up on the parenthesis and brackets and whether to use SUM. None of the field names h

I am trying to use REGEX_EXTRACT to convert a username into an Email address. For example: WSSUMITS\smithfred => smithfred, then I can add the @domain.com to the result. The regex formula I came up with, through trial and error, is: REGEX_EXTRACT({Asset Last Logged In User}, '^(?:WSSUMITS)?([^:\\/\n?])+\\') However, I’m getting ERROR in the field. I don’t have much experience with regex. Where am I going wrong? Thanks in advance.

hi, I’m new to airtable and not much of a techy person so even when I read some of the comments for how to do a running total, I’m still so lost… I’m trying to create a monthly roll up to see how much money I should have at the end of each month but rolling up from the last month total. I am able to get as far as getting the Income - Expenses = Total but I tried using Previous Row function to get last row total to try to add it to next month’s total but it tells me it causes a circular reference. Or is there a way to add last month’s total with this month’s income? I’m at my end on trying to get a running total. HELP anyone… here is the view of the table… I need the previous row to be the last column roll up. I can’t set it up since it gives me circular error so only been able to use Delta for previous row to test it but it’s not the right value I need for the running total. how can I get the last column to be the running total?

I am trying to solve the following problem. I have thousands of registration numbers that always start with a “T” and are followed by 10 digits. This is how they are delivered to me T3090728379. But they all need to look like this T-309072837-9 which translates to the following format T-[9 digits]-[1 digit] The last digit can be anything between 0-9. What formula could I use to reformat these fields in Airtable? I managed to substitute “T” with “T-” but I do not know how to get a hyphen in front of the last digit. Is this something one would use the LEN() function for? (I have never been able to fully understand how this works…) I’d appreciate any help. Thank you so much in advance. This is how far I got: IF(STRING,SUBSTITUTE(STRING,"T","T-"))

Okay, so I’m new to AirTable, trying to move away from Excel sheets :slightly_smiling_face: I’m creating a forecasting tool for my sales department. For each project we forecast the number of products that we will sell. For my purchasing department, I’m creating another tool that will use the summarized number of forecasted products in calculations. So for example Project 1: Product 1 - 30 pcs. Product 2 - 10 pcs. Project 2 Product 1 - 5 pcs. Product 2 - 10 pcs. I need the sum of Product 1 (35 pcs) and Product 2 (20 pcs) available for further use in another table. How can I do this? These qty I would like to summarize in the Product table.

I’m relatively new to airtable and i’m working on a tracking and costing system for the project I am working on. I am creating security schedules which are usually the same for monday through to friday. they change on saturdays, sundays and public holidays. I want to count the number of saturdays and sundays in a given period to set the schedule for those particular days but the starting point is knowing how many there are… can anyone help out with this? thanks

Hi, I need help to split this string into four pieces and put them in four new columns… Maybe split two times with different delimeters? {“email":"mail@gmail.com”,“firstname”:“John”,“lastname”:“Hong”,“phone”:“1234567”} Column 1: mail@gmail.com Column 2: John Column 3: Hong Column 4: 1234567 Thanks in advance!

Hello, I am looking for a solution/formula that will generate in a Multi-Select field all unique dates between ‘start date’ and ‘end date’. I need that solution to generate a report with rollup fields and calculate the number of inhouse guests on specific dates, for exemple in hotel room management, or vehicles car rental. Thanks

First of all, I am a beginner at creating formulas. I only have experience with with basic math formulas. I am trying to create a field that show how long a product was on the market. I have two fields, purchase date and sales date. Purchase is the day we bought the items, Sales date is the date we sold it. I used the datetime_diff formula but I am getting NaN on items that have not sold yet. How do I create a formula that would show days on market for items that have not sold yet. In other words, the items that have been on market for 30 days would show 31 days tomorrow, 32 days the next day, and so on… Is a formula like that possible? If not, what are my options to remove the NaN appearing in the field?

Hi all, I’m trying to create a formula to help me calculate invoicing periods. If the payment date is before the 20th of the month the invoicing period is the current month, if the payment date is on or after, then the invoicing period will be the next month. Essentially, there should be two outputs, the first day of he current month or the first day of the next month. I’ve tried to build this with an IF formula, but I’m coming unstuck with the first half of the formula when testing as it doesn’t seem to be calculating correctly: IF({Date paid} < DATETIME_FORMAT({Date paid}, ‘20/MM/YYYY’), DATETIME_FORMAT({Date paid}, ‘01/MM/YYYY’), “test”) How anyone advise on where I’m going wrong on this one?

I have two fields full of product reference numbers that I want to compare for matches. The first is called Reference . This is a long list of stuff we make, some of which are out of date and out of stock. The second is called OldReference , which contains just the old items with remaining inventory. I want to search the two fields for instances where the reference numbers match, and then mark them somehow. I assume in a third field that returns 1 or 0, true or false, yes or no… Anybody know how to do this using a formula, lookup field, or some combination? Thanks in advance to anyone with a good idea. :grinning_face_with_big_eyes: -Frank

Hello, I’m trying to summarize information in a Linked Record field type collected through Forms in which respondents can select multiple linked records. We invite respondents to select their Subject Area Expertise, options provided are categories from a sector-wide set of standard taxonomy. The Subject Area taxonomy is organized in a hierarchical manner. For example: Level 1 (Category): Health (SE000000) Level 2: Nursing Care (SE060000) Level 3: Home Health Care (SE60100) Level 3: Hospice Care (SE60200) Respondents can select from the whole list without seeing the hierarchy. I would like to generate charts that summarize and collapse respondents’ answers. Using the example above, I would like the chart to collapse all individuals who selected “Home Health Care” and “Hospice Care” to fall under the bar/pie chart slice for "Health" I tried using a lookup field for each Subject Area’s category, but it duplicates responses In the example below, the row with three different “Environment”

I currently have the following IF nested statement. I don’t get an error when saving, but it only works when the first IF is found (13) and produces nothing for a result when the other two are in the identified field to check. Am I missing a key element to make the “false” portions of each IF statement work with a new nested IF statement? IF({⛔ Extender Weeks}=13, SUM({👌 WEEKS in Program (26 = 6mo)}+13, IF({⛔ Extender Weeks}=9, SUM({👌 WEEKS in Program (26 = 6mo)}+9, SUM({👌 WEEKS in Program (26 = 6mo)}+4)))))

Hello, I am in despair, I want to build a table, with UTM left. The UTM links are fixed, but still need to exchange names and discount code. In google Sheet this was totally easy, but in Airtable I just can’t get it right :frowning: Here are the fields, the function should take: https://www.website.com/?utm_source=instagram&utm_medium=influencer&utm_content=LINK_TO_FILED&utm_campaign=webseitecampain&utm_term=websiteterm&discount=LINK_TO_FIELD Thanks for your help

Hi there. I have a multiple select column that contains seven audience types, which have been added in a different order (no set pattern). I also know how big these audiences are (eg Generalists = 4406). I’m trying to write a formula that returns a total audience based on whatever has been selected in that multiple select box. EG if “Generalists” and “Digital” have been selected, in any order, it counts 4406 + 106 I can write an IF command to create a value for an exact match in the box (e.g. ONLY says “Generalists”), but stuck on how writing this more complete form. Any help gratefully received!

I’m making course codes (like ACT015) from numbers (like 15) and prefixes like “ACT”. The trick is - how to left pad the numbers with 0 when combining them? The part of the formula to add the right number of '0’s: REPT(“0”, 3 - LEN(Number&"")) and the whole formula: CONCATENATE(Prefix, REPT(“0”, 3 - LEN(Number&"")), Number) Hope this helps someone! I didn’t see any other posts on the first page of results when I googled “airtable formula leftpad”, so had to get creative.