Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,941 Topics
  • 18,238 Replies
5941 Topics
K
ktsParticipating Frequently
asked in Formulas
Run IF(OR()) if any of 4 fields is filled

Hello! I have 4 date fields, and for a given record, any of the 4 could be filled in (or several of them, or all of them). I've written a formula to return 'Y' if any of the 4 dates fall within the current week. Here's what I've got:IF(OR({Date 1}, {Date 2}, {Date 3}, {Date 4},IF(OR(((DATETIME_FORMAT({Date 1}, 'YYYY')&DATETIME_FORMAT({Date 1}, 'WW'))) = (DATETIME_FORMAT(TODAY(), 'YYYY')&DATETIME_FORMAT(TODAY(), 'WW')),((DATETIME_FORMAT({Date 2}, 'YYYY')&DATETIME_FORMAT({Date 2}, 'WW')) = (DATETIME_FORMAT(TODAY(), 'YYYY')&DATETIME_FORMAT(TODAY(), 'WW'))),((DATETIME_FORMAT({Date 3}, 'YYYY')&DATETIME_FORMAT({Date 3}, 'WW')) = (DATETIME_FORMAT(TODAY(), 'YYYY')&DATETIME_FORMAT(TODAY(), 'WW'))),((DATETIME_FORMAT({Date 4}, 'YYYY')&DATETIME_FORMAT({Date 4}, 'WW')) = (DATETIME_FORMAT(TODAY(), 'YYYY')&DATETIME_FORMAT(TODAY(), 'WW')))),'Y', 'N')))I added the first IF(OR( argument to allow the second IF(OR( to run, no matter how many of the date fields have data in

100
K
A
asheliz2New Participant
posted in Formulas
Formula for a Checkbox Total

How would I go about setting up a formula that only activates IF a checkbox is checked?If I have selected a checkbox, I want the total to be divided in half, otherwise, I want to keep the whole total as/is

21
Andrey_Kovalev
2
2d78New Participant
posted in Formulas
Count and divide the hours for the past day

Hi, please help to understand whether it is possible to implement the following:On the screenshot, there is a table in which data about the status change and its time are entered via a webhook. I calculate the duration between events. Now I need to make a report for the previous day, in which I indicate how many hours the status was online and how many hours the status was offline.@kuovonne can you help?

166
kuovonne
B
Bill_McInerneyNew Participant
posted in Formulas
Cells change based on another cell info

Here’s an examnple of what i’m trying to do One cell has a single select item to choose from… In this example i will say i can choose from “A” and “B”. if i choose “A” then the cell to the right of that would change the single select items in that cell, and allow me to choose either “blue” or “green”. if I choose “B” then the single select item dropdown changes and allows me to choose the colors “red” or “orange”. Is doing something like this possible?

432
O
M
Michael_LeverInspiring
asked in Formulas
Field entries are £20.49 and £13.66 but in a third field when I use CONCATENATE …

The field entries are £20.49 and £13.66 (both fields set to 2 decimal places) but in a third field when I use CONCATENATE this is what I get:Summary: Headline £15000 pa Actual £9975 pa £20.491666666666667 £13.661111111111111How do I get £20.49 and £13.66 ?tiaps - I daresay this (sort of) question has been asked before but I have no idea what to search for. 

10
M
B
Ben_BishopNew Participant
posted in Formulas
IF statements not working with rollup date

Hey,I want to do a simple if statement to show TODAY for the date today. It works for a date field but not for a rollup date field. IF({Date Rollup (from Stock)}=TODAY(),"TODAY","")

41
kuovonne
R
Rlaurence83New Participant
asked in Formulas
Search & Update specific Field - Airtable - Zaiper - Frame.io

Not sure if this is a air-table or zaiper question hopefully someone can explain to me. This is what im trying to do:Uploaded video To frame.io where the name of the video is the same as a Unique ID in airtable ( called Asset ID ) in Airtable search for a field ( Video Edit ) Input text or link  to inform that video has been uploaded I have tried to find the record using a search function and my zap looks ok but when i run it it does not update to the correct field.when i test the last part of my zap ( input text ) it will only place it in the first record and not the one attaching the unique ID i have tried the following SEARCH functions also FIND SEARCH(({Asset ID}))!={Edit}SEARCH(({Asset ID})) = {Edit}not sure if it is that or when i go to set up my action in the RECORD section i am picking the wrong dropdown.i thought it may be “custom” “zap search was found” but didn't work , and i have also tried multiple other options  any input would

70
R
K
Kamtex_SolarNew Participant
posted in Formulas
Stock Changes: How to I update a linked item's field value in another table?

Hi guys and fellow airtable experts. I am creating an inventory system in airtable with a stock receipt table that will record any changes to my main table's item stocks.  It can be additions if I receive goods and subtractions if I sell away the goods.Main table has a list of items.Main table fields:item namestock quantityStock Receipt table has a list of stock receiptsStock Receipt fields:stock receipt name (text)date (date)item name (linked to main table's item name)quantity adjusted (number, can be pos+ or neg-)  How do I make it so that when a stock receipt is created, airtable automatically compares the stock receipt entry and updates the main table's quantity for that item? E.g. Stock of item was 10. A stock receipt was created with quantity of" -1".  new updated stock of item is now 9.

22
K
T
Tina_LopezParticipating Frequently
posted in Formulas
Split a linked record by comma

Hello, I've looked everywhere and can't find a simple solution that works for me. I have a table where I link records and I want to take the linked records field and split it by comma and input those items into it's own field. Can someone please give me a formula to input into the columns?

853
J
Z
zeremKnown Participant
asked in Formulas
Really need your help - how to create an automation that does "contains" in MAKE with Airtable?

Hey guys, this is it -I have 2 forms on the site -1. Form for capturing leads (cold)2. Another form at a more advanced stage that captures very hot leads.I have automation in MAKE that actually transfers all the cold leads that come from the first form to the Airtable list.In each line for each lead in Airtable there is a checkbox whose purpose is - did the same lead also fill out details in form 2? (in order to understand if it is a hot lead) because it is also possible to reach form 2 without going through form 1 and therefore I do not want to create an additional column for those who fill out the form 2 but to update in the same column of the lead the person who filled in form 1. (so I will not have duplicate columns with different email or something like that)I managed to create an automation in Make that checks whether the incoming lead's email is in the list and if so to update, and if not to create a new one.My problems are as follows:1. Sometimes there are leads that use differ

50
kuovonne
K
K_M_RNew Participant
asked in Formulas
How can I have one field automatically populate another field.

I am looking for help, hopefully I can explain this well enough.In my Airtable, I have a multiple select field where you can choose different available mediums.  (Video, animation, poster, pdfs etc). And I want to create another column that automatically changes depending on what is selected.  If video or animation is selected, a V would appear in the column next to it.  If a “poster or PDF” is chosen a G would appear.  I cannot figure out what Formula to use.Basically I want it to be “if this is selected” then “show this”I tried creating a formula column and addedIF(Medium, 'Video/animation', 'V')IF(Medium, 'PDF', 'G')but it doesn't work so I assume i am doing this incorrectly, I'm new to formulas and building these tables, so any help is appreciated. 

1930
J
R
Rlaurence83New Participant
posted in Formulas
Frame.io New Video Added Not updating to correct field Air-table

Not sure if this is a air-table or zaiper question hopefully someone can explain to me. This is what im trying to do:Uploaded video To frame.io where the name of the video is the same as a Unique ID in airtable ( called Asset ID ) in Airtable search for a field ( Video Edit ) Input text or link  to inform that video has been uploaded I have tried to find the record using a search function and my zap looks ok but when i run it it does not update to the correct field.when i test the last part of my zap ( input text ) it will only place it in the first record and not the one attaching the unique ID i have tried the following SEARCH functions also FIND SEARCH(({Asset ID}))!={Edit}SEARCH(({Asset ID})) = {Edit}not sure if it is that or when i go to set up my action in the RECORD section i am picking the wrong dropdown.i thought it may be “custom” “zap search was found” but didn't work , and i have also tried multiple other options  any input would

20
R
A
athomasNew Participant
posted in Formulas
Stop "days old" count when status equals complete

Hello Community! I have to preface this request by saying that I was asked to come up with a way to track issues, stumbled across Airtable, and purchased an account. Airtable has been a lifesaver but I have zero knowledge on how to create formulas.I built a table and created a form for people to submit their issues but now I need to track how long it takes the team to complete a ticket. I would like the days-old filled to stop counting once the Status field equals complete. Would anyone be able to help me with this? Below is a screenshot of the modified bugs template that I've been using.   

92
A
R
Rlaurence83New Participant
posted in Formulas
SEARCH Formula for Newbie HELP!!! Video Production Workflow

New to Airtable / Formulas and Automations 🙂 Hopefully someone out there can explain what im assuming is a basic SEARCH formula to me 🙂 I have created some automations using Zaiper to update a record based on a UNIQUE ID ( Video ID ) but i can only create a search function for that specific ID.  Example Attached 🙂How would i go about creating a formula to SEARCH for a ID value that is 1 more than the previous one.ie i can find "SC1" but i would like to create a formula that could find "SC2" , the 3 and so on. As i have a file naming convention that is sequential its needed to update each record through-out the workflow process. 

53
R
A
Ashley_PoslunsParticipating Frequently
asked in Formulas
Markdown URL From Formula Field

Is there a workaround to use rich text markdown for a formula that provides a URL? More context: My formula provides a lengthy URL, and it triggers a slack notification. Would love to mark down so the slack notification doesn’t take up the entire window! Thanks in advance for your advice.

947
R
A
Abbey_MatyeParticipating Frequently
asked in Formulas
Formula to pull a list of individual nights out of a date range?

Hi all! I’m using part of my AirTable Workspace to track hotel lodging needs for my organization’s yearly event. I have the data built where I assign an attendee to a property and room type, and include their check-in and check-out dates. Is there a formula to pull each night of stay for the date range without me having to manually build out a report listing each night of stay for every individual? Or is there a way this can be built in automations or interfaces? For example, John Smith is checking in on March 22 and checking out on March 24, so his individual nights of stay would be March 22 and March 23. I’m looking to pull this data so I can see what the nightly pick-up is for each property by room type. I started using the day calendar as a sort of “list” of nightly pick up of rooms, but since it shows up on the check out date, it’s not quite accurate. Thanks for any suggestions you may have! I’m happy to schedule a GoogleMeet meeting to visualize things together if you’ve built so

306
summerdickey
P
plyskeInspiring
posted in Formulas
Recurring Records, how to have field data saved for the "new" Record?

Hello! I have a table with Recurring Records, representing daily or weekly tasks. Some of these tasks have sub tasks to them. When I complete a task (Record) it is sent to a 'Done' view and then Recreated automatically in the Todo view. However only the name of the Record is saved and used again, not the data from the Fields in the Record, eg. sub tasks. Is there a way to save this so that I don't have to write them manually every time the Record is recreated?

215
kuovonne
Z
zeremKnown Participant
posted in Formulas
What is the "code" for the checkbox? I mean, if I want it to automatically mark V in the checkbox, w

Hey friendsI want to check who has watched the videos on my system and I have some automation that was up until now transferring to Google Sheets and now I'm switching to AIRTABLE.If I want it to mark me with a V in the checkbox, what should I write?I would later like to add IF on the same simple checkbox and check the quality of a lead.Hope you understood.I'll be glad to receive your help,Thank you! 

76
Z
M
Matt_StanleyNew Participant
posted in Formulas
Formula for creating daily sleep log

Hey guys, I recently built a “Wellness Log” base in Airtable that tracks all kinds of stuff: When I get up and go to be every day, when I eat each meal, how much H2O I drink every day, how long between each meal, and star-based ratings of my mood, mental focus, and energy/stamina. What I have no idea how to do but really WANT to do is build formulas that calculate change over time using entries from other days. Specifically, I want to calculate the amount of sleep I’m getting every night, as well as net weight loss/gain since I started tracking my weight in the base. Regarding the nightly sleep calculation, I have a “RISE” and “BED” field in each record that is a Date field type that includes the time in 12 hour format. I want to create a formula to identifies the preceding record and calculates hours of sleep based on the amount of time between “BED” yesterday and “RISE” today. With the weight loss/gain I would need to ID the first record that has a value in the numeric weight field,

81
M
N
NoCodeLearner32Participating Frequently
asked in Formulas
How to separate out text into two fields based on a specific word?

Hi there, I'm new to Airtable and trying to figure out how everything works. Let's say I have a cell that as the following information I inserted below: Ingredients- 4 salmon fillets- 2 tablespoons of melted butter- 2 tablespoons of olive oil- 4 cloves of minced garlic- 2 tablespoons of freshly-chopped parsley- Salt and pepper- Lemon wedges (optional)Instructions1. Preheat the oven to 375 degrees Fahrenheit.2. Place the salmon fillets onto a baking sheet lined with foil.3. In a small bowl, mix together the melted butter, olive oil, minced garlic, and parsley.4. Brush the butter mixture onto the salmon fillets.5. Sprinkle salt and pepper over the fillets.6. Bake the salmon for 20 minutes, or until the fish is cooked through.7. Serve with lemon wedges, if desired. Enjoy! How can I separate all of the "ingredients" into one cell? And all of the instructions into another?  

190
N
Z
zeremKnown Participant
posted in Formulas
How to connect data from 2 different tables so that if the same email appears - I will get a V in th

Hey friendsI have 2 tables in one base.In one table I have a list of all my leads and in another table I have a list of those who paid money for the workshop I give.I want to make it so that in one table there will be a column that checks if the email of that lead exists in the other table of the payers and if so, to add a V in the checkbox.Is it possible?And if so, how do you do it?Many thanks to the best community in the world!

10
Z
G
Gaspar_CaroParticipating Frequently
posted in Formulas
Formula to Alphabetize Values

I believe I know the answer to this question (no) based on a search of past Community posts, including this one by @Jason_Hull that got expert replies by @kuovonne : is there a formula that can alphabetize referenced values? There are a lot of use cases for where this would be a helpful function. In my case, it's to list a different field's multi-select options in alphabetical order for an easier at-a-glance confirmation of the multi-select field's entries. Specifically, my source multi-select field has a looooong list of occupations and I'd like to build a formula field that lists the selections alphabetically.

271
kuovonne
J
John_B2Inspiring
posted in Formulas
Incorrect date and time

It's currently 19:55 on the 21st december in New Zealand. Why does this calculation return a date of the 20th december and the time as 18:55? Surely it can't be that hard to show the correct date for the country you're in?  

112
J
Z
zeremKnown Participant
asked in Formulas
why my formula isn't working?

Hey friendsI'm trying to create a rating for my leads in the system based on all kinds of parameters - if they watched video 1, if they watched video 2, etc. For some parameters, I want to create 2 stars and some more. As in the example, if the customer is marked V on Facebook, he should receive 4 stars because this indicates a higher rating.It doesn't work for me when I add the most stars.I would appreciate the help and understanding of what I am doing wrong here.Thank you! 

100
Z
A
apisanoNew Participant
asked in Formulas
Multiple IF(FIND(OR statements

Hi everyone, I've got a set of criteria to score the program length that states the following: "A faculty-led program for any term is 3 points. An interterm program longer than 3 weeks is 3 points. A summer program longer than 6 weeks is 3 points. An interterm program longer than 11 days, but less than three weeks is 2 points. A summer program longer than 21 days, but shorter than 41 days is 2 points. An interterm program shorter than 11 days is 1 point. A summer program shorter than 21 days is 1 point. In translating that to a nested combination of IF(FIND(OR arguments, my syntax got off, or, alternatively, resulted in all points being wiped out or everyone being assigned 3 points. I've included a few versions of my formula text below. Should I be using a FIND( statement for each IF( statement?v1IF(   Find(      "interterm",{term}),      OR      ({Smith faculty-led?}="Yes

80
pressGO_design

Badge winners

Show all badges
Terms & Conditions