Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,938 Topics
  • 18,225 Replies
5938 Topics
D
Darren_LedwichNew Participant
posted in Formulas
Autogenerate 4 digit numbers

Hi, I am creating a client job tracker and would like to autogenerate 4 digit numbers, eg. 0001, 0002. Can someome please help as I can only get a single digit or decimals so far. Thanks Daz

806
S
M
Michael1Known Participant
asked in Formulas
How to extract text

Hi, I have a list of cities and states, and I want to extract only first state initials. How can I do that? Thanks

201
Ben_Young1
P
Patrick_MustoNew Participant
posted in Formulas
Differentiating between day and night hours using a formula

Hi there, I’m trying to figure out a way to take 2 date inputs (shift start and shift end) to output a duration and I’ve done this using “DATETIME_DIFF({Shift End}, {Shift Start})”. However the system we use for our company relies on daytime hours and night hours being paid differently, so ideally we want the end duration to be split between day and night where the crossover is 7AM/7PM. For example if someone started their shift at 0500 and finished at 2300 they’d work 12 day hours and 6 night hours. Would this be possible to configure within Airtable’s capabilities? Thank you in advance.

304
P
S
Shawn_CarrieNew Participant
posted in Formulas
Calculating Date Differences

I’m trying to use a formula to calculate the number of years between two dates, namely the start and end dates of a job/position – while accounting conditionally for when ether of those fields are blank. Thought myself rather clever with this formula, but sadly it’s not working. Can anyone help me figure out what’s wrong? IF( {Position Start}=BLANK(), "", DATETIME_DIFF( {Position End}, IF( {Position End}=BLANK(), TODAY(), {Position End}), 'years' ) )

21
Ben_Young1
J
Janet_JanowiakNew Participant
asked in Formulas
Need "If" Statement to calculate referral amt if there is a name in the referral name field

I’ve been fighting this and researching long enough. here is what I currently have that doesn’t work: IF({Referral Name}}, “Field is Not Empty”),{Commission}{Referral %},"")* Explained: If Referral Name field is not blank or empty, Calculate Commission field multiplied by Referral % Otherwise, leave return a blank field. Help! :slightly_smiling_face:

53
kuovonne
M
M_kInspiring
posted in Formulas
Airtable Formula

Hi Airtable! I am trying to figure out how to create a formula for a string. I would like to remove everything after the first set of numbers, with no spaces: 1234 56.jpeg 1234a 56.jpeg to: 1234 1234a Can someone help me? Formulas are not my strong suit. Mary

105
Ben_Young1
L
Laurie_AhmediNew Participant
asked in Formulas
Formula to calculate employee's seniority in years and months

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 !

255
L
Kim_Trager1
Kim_Trager1Brainy
posted in Formulas
Is it possible to calculate an end date if days have different hours?

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?

92
Kim_Trager1
S
S_Pacheco-WilliNew Participant
posted in Formulas
If statement with date formula

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!

432
S
C
Corrine_YeagerNew Participant
asked in Formulas
FIND Formula not picking up all values

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!”)

102
C
S
Simon_HavilNew Participant
asked in Formulas
Pull highest number of a comma separated text field

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

55
S
R
Robbie_HeNew Participant
asked in Formulas
Skip empty field in formula

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!

173
R
K
Kai_CraigNew Participant
posted in Formulas
How to count how many of the same record is linked to another

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

196
K
V
Valerie_MilesParticipating Frequently
asked in Formulas
Formula to return a result based on two variables (with ranges) in a chart

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?

114
V
K
Kevin_BowerNew Participant
asked in Formulas
How to separate multiple dates pulling from look up fields and have them readable

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?

143
M
E
Ellen_EdgertonNew Participant
posted in Formulas
Formula help needed using sum, multiplication, division

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

111
Ben_Young1
F
Fred_Smith1New Participant
asked in Formulas
REGEX_EXTRACT Help

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.

434
F
B
B11New Participant
posted in Formulas
Running total for monthly budget

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?

422
kuovonne
M
Markus_WernigInspiring
asked in Formulas
Reformatting a string: how to add hyphen in front of last digit?

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-"))

142
M
E
Erik_LeirdalNew Participant
posted in Formulas
Summarize forecasted quantity per product

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.

51
Rupert_Hoffsch1
M
Matthew_CamilleNew Participant
posted in Formulas
Count number of Saturdays & Sundays in a date range

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

401
JonathanBowen
A
alionaNew Participant
asked in Formulas
Split text into new columns, unclear delimeter

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!

324
kuovonne
W
Walid_ChakerNew Participant
posted in Formulas
Unique dates between 2 dates (start - end)

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

155
C
P
Philip_MarshNew Participant
posted in Formulas
Formula to show days on market and remove NaN entries

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?

153
P
B
Bayo_ObasajuNew Participant
asked in Formulas
Help with a conditional date formula f

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?

130
Kamille_Parks11

Badge winners

Show all badges
Terms & Conditions