Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,940 Topics
  • 18,230 Replies
5940 Topics
C
Candice_MooneyParticipating Frequently
asked in Formulas
Substitute HH:mm in DateTime field with new HH:mm based on conditions

Hello, I am new here. I am also new to formulas. Have managed to work out the formula to calculate the number of 12hr shifts between two date/time fields. ROUNDUP(((DATETIME_DIFF({DepartureDate},{ArrivalDate},‘minutes’) /60 ) /12), 0) My struggle is that shifts are from 06:00 to 18:00 day shift and 18:00 to 06:00 night shift. Therefore if a start time is any time between 06:00 and 18:00 I need it to 'snapback to the default start 06:00. And the same for if the start time is any time between 18:00 and 06:00, I need it to ‘snapback’ to the default start 18:00. Bearing in mind that these are date and time fields formatted YYYY-MM-DD HH:mm. Can anybody help with the correct formula? I hope my explanation makes sense!

2513
C
Ricardo11
Ricardo11Inspiring
asked in Formulas
Regex extract pattern

Depsite @Justin_Barrett’s help last time, I still cannot figure out simple regex patterns. This time, I’m combining in a roll-up year and value and am trying to extract the value based on a date. IF( AND(Date, Values), REGEX_EXTRACT( Values, "(?:" & DATETIME_FORMAT(Date,"YYYY") & " )([^,])")) For example Values: 2023 530,2022 480, Date: 2022 Result: 480 Preferably each string would be Year:Value,Year:Value… but I was afraid the double dot may me some reserved character and dropped it.

1182
Justin_Barrett
F
Franck_GentilinNew Participant
posted in Formulas
Wrong calculation

Hello, in the following formula the result is false Thanking you in advance for your return Price €699 * insurance : 1.5% = € 10.49 * amount : 2, total 20.98 - The formula gives €20.97

102
F
M
Micky_WolfNew Participant
posted in Formulas
Attendance System to Count Potential Days in Real-Time

Just wanted to share an awesome exchange I had with the Airtable Support Staff! We have programs with different start dates that meet on different days. I wanted to count the total days since start dates that have passed so that we can have our Attendance System Account for it. This is what they said: Do you have a field in your base that indicates whether a role meets Tuesday/Fridays or Mon/Wed/Fri? If so, you could combine the formula that Kuvonne suggested in the community post you linked with a IF( statement to calculate the number of class instances that have occurred. In the example below, I created a formula that operates under the assumption that there is a field denoting ‘T/F’ for the Tuesday-Friday class and ‘M/W/F’ for the 3 day meeting schedule. If you have a Start and End date that you will be comparing, you could structure your formula like the below: IF({Meeting Cadence}=‘T/F’, SUM(DATETIME_DIFF({End Date}, {Start Date}, ‘weeks’)+ IF(WEEKDAY({End Date}) >= WEEKDAY({St

60
M
K
Kc_chenNew Participant
posted in Formulas
Can IF use selected type

Hello everyone, Sorry to bother you, but here’s a little question for the formula. Can the selected function be used in the formula? For example: IF(argument 1 selected A, then count as xxxx, argument 2 selected B, Argument 2 Selected C then xxxx) I’ve tried a few different ways, but it didn’t work. Hope anyone can kindly give me some structure, Thank you. Best Regards, K.c

84
K
M
myrte_van_der_vNew Participant
asked in Formulas
Format dates inside a add-date & IF formula

Hello, For my plant tracker I’ve been using a formula to give me an estimated date of transplanting. This has been working fine. Now I want to add a check-box, so that when I plant something in-ground and i check this, the Estimated date of transplanting box will return an N/A vallue. I got all of this to work but I have a problem with how to date is formatted. As you can see the last column has a different date format. This is the code: IF({In-ground} = 1, “N/A”, IF({Germination Date}, DATEADD({Germination Date},{Estimated days to Transplanting}+0,‘days’),IF({Estimated germination date}, DATEADD({Estimated germination date},{Estimated days to Transplanting}+0,‘days’)))) I tried to add a BLANK() after the DATEADD so I can change it in the formatting tab but it doesn’t work. I think I have to add a DATETIME_FORMAT but i’m not sure where to add it in this formula. Thanks for your help!

102
M
C
Carlos-WerqParticipating Frequently
asked in Formulas
How can I best combined a column with a switch formula and another with a if/and formula?

Hello! I have two fields. One titled: Naming Convention Help w/ this formula: SWITCH({Naming Convention},"Series",{Standard File Name (Series) Template}, "Movie",{Movies - Naming Convention}, "Awards & FYC",{Awards - Naming Convention}, "Free",{Free Series - Naming Convention}, "Series V2",{Series Naming Convention V2}, "Series V3",{Series Naming Convention V3}, {Standard File Name (Series) Template}) and another formula field titled Naming Convention Final: IF(AND({Language}&"" = "en-US", {Naming Convention}="Series V3"),{Series Naming Convention V3},{Naming Convention Help}) I’m completely stumped on this one and need to reduce the number of fields in my base, any way to combined these two fields into one?

132
C
S
sarahdoodleNew Participant
posted in Formulas
Create formula to detect if a date is next month

I run a career coaching program, and I am trying to automate how we offer people the opportunity to renew their time in the program. We do this manually each month by looking at everyone’s dates and changing a single select field called “Initiate Renewal.” What I want to do, is create a formula that will continuously look at everyone’s end dates and detect when someone’s end date is next month. If their end date is next month, it would update a field “Renewal end date is next month” with YES. And this would then be a trigger we could use in a Zap to send that person to the right email automation in ActiveCampaign (to send the renewal emails). I made a mockup of what I’m trying to do. I’m totally open to better ways to do this, but this is what I’m trying to do on a very literal level :slightly_smiling_face: …

452
TheTimeSavingCo
H
Hayley_DevlinNew Participant
posted in Formulas
Formula to work out end date & time - start date and time minus duration

Help please! I want to create a formula that spits out the total amount of hours work less a break time for team time sheets. I’m using time and date field to record when people work and a duration field for breaks. Can anyone give me the formula to use please?

466
J
J
Jack_TranckleParticipating Frequently
posted in Formulas
Timeline Utilization using 2 dates (start date and end date)

Hello all, thank you so much for taking the time to read this, any help would be appreciated I have a question with regards to a topic that Gareth covered in is "“Allocating Resources with a Timeline View”. His demonstration is great, however the utilization field in the timeline only links to a single record, which is fine if you only take 1 day off at a time. My records need to have both a start and end, some up to a week apart. My question, is there a formula, or other method that will allow to be take both of these dates and consolidate all of the dates in between into a single cell? As this seems to be the only way for the timeline to recognize all of my holidays If there is a better solution for me to do this then any help is appreciated, but essentially the end goal is to find a way to combined projects and holiday data (2 dates) onto one timeline Thanks !

20
J
H
Hairtable11Known Participant
posted in Formulas
Use a conditional formula to create text string containing a field name

Is it possible, for a number of checkbox fields, to write a formula that will concatenation all of the field names for those fields that are checked (Airtable automatically adding those field names to the string, not using SWITCH or other operators)?

00
H
James_Carringto
James_CarringtoParticipating Frequently
posted in Formulas
Get the email address of a collaborator

Hi community! Looking for a means of extracting the email address of the collaborator. Column 1: Collaborator: John Smith Column 2: Formula: Extract email address of John Smith Is this possible?

1144
James_Carringto
C
Cassy_AmeliaKnown Participant
posted in Formulas
SUM of values separated by commas in the same cell ,

Hi, Is there ANY solution to adding numbers separated by commas in the same cell? I have used the SUBSTITUTE function to replace multi-select items with a numerical value and this has resulted in this kind of result: 3,2,1,4,1 Now I need to add those numbers so I get 11. The closest I’ve found to a solution is in this post: SUM of Values separated by a comma in the same sheet Ask the community Try IF( {Your Variable}, VALUE( LEFT( {Your Variable}, FIND(',',{Your Variable})-1 ) )+ VALUE( RIGHT( {Your Variable}, LEN({Your Variable})-FIND(',',{Your Variable}) ) ) ) IF( {Your Variable}, VALUE( LEFT( {Your Variable}, FIND(',',{Your Variable})-1 ) )+ VALUE( RIGHT( {Your Variable}, LEN({Your Variable})-FIND(',',{Your Variable}) )

292
kuovonne
J
Jarred_SandersNew Participant
posted in Formulas
Review Process turn time Date/Time diff with multiple fields

Requesting help with a formula. Most likely an IF or Switch. We have an escalation review process that I am trying calculate the Level 1 turn time between when the review of an escalation began (Review in Progress selected) to when it was approved or denied. Escalation Decision (Single Select): Received, Review in Progress, Approved, Approved w/ Conditions, Additional info pending, Returned, Denied. Escalation Decision Last Modified (Last Modified field, only updates when Escalation Decision is changed) I was able to use the solution from a previous topic Justin_Barrett answered to grab the time stamp for when a specific Escalation Decision was an use three automations to populate a date/time field for each option. (Right now Review in Progress, Approved and Denied) How to get the time when a field was last modified to the desired value? Formulas This could also be done with Airtable’s native automations if your base is part of a Pro- or Enterprise-plan works

71
Rupert_Hoffsch1
K
Katie_McHughNew Participant
posted in Formulas
Formula help request please

Hi I have This is probably really simple but I am not fab on formula. I have column 1 that is hourly rate (£) column 2 the task (looks up sheet 1) (text) column 3 Duration (looks up Time recorded via look up on Sheet 1) (Hour & mins) column 4 Budget for the task (looks up sheet 1) ((£) column 5 Final costs Column 5 I cannot get the formula right. It seems to use the day rate and multiply’s the mins so day rate of £265 worked 20 mins is totalling 318, 000, 00…Can a kind soul please help me? Thank you!

192
K
V
Veronica_BaremaNew Participant
posted in Formulas
USE If to create negative #

I am creating an income/expense log - I would like the currency amount to automatically become a negative number when the single select option is “expense.” Can I use IF to make this happen?

211
V
Y
Yamen_RabadiNew Participant
posted in Formulas
URL preview within table

Hello . Is there a way to preview a URL link (in field 1) to show within a table field (Field 2) as a thubnail of image ?

1786
kuovonne
E
Event_BrandingNew Participant
posted in Formulas
Help with DATEADD formula for start up date depending on 'Tier'

I’m trying to set up a formula to determine the start up date of a project depending on whether it is a Tier 1 or Tier 2 event. Tier 1 will have a 4 month lead time. Tier 2 event will have a 3 month lead time. IF ({Tier} = 1, DATEADD({Event start}, -4, ‘month’) IF ({Tier} = 2, DATEADD({Event start}, -3, ‘month’) This formula is ‘invalid’ - please help!

32
E
G
Greg_CulleyNew Participant
asked in Formulas
Prefilling Form with User

If all users have a record in the base, is there a way to prefill the user field on a form with the user who is signed in when they access the form URL? Not having luck creating a prefill formula that accomplishes this.

362
K
J
Jesper_HolmstroKnown Participant
asked in Formulas
#ERROR message when LEFT or extracting text formulas used

Hi guys, have been sitting with this a couple rounds now and can’t get it right. Im extracting a telephone number from a column in Airtable, but when there is no phone number or the word message I get the famous #ERROR. How can I get Airtable to just show it as blank? My formula looks like this: IF({Telefon 1},(LEFT({Telefon 1}, FIND("Message", {Telefon 1}) -1)),BLANK()) Any advice is welcome Regards Jesper

2110
J
F
FrankLovesAirtaNew Participant
asked in Formulas
Formula for sending report after 1 year, considering previous and future dates

Hello community, I already searched through the forum and found a helpful formula - with a minor problem based on that formula that I am trying to fix. Our datetime_format is D-M-YYYY. Background: We have a field called {start date} for when a new client relationship starts. Let’s say one client started on 1st September 2022 (past date) and another client will start on 1st November 2022 (future date). We have a report automation that we want to send out every year. The first report will be sent out one year after the client was onboarded, that process starting now. That means we do not want to send out multiple reports for a client that started on 1st July 2010, instead they will receive their first report on 1st July 2023. In our understanding we need a date formula adding one year to the {start date} and also consider the current year. Formula: IF({start date}, DATETIME_PARSE(DAY({start date}) & “-” & MONTH({start date}) & “-” & (YEAR(NOW()) + IF(DATETIME_FORMAT(DATEA

42
F
C
Catalina_AngariNew Participant
posted in Formulas
Extract text after second character

Hi can someone help me to extract the TEXT3 after the second " - " I’ve tried this formula but haven’t been able to reference the second " - " IF({Engineer + Seniority + Direct Manager}, REGEX_EXTRACT({Engineer + Seniority + Direct Manager}, “(?:\- )(.*)”)) Text sample: TEXT1 - TEXT2 - TEXT3

163
C
A
Arlo_James_JackNew Participant
asked in Formulas
Make something say DONE when done, OVERDUE when overdue, Approaching within one week

So I have the following: IF( TODAY() > {Date Due}, “OVERDUE” ) I have a Column named ‘Days’ for days left to do my task, so I made the following, using the nested IF tutorial: IF( TODAY() > {Date Due}, “OVERDUE”, IF({Days} < 5, “Soon”) ) I’m trying to figure out how to add my ‘done’ column to this but I keep on getting errors. So far I tried: IF( TODAY() > {Date Due}, “OVERDUE”, IF({Days} < 5, “Soon”) IF({Done} = Yes, “Done”) ) but it says invalid. idk how to return text from a multiple choice??

42
A
C
Curtis_Hall1New Participant
posted in Formulas
Selecting single select based on another select select column

I’m trying to setup a training list. I have primary and secondary categories. I want to select from the primary category and then have the option to select from the secondary categories based on the primary category. How would I go about this?

1755
Rupert_Hoffsch1
G
Groupof7Participating Frequently
posted in Formulas
Assigning a product to a shipping box based on the product dimensions

Hi, I’m looking for some help please to write a formula that assigns a product to a particular shipping box based on dimensions of the product. The products have a height, depth and length, The boxes have minimum and maximum dimension thresholds for height, depth and length. How do I created a formula that assigns each product a box (or multiple boxes?) based on its dimensions? I created a demo of the Product and Box tables: Airtable Product - Shipping Base - Airtable Explore the "Product - Shipping Base" base on Airtable. Thank you!

63
TheTimeSavingCo

Badge winners

Show all badges
Terms & Conditions