Formulas

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

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

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?

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?

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. 

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

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 f

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.

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?

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.

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'

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 f

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.   

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. 

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.

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 tog

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?

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! 

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 valu

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.

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!

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.

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?  

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! 

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(