Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,227 Replies
5939 Topics
Nick_Robeson
Nick_RobesonInspiring
asked in Formulas
Agenda Creation, including start times and end times

Hi all! So I’m trying to make things a little easier for our Training Team and automating their Class Agenda creation process. Currently I have 3 tables in the base: Classes, Agendas, and Class Agenda Templates. Using automations, I am able to select a template in the Classes table (through a linked record field) that generates all agenda items for the given class in the Agenda table. Each agenda item created is also linked to the specific class through the linked record field. I’ve gotten as far as you can see in the screenshot of the Agenda table below. I’m trying to figure out a solution that will allow me to use the Start Time (given) of the first Agenda Item along with the Duration field to generate the start and end time for each topic throughout the day. I’m aware that I will most likely need to create and use formula fields as well. Any help is appreciated!

293
TheTimeSavingCo
J
Jesus_YanesParticipating Frequently
posted in Formulas
Converting a formula filed that has year and month (Round to year)

I have a Service Years column that I would like to round in a new formula filed This is what the formula looks like in the Service Years column I would like to have a new field/column that take the year and month and round it up to the year. Example: (Service Years) 10 years 10 months – (New Field/Column) 11 years

211
Kamille_Parks11
W
Weng-ICParticipating Frequently
posted in Formulas
How to pull data from a separate table

Is there a way for me to auto tabulate from “Inventory” description to the respective field under “description” column for all the rows? Title: enter title Year: enter year Type: enter type Dimension: enter dimension https://airtable.com/apphT5d1TuRAoA4Tw/tblgN5TZV5AoF3Uo0/viwietBKsP2FguBSx?blocks=hide Original post was here. Auto Tabulate Between Tables Formulas Is there a way for me to auto tabulate from “Inventory” description to the respective field under “description” column for all the rows? Title: enter title Year: enter year Type: enter type Dimension: enter dimension I am getting this error. ‘ Brand:’ & {Brand Name} & ‘Title:’ & {Title} & ‘Year:’ & {Year} & ‘Type:’ & {Product Type [Non Editable]} & ‘Dimension:’ & {Length} & ‘x’ {Width} & ‘x’ & {Height} & '

20
W
J
Jason_Friedlan2Participating Frequently
posted in Formulas
Find first Entry in Look Up Field

I have a very predictable set of data across multiple tables. There is one table where a person can request a room for a meeting. If the room for the meeting is more than 30 minutes I run automation to make sure the next 30 minutes time period is assigned and link it back to the original request. This results in a LookUp field called Timeslots that has two results 30 minute period 1 w/ Date & 30 minute period 2 with date. For the next step I need to take 30 minute period 1 w/ date and use that info to Schedule the meeting in my cal. the result is always hh:mm mm/dd/yyyy I am stuck trying to figure out how to extract just that first 30 minute entry from the look up datetime format it.

815
Justin_Barrett
L
Ludwik_C_SiadlaNew Participant
posted in Formulas
Random Lookup to Attachment

Hey, I’m looking for a solution to choose the random row from another table. Here’s my scenario: 2 tables. One has text data, the other has just two columns: ID and Attachment (in this case it’s a png file). I want to create a lookup filed to pull the attachment, but I want this lookup to pull the RANDOM ID out of finite number of rows (like 10 in my example). I could do that in Excel by combining VLOOKUP with RANDBETWEEN functions, but how to I do that in Airtable? Thanks!

30
L
P
Paul_KariniemiNew Participant
posted in Formulas
How to create two dependent single select fields to determine the contents of a third field

I’m having trouble figuring out how to do a formula for dependent single select fields. This formula will be used in a form for field technicians for a construction company. The first single select field will have the choices: Product A, Product B, Product C The second field will have the choices: Variation 1, Variation 2 Any help with the formula below is greatly appreciated!

22
Hannah_Wiginton
I
Ish_RojasNew Participant
posted in Formulas
Need Help to Evaluate an Impact Range

Am unsure how to best evaluate an impact range. Three attempt below. Would appreciate help to divine practical solution. Thanks in advance. Impact Assessment No Impact, 0 - .9 Very low Impact, 1 - 1.9 Low Impact, 2 - 2.9 Medium Impact, 3 - 3.9 High Impact, 4 - 4.9 Very High Impact, 5 I thought to evaluate and have tried these: Attempt1, cannot get beyond first set of IF(OR IF( OR({xf.Impact Score Average}=0,“No Impact”, {xf.Impact Score Average}<=1),“No Impact”, IF( OR( {xf.Impact Score Average}=1,“Very Low Impact”, {xf.Impact Score Average}<=2),“Very Low Impact”,“X”)) Attempt 2, does not appear to work IF({xf.Impact Score Average}>0 <1,“No Impact”,“XX”) Alternative 3, Appears to work but bit cumbersome IF({xf.Impact Score Average}=0,“No Impact”, IF({xf.Impact Score Average}=0.1,“No Impact”, IF({xf.Impact Score Average}=0.2,“No Impact”, IF({xf.Impact Score Average}=0.3,“No Impact”, IF({xf.Impact Score Average}=0.4,“No Impact”, IF({xf.Impact Score Average}=0.5,“No Impact”, I

00
I
B
Bharat_SinghalParticipating Frequently
asked in Formulas
WORKDAY_DIFF function and DATETIME_DIFF function are giving same output. Am I doing something wrong

Hello everyone, I am trying to use workday_diff function to calculate whether my team is able to process orders within specified TAT or not. and hence needs to calculate workdays. We have a column that tracks date created ({Date Created})for any order and another column that tracks last modified time to track how much time we took to finish the order ({Date Completed}). Hence the formula is : WORKDAY_DIFF({Date Created},{Date Completed}) But the output counts weekends too and the output is same as using DATETIME_DIFF. Please help me figure it out.

1899
B
G
Gabriel_AmaralNew Participant
posted in Formulas
Tiny formula field

i’m having a lot of trouble to expand to see my formula field. i tried to reload the page, reopen the airtable, and the formula field still tiny. Only using the scroll to see what i am doing. Help! :cry:

233
G
R
Ryan_SagerNew Participant
posted in Formulas
Create a bar or line graph of spending by month/quarter

Hi - I’m trying to track spending for various entities by date, to allow the creation of a chart of spending by month. Our database has a list of Advertisers. And each of those Advertisers is linked to many Advertisements. Each Advertisement has already been assigned a dollar value and each record also has a date. How would I create a chart for each advertiser for what they’ve spent in a given date range — ideally by month and/or quarter? I’ve been able to create a cumulative sum of ad spending per Advertiser, but I’m stumped on breaking it down by month. Any help much appreciated! Thanks!

30
R
L
Liesbet_De_RoucNew Participant
posted in Formulas
Trying to translate Template ID's to Words

Hi there, Not sure if anyone can help me out on how to write the following formula: I have a column with Template ID’s that should be translated to what type of Template they are. I assume i need to write an IF template ID = 1 2 or 3 then show ‘thisword’ and if template ID = 4 5 or 6 then show ‘thatword’ but i have no idea how to write that formula. Im new to this so any help would be hugely appreciated. Looking forward to solving this!

21
ScottWorld
A
Alana_YanezNew Participant
posted in Formulas
How do I take a date from two separate columns and fill a third column with that data?

Thank you in advance for your help! I have two columns with dates related to when students passed quizzes. For those that pass Quiz 1, we have a date and nothing in the column for Quiz 2, which would be a second attempt to pass the course. I’d like to create a third column where it contains the pass date from either Column 1 or Column 2. I’m not finding the right combination for a formula. I’d appreciate some help. I’m also not sure how to write a value for saying that any date is ok, and that any cell that doesn’t have a date can be skipped. I’ve tried something like this but it’s totally wrong, and I’m missing how to write any date rather than specifying an exact date too. IF(OR({Quiz 1: Date}=“Date”,{Quiz 2: Result}=“Date”),“Date”,“Date”)

42
A
C
compaenen_compaNew Participant
posted in Formulas
Rollup field based on value in other (changing) field

Hello! Is it possible to create a roll-up based on the value of another field? Conditional roll-ups won’t do the trick, because I’d like to the value to be ‘variable’, and not fixed. Is this possible with a formula within the aggregate field? Or any other suggestions? I guess the needed functionality looks a lot like “sum-if” from Excel? Thanks! Dennis

131
TheTimeSavingCo
rrbbtalnorth
rrbbtalnorthParticipating Frequently
posted in Formulas
Filter one field from another field

I’m looking for a formula that knows how to filter one field from another field and find what’s left. Would appreciate help

146
rrbbtalnorth
NEIL_S
NEIL_SNew Participant
posted in Formulas
Running total of text submissions and then email them to submitter

I’d like to have a simple form where people can submit key words, separated by a comma. When they hit submit, they will be emailed all of the submissions to date, including theirs. Is this possible? It will be mostly words, but maybe some numbers too. Submitted as example: dogs, cats, fish, horses - Who can help? @Noamsay

11
TheTimeSavingCo
J
Jon_FramptonNew Participant
asked in Formulas
Last Day of Month / End of Month / EOMONTH

If I want to specify the last day of the current month in a cell as a date, what is the syntax? In Google Sheets & MS Excel it is EOMONTH (End Of Month). EDIT - HERE IS THE FORMULA - THANKS KAMILLE & JUSTIN DATETIME_FORMAT( DATEADD( DATETIME_PARSE( DATETIME_FORMAT( TODAY(), 'YYYY' ) & '-' & (VALUE( DATETIME_FORMAT( TODAY(), 'MM' ) ) + 1) & '-01', 'YYYY-MM-DD' ) , -1, 'day' ) , "MM-DD-YY" )

19017
Irit_Levi
S
Sebastian_SchulNew Participant
posted in Formulas
How to get a condtional lookup of linked record displayed in primary field (circular reference error)

No matter what I try, Airtable will not allow me to display a latest linked record in the primary field. All attempts end with circular reference errors. Table structure: Company name < primary, formula > ( {current name} ) company names [1:n] < linked > related to Company Name current name < conditional lookup > from {company names} ( {if latest} = 1 ) ... Company Name name < text > company [n:1] < linked field > related to Company if latest < formula > ( IF {start date} = {latest start date}, TRUE() ) ... I’m running out of ideas. As soon as I use company names in conditional context with if latest, I don’t get the value into the primary field. It also doesn’t matter if I evaluate current name in the Company table as shown in the example or in the Company Name table and use a normal unconditional lookup. It can’t be that you have to use an automation for such a trivial thing!? I would be happy if someone knows a solution.

30
S
Ricardo11
Ricardo11Inspiring
posted in Formulas
Formula to detect broken PDF attachments?

Hey there, I wondered whether a formula could be employed to detect broken PDF attachments? Due to an unknown error, some attachments are broken and I’m looking for a way to detect those.

00
Ricardo11
L
Lindsey_GarveyNew Participant
posted in Formulas
Error in cells where date is missing in formula

Hi there, I know this question has been asked previously, and I realise there’s resources to give an idea, I’m just new to Airtable formulas, and still can’t make it work for me. My question is super simple. I have a ‘date’ column, and then a column with formula to add 30 days. It then produces an error in all of the cells where the ‘date’ column hasn’t been populated. I realise I need to use ‘IF’… but struggle from there. I’ve tested out examples that I’ve seen, but no luck. Thank you!

75
kuovonne
D
David_VoynowParticipating Frequently
asked in Formulas
Displaying a formula produced future date based on one existing date and the selected value of another field

I want to use an IF statement in a field where the logic will basically be the following: IF({Calibration Frequency}= Annual, ({Last Calibrated}+365 days)) I know this isn’t a valid way to code this - strictly an attempt to explain the intent. But basically, the {Calibration Frequency} field is biannual, annual, or 2 years. Depending on that value, i want the field to display the correct “due date” in the coded field based on the calibration frequency and the {Last Calibrated} date.

2210
D
C
Chris_DukesKnown Participant
posted in Formulas
Formula to get a field from another record based on it's date

My table lists all the video episodes that my company releases. Each Episode’s Record has an Air Date date field. Each Episode’s Record also has a Previous Episode field that has the Project Name of the last released episode. The last released episode is based on the Air Date of the most recent previous episode. Currently, I manually add the previous episode’s Project Name to the Previous Episode field of each Episode’s Record. I want to use a formula to fill the Previous Episode field automatically. Can you even use a formula to reference a field in a different record from the same table?

2512
C
C
Claire_LNew Participant
posted in Formulas
Hide multiple prefilled fields

Hello, Could you please tell me if you can hide multiple prefilled fields ? When I do it, it only hide the second one (out of 2). I cannot manage to have both hidden Thank you in advance !

92
C
Karlstens
KarlstensBrainy
posted in Formulas
Omitting words from Regex_Replace() sanitation

Using https://regex101.com/ - the below works well enough for omitting words from sanitation. (?i)(?!keepWord1|keepWord2|keepWord3)(replaceWord1|replaceWord2|replaceWord3|replaceWord4|replaceWord5|replaceWord6) So this doesn’t work; REGEX_REPLACE(Player,"(?i)(?!keepWord1|keepWord2|keepWord3)(replaceWord1|replaceWord2|replaceWord3|replaceWord4|replaceWord5|replaceWord6)", "Word_Replacement") The below at least does work, but it’s missing the important omission check - so some proper words get flagged and replaced unnecessarily. REGEX_REPLACE(Player,"(?i)(replaceWord1|replaceWord2|replaceWord3|replaceWord4|replaceWord5|replaceWord6)", "Word_Replacement") Any ideas of how to formulate this within a Formula Field?

103
Justin_Barrett
Leah_Relish
Leah_RelishKnown Participant
posted in Formulas
Button to link to Google Maps

I am trying to create a button to link to a client’s address and am getting a “about:blank#blocked” url when I click on it. What am I missing? The URL Formula I am using is: ENCODE_URL_COM (“https://google.com/map/place/”& {address}) Or is there a better way to accomplish this - it needs to be able to be clicked from the app on a mobile phone. Thanks!

276
Leah_Relish
J
Justin_Miller1New Participant
asked in Formulas
Need help with a date difference formula with some interesting pieces

Hello community! Here’s what I’m seeking assistance with: I have 3 fields total: Length of Service, Service Start Date, and Service End Date (or anticipated) I would like to write a formula for the Length of Service field that does what I currently have, but takes it one step further. Currently, I have a simple DATETIME_DIFF formula to display in Years the difference between the two dates. However, I would like to use today’s date (I’m guessing the NOW function) IF the Service End Date is in fact an anticipated date in the future. So if the Service End Date is greater than today, I would like to use today to calculate the time difference, and if the Service End Date is in the past, I’d like to simply calculate the date difference between the two. Any help is greatly appreciated!

82
Kamille_Parks11

Badge winners

  • Welcome
    youssefhas earned the badge Welcome
  • First Topic
    _Chris_has earned the badge First Topic
  • First Topic
    GegHhas earned the badge First Topic
  • Welcome
    blessing333has earned the badge Welcome
  • Welcome
    margaryhas earned the badge Welcome
Show all badges
Terms & Conditions